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
