Oracle Table size Oracle Object size How to find oracle Object size
SELECT trunc(SPACE_USED/1024/1024) SPACE_USED_Mb ,trunc(SPACE_ALLOCATED/1024/1024) SPACE_ALLOCATED_Mb,CHAIN_PCENT FROM TABLE(dbms_space.object_space_usage_tbf('SYS', 'COL$', 'TABLE', NULL));
SPACE_USED_MB SPACE_ALLOCATED_MB CHAIN_PCENT
------------- ------------------ -----------
286 304 0
ReplyDeletewith segment_rollup as (
select owner, table_name, owner segment_owner, table_name segment_name from dba_tables
union all
select table_owner, table_name, owner segment_owner, index_name segment_name from dba_indexes
union all
select owner, table_name, owner segment_owner, segment_name from dba_lobs
union all
select owner, table_name, owner segment_owner, index_name segment_name from dba_lobs
), ranked_tables as (
select rank() over (order by sum(blocks) desc) rank, sum(blocks) blocks, r.owner, r.table_name
from segment_rollup r, dba_segments s
where s.owner=r.segment_owner and s.segment_name=r.segment_name
and r.owner=upper('&schema_name')
group by r.owner, r.table_name
)
select rank, round(blocks*8/1024) mb, table_name from ranked_tables
where rank<=20;