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