, , ,

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'
)
Читать полностью

, ,

Free space in tablespace oracle

пятница, 3 февраля 2012 г. 0 коммент.

This small reports the tablespace name, alloc size, free space, used space, extend size, total size in all the tablespaces in Oracle database.

select a.tablespace_name ,
round(a.bytes_alloc / 1024 / 1024, 2) m_alloc,
round(nvl(b.bytes_free, 0) / 1024 / 1024, 2) m_free,
round((a.bytes_alloc - nvl(b.bytes_free, 0)) / 1024 / 1024, 2) m_used,
round((maxbytes - a.bytes_alloc)/1024/1024, 2) m_extend,
round(maxbytes/1024/1024,2) Max
from ( select f.tablespace_name,
sum(f.bytes) as bytes_alloc,
sum(decode(f.autoextensible, 'YES',greatest(f.maxbytes, f.bytes),'NO', f.bytes)) as maxbytes
from dba_data_files f
group by tablespace_name
union all
select f.tablespace_name,
sum(f.bytes) as bytes_alloc,
sum(decode(f.autoextensible, 'YES',greatest(f.maxbytes, f.bytes),'NO', f.bytes)) as maxbytes
from dba_temp_files f
group by tablespace_name
) a,
( select f.tablespace_name,
sum(f.bytes) bytes_free
from dba_free_space f
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name (+);
Читать полностью