, , ,

Size of table in Oracle

пятница, 2 марта 2012 г. 0 коммент.

Here's a simple request. The request takes into account the size of the table, including indexes, lobs and nested tables.


select '&TABLE' as table_name, sum(dba_segments.BYTES) / 1024 / 1024 as Mb
from dba_segments
where (dba_segments.owner, dba_segments.segment_name) in
(
-- table
select '&OWNER' as owner,
'&TABLE' as segment_name
from dual
union all
-- lob segments
select dba_lobs.owner, dba_lobs.segment_name
from dba_lobs
where dba_lobs.owner = '&OWNER' and
dba_lobs.table_name = '&TABLE'
union all
-- lob index segments
select dba_lobs.owner, dba_lobs.index_name
from dba_lobs
where dba_lobs.owner = '&OWNER' and
dba_lobs.table_name = '&TABLE'
union all
-- nested tables
select dba_nested_tables.owner, dba_nested_tables.table_name
from dba_nested_tables
start with dba_nested_tables.owner = '&OWNER' and
dba_nested_tables.parent_table_name = '&TABLE'
connect by dba_nested_tables.parent_table_name = prior dba_nested_tables.table_name
union all
-- indexes
select dba_indexes.owner, dba_indexes.index_name
from dba_indexes
where dba_indexes.table_owner = '&OWNER' and
dba_indexes.table_name = '&TABLE'
)
Читать полностью