Script for getting Oracle table size

There is no oracle defined function for getting size of a table. After all if it is easy with one simple query who will require a function. Isn't it?

Anyway you can choose to save this query as a function for easy retrieval.

select

segment_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;

Read more on what all to remember while getting the size of a table. Click here

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

9 comments :