Search This Blog

Monday, January 15, 2007

Table Size

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

No comments: