Re: Physical Storage Requirements for NUMBER attributes.

In article <3kn6tk$5eh_at_inn-amct.atc.alcoa.com>, DPWCC02.OBERLE02_at_SSW.ALCOA.COM says...
>
>I have inherited a database with many fields having the default
>of NUMBER(38). In a similar ORACLE (DEC) Rdb database those
>fields are defined as integers which take 1 word to store. How
>much space does a NUMBER(38) take? Is there a way to dump data
>from an ORACLE database to examine what the physical storage
>looks like?
>

For NUMBER columns the size only indicates the maximum size of the column. To find out how much space is actually being used to store a particular value use the VSIZE function in a select statement. It will tell you the number of bytes being used. To see what's in those bytes use the DUMP function in a select statement. For example, lets say you've got a table created with this statement:

CREATE TABLE FOO (BAR NUMBER(38) NOT NULL); To list each record, along with the number of bytes, and byte contents use:

SELECT BAR, VSIZE(BAR), DUMP(BAR) FROM DUAL; Bear in mind, that the table itself also requires some space of it's own (i.e. SUM(VSIZE(BAR)) != total bytes used by the table).

• Dan