Home » RDBMS Server » Server Administration » hOW TO FIND TABLE SIZE
hOW TO FIND TABLE SIZE [message #215799] Tue, 23 January 2007 23:38 Go to next message
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 #215801 is a reply to message #215799] Tue, 23 January 2007 23:52 Go to previous messageGo to next message
sponguru_dba
Messages: 51
Registered: May 2005
Location: Bangalore
Member

Hi

I find the solutions

SELECT SUM(BYTES/1024/1024) FROM DBA_SEGMENTS WHERE SEGMENT_NAME='<>' AND OWNER='<>'
Re: hOW TO FIND TABLE SIZE [message #215805 is a reply to message #215801] Tue, 23 January 2007 23:57 Go to previous message
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
Previous Topic: Temp tablepace max size question
Next Topic: Problem faced while patching 10.2.0.3 Patch set 2 on 10g (5337014)
Goto Forum:
  


Current Time: Fri Sep 20 04:51:02 CDT 2024