Friday, 16 May 2014

How to - Check the table size in Oracle 10g /11g

              To check actual tale size of oracle is really can be hectic for you if you dont know much about oracle blocks, extents, segment and specially PCT free and PCT used concepts, and there is no actual function in oracle to check the table size,
First, we must remember that Oracle table size is constantly changing as DML adds and re moves rows.  Also, we have many other factors that determine the "actual" size of a table:





  • The high water mark for the Oracle table
  • The number of allocated extents
  • The amount of spaced reserved on each block for row expansion (pctfree)
You need to ask yourself exactly which Oracle table size, you wish to query:
  • Do you want only the row space consumed? ( select avg_row_len*num_rows from dba_tables)
     
  • Do you want to include allocated file space for the table? (select . . . from dba_segments)
     
  • Do you want to include un-used extent space? (select . . . from dba_data_files, dba_extents . . )
     
  • Do you want to include un-used space up to the high water mark?  This may over-estimate the real Oracle table size.

 Anyway you can choose to save this query as a function for easy retrieval.selectsegment_name table_name,
sum(bytes)/(1024*1024) table_size_meg
from user_extents
where segment_type='TABLE'
and segment_name = '&table_name'
group by segment_name;


Create your own function for the purpose:

CREATE OR REPLACE FUNCTION get_table_size
(t_table_name VARCHAR2)RETURN NUMBER IS

l_size NUMBER;
BEGIN
SELECT
sum(bytes)/(1024*1024)
INTO l_size
FROM
user_extents
WHERE
segment_type='TABLE'
AND segment_name = t_table_name;

RETURN l_size;
EXCEPTION
WHEN OTHERS THEN
RETURN NULL;
END;

/

Example:
SELECT get_table_size('EMP') Table_Size from dual;

Result:
Table_Size
0.0625