Home » RDBMS Server » Server Administration » Table size in Oracle 9i
Table size in Oracle 9i [message #208608] Mon, 11 December 2006 09:19 Go to next message
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
Re: Table size in Oracle 9i [message #208618 is a reply to message #208608] Mon, 11 December 2006 09:34 Go to previous messageGo to next message
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



Re: Table size in Oracle 9i [message #208623 is a reply to message #208618] Mon, 11 December 2006 09:57 Go to previous messageGo to next message
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';
Re: Table size in Oracle 9i [message #208630 is a reply to message #208623] Mon, 11 December 2006 10:09 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Cool Smile
Re: Table size in Oracle 9i [message #209662 is a reply to message #208608] Sat, 16 December 2006 07:51 Go to previous message
orasaket
Messages: 70
Registered: November 2006
Member
That's great!!

Many thanks for the solution

Regards,
OraSaket
Previous Topic: Full DB Export from 8i and Import into 10g
Next Topic: ORA-12224: TNS: no listener
Goto Forum:
  


Current Time: Mon Dec 05 09:18:39 CST 2016

Total time taken to generate the page: 0.12813 seconds