-
Hello,
We are having a problem like this. We are having a the following table with the specified number of records.
SQL> desc inv_details
Name Null? Type
------------------------------- -------- ----
DOC_TYPE NOT NULL VARCHAR2(3)
INV_YEAR NOT NULL NUMBER(4)
INV_NO NOT NULL NUMBER(10)
SEQ NOT NULL NUMBER(4)
ACT_DATE DATE
DESCRIPT VARCHAR2(255
LINE_AMT NOT NULL NUMBER(12,2)
CONT_PREFIX VARCHAR2(4)
SERIAL_NO NUMBER(6)
CHECK_DIGIT VARCHAR2(1)
VOYAGE_NO VARCHAR2(15)
ACT_DATE1 DATE
CONT_SIZE NUMBER(4)
CONT_TYPE VARCHAR2(4)
IN_NO NUMBER(2)
OUT_NUMBER NUMBER(2)
OFF NUMBER(9,2)
ON_AMT NUMBER(9,2)
DAYS NUMBER(5)
TAX NUMBER(2)
VOYAGE_OUT VARCHAR2(15)
STO NUMBER(9,2)
OUT_NO NUMBER(9,2)
INV_DETAILS DATE
INV_DATE DATE
REMARKS VARCHAR2(20)
SQL> SELECT COUNT(*) FROM inv_details;
COUNT(*)
----------
218939
Now what we want to do is find out the ACTUAL space utilized in the data file(s) in the hard disk for this table.
In order to find this out we created a export dump of this table with the data and without the data.
Dump file size with the data : 22,960,128 Bytes
Dump file size without the data: 2,048 Bytes
So from this method we can come to the conclusion that the table is
22,960,128 - 2,048 = 22,958,080 Bytes. i.e. 21.89 MB. Is this the correct method to find out the ACTUAL space taken for a table in the data file? Is this method accurate?
Also could you please tell us how much space does Oracle take to store a DATE field. Since the format of any date field can be ultimately given as DD/MM/YYYY HH:MI:SS and since Orale does not store milliseconds, can we take it as approximately 20 Bytes, which is the size of "DD/MM/YYYY HH:MI:SS"?
We would very much appreciate if you could let us know.
Thanks and regards,
OraFan2
-
What about this one:
select bytes/1024/1024 from dba_segments where segment_name='inv_details';
?
-
The above query gives the size all blocks whether they are filled with rows or not. Exporting and finding the size also may not be a perfect way as it gives only the difference in the dumpfile sizes. If you dont rquire it 100% exactly, then analyze the table to compute statistics and from dba/user_tables find out the avg_row_len, multiply this by the no. of rows.
The date field in Oracle requires 7 bytes.
