/********************************************************************************************
-- Title : [10g] Tablespace와 datafile 정보 출력 뷰
-- Key word :
********************************************************************************************/
create or replace view sys.sp_tablespace0
as
select y.ts#, y.tablespace_name "TABLESPACE", y.file#, y.file_name
, to_char(y.bytes/1024/1024, '999,999.00') "FILE_SIZE_mb"
, to_char(y.inc/1024, '999,999.00') || ' kb' "GROWTH"
, to_char(y.maxbytes/1024/1024, '999,999.00') "MAX_SIZE_mb"
, to_char(y.user_bytes/1024/1024, '999,999.00')
|| '(' || to_char(y.user_bytes / y.bytes * 100, '999.00') || '%)' "FREE_SIZE_mb"
, y.status "FILE_STATUS", y.enabled--, y.autoextensible
, '■' as "■", x.status "TS_STATUS"
, x.contents, x.default_ts, x.bigfile, x.logging, x.allocation_type "ALLOCATION"
, x.extent_management "EXT_MNG", x.segment_space_management "SEG_MNG"
, to_char(x.initial_extent, '999,999,999') "INIT_EXT"
, to_char(x.next_extent, '999,999,999') "NEXT_EXT"
, to_char(x.min_extents, '999,999,999') "MIN_EXT"
, to_char(x.max_extents, '999,999,999,999') "MAX_EXT"
from
(
select a.tablespace_name, a.status, a.contents, b.default_ts
, a.logging, a.allocation_type, a.plugged_in
, a.extent_management -- 9i 이상부터 사용
, a.segment_space_management -- 9i 이상부터 사용
, a.bigfile -- 10g 이상부터 사용
, a.initial_extent
, a.next_extent
, a.min_extents
, a.max_extents
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
) x
inner join
(
select v.ts#, d.tablespace_name, d.file_id as file#, d.file_name
, d.bytes
, d.increment_by * 8 as Inc
, d.maxbytes
, d.status || '/' || v.status as status
, v.enabled, d.autoextensible
, d.user_bytes
from
(
select file_name, file_id, tablespace_name, bytes, blocks, status
, relative_fno, autoextensible, maxbytes, increment_by
, user_bytes, user_blocks, online_status
from dba_data_files
union all
select file_name, file_id, tablespace_name, bytes, blocks, status
, relative_fno, autoextensible, maxbytes, increment_by
, user_bytes, user_blocks, 'ONLINE' as online_status
from dba_temp_files
) d
inner join
(
select file#, creation_change#, creation_time, ts#
, status, enabled, bytes, blocks, name
from v$datafile
union all
select file#, creation_change#, creation_time, ts#
, status, enabled, bytes, blocks, name
from v$tempfile
) v
on d.file_name = v.name
) y
on x.tablespace_name = y.tablespace_name
order by y.ts#, y.file#;
-- select * from sys.sp_tablespace0;
grant select on sp_tablespace0 to public;