hOW TO FIND TABLE SIZE [message #215799] |
Tue, 23 January 2007 23:38 |
sponguru_dba
Messages: 51 Registered: May 2005 Location: Bangalore
|
Member |
|
|
HI
I know "SELECT * FROM DBA_TABLES WHERE TABLE_NAME='<>' AND OWNER='<>'"
but i don't know how to find size of table accupined
how to find out size of table
thanks in Advance
sreenivasaRao.P
|
|
|
|
Re: hOW TO FIND TABLE SIZE [message #215805 is a reply to message #215801] |
Tue, 23 January 2007 23:57 |
Mohammad Taj
Messages: 2412 Registered: September 2006 Location: Dubai, UAE
|
Senior Member |
|
|
Hi,
yes it is correct...
SQL> create table ORACLE as select * from all_objects;
Table created.
SQL> exec dbms_stats.gather_table_stats('SCOTT','ORACLE');
PL/SQL procedure successfully completed.
SQL> column segment_name format a25
SQL> column table_name format a25
SQL> select segment_name,round((bytes/1024),2) ||'kb' "SIZE"
2 from user_segments
3 where segment_name = 'ORACLE';
SEGMENT_NAME SIZE
------------------------- ------------------------------------------
ORACLE 5120kb
SQL> --Table Size
SQL> select table_name, round((blocks*8),2)||'kb' "SIZE"
2 from user_tables
3 where table_name = 'ORACLE';
TABLE_NAME SIZE
------------------------- ------------------------------------------
ORACLE 4504kb
SQL> --Actual Data Size in Table
SQL> select table_name,round((num_rows*avg_row_len/1024),2)||'kb' "SIZE"
2 from user_tables
3 where table_name = 'ORACLE';
TABLE_NAME SIZE
------------------------- ------------------------------------------
ORACLE 3575.67kb
SQL>
Note :
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
regards
Taj
|
|
|