/********************************************************************************************
-- Title : [10g] 테이블스페이스 및 데이터 관련 딕셔너리뷰 - ver.dbrang
-- Key word : tabledacpe data file dictionary view
********************************************************************************************/
-- tablespace.sql
select a.tablespace_name, a.status, a.contents, b.default_ts "DEFAULT"
, a.logging, a.allocation_type, a.plugged_in
, a.extent_management -- 9i 이상부터 사용
, a.segment_space_management -- 9i 이상부터 사용
, a.bigfile -- 10g 이상부터 사용
, to_char(a.initial_extent/1024, '999,999.00') "initial_extent(KB)"
, to_char(a.next_extent/1024, '999,999.00') "next_extent(KB)"
, to_char(a.min_extents/1024, '999,999.00') "min_extents(KB)"
, to_char(a.max_extents/1024/1024, '999,999.00') "max_extents(MB)"
from dba_tablespaces a
left outer join (select property_value as ts_nm
, 'DEFAULT' as default_ts
from database_properties
where property_name
in ('DEFAULT_TEMP_TABLESPACE','DEFAULT_PERMANENT_TABLESPACE')
) b
on a.tablespace_name = b.ts_nm
order by a.contents, a.tablespace_name;
-- data_file.sql
select d.tablespace_name, d.file_name
, to_char(d.bytes/1024/1024, '999,999.00') "Size(MB)"
, to_char((d.increment_by * 8), '999,999.00') "Increment(KB)"
, to_char(d.maxbytes/1024/1024, '999,999.00') "MaxSize(MB)"
, d.status, v.status "Online"
, v.enabled, d.autoextensible
from dba_data_files d
inner join v$datafile v
on d.file_name = v.name
order by d.tablespace_name, d.file_name;
-- datafile_ckpt.sql
select file#, name, status, enabled, checkpoint_change#
from v$datafile;
-- datafile_num.sql
select d.name , d.file# "FILE_NO", t.name , t.ts# "TS_NO"
from v$datafile d, v$tablespace t
where d.ts# = t.ts#;
-- datafile_size.sql
SELECT file_name
, d.tablespace_name
, d.bytes as "file_size"
, NVL(SUM(e.bytes),0) as "bytes_used"
, ROUND(NVL(SUM(e.bytes),0) / (d.bytes), 4) * 100 as "percent_used"
, d.bytes - NVL(SUM(e.bytes),0) as "bytes_free"
FROM dba_extents e
RIGHT JOIN dba_data_files d
ON d.file_id = e.file_id
GROUP BY file_name, d.tablespace_name, d.file_id, d.bytes, status
ORDER BY d.tablespace_name, d.file_id;
-- free_space.sql
select tablespace_name, totsize "TotSize(K)",
to_char(frsize*100/totsize, '999.00') "Free(%)", maxsize "MaxExt(K)"
from ( select sum(bytes)/1024 frsize, max(bytes)/1024 maxsize, tablespace_name
from dba_free_space group by tablespace_name) fr,
( select sum(bytes)/1024 totsize, tablespace_name tname
from dba_data_files group by tablespace_name) tt
where fr.tablespace_name = tt.tname
order by tablespace_name;
-- tablespace_usage_metrics
select *
from dba_tablespace_usage_metrics
order by TABLESPACE_NAME;