OraFAQ Forum: Server Administration » Table size in Oracle 9i

Home » RDBMS Server » Server Administration » Table size in Oracle 9i

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Table size in Oracle 9i [message #208608]

Mon, 11 December 2006 09:19

orasaket
Messages: 70
Registered: November 2006

Member

Hi,
I am using Oracle 9i R2 on Linux

Want to know the table size (actual data size in table)

I am running the following script.

select ((blocks*8192)-(blocks*avg_space))/1024/1024 "MB size", empty_blocks,
avg_space, num_freelist_blocks
from user_tables
where table_name = 'COM_ACCOUNTS_REC';

however i am not sure if i am firing the proper query.

Please advice me on this.

Thanks and Regards,
OraSaket

Report message to a moderator

Re: Table size in Oracle 9i [message #208618 is a reply to message #208608]

Mon, 11 December 2006 09:34

Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE

Senior Member

hi,
try this

SQL> create table big as select * from all_objects;

Table created.

SQL> show parameter db_block_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_size                        integer     8192

SQL> EXEC dbms_stats.gather_table_stats ( 'SCOTT','BIG');

Actual size of tables
SQL> column segment_name format a20
SQL> column table_name format a20
SQL> select table_name , blocks*8/1024, blocks
  2  from user_tables
  3  where table_name = 'BIG';

TABLE_NAME           BLOCKS*8/1024     BLOCKS
-------------------- ------------- ----------
BIG                      5.2109375        667

  1  select segment_name, bytes/1024/1024,blocks
  2  from user_segments
  3* where segment_name = 'BIG'
SQL> /

SEGMENT_NAME         BYTES/1024/1024     BLOCKS
-------------------- --------------- ----------
BIG                                6        768

Report message to a moderator

Re: Table size in Oracle 9i [message #208623 is a reply to message #208618]

Mon, 11 December 2006 09:57

dba_blr
Messages: 43
Registered: December 2006

Member

The queries mentioned earlier will give you the actual space occupied by the table not the actual data.

Hope the following query will give you the actual space occupied by the data.

select table_name,(num_rows*avg_row_len)/(1024*1024) MB from user_tables where table_name='T1';

Report message to a moderator