/* tablespace size현황 */
select
a.tablespace_name name,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) bytes,
sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id ) -
sum(a.bytes)/count( distinct b.file_id ) used,
sum(a.bytes)/count( distinct b.file_id ) free,
100 * ( (sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) -
(sum(a.bytes)/count( distinct b.file_id ) )) /
(sum(b.bytes)/count( distinct a.file_id||'.'||a.block_id )) pct_used
from dba_free_space a,
dba_data_files b
where a.tablespace_name = b.tablespace_name
group by a.tablespace_name
/
select a.tablespace_name, b.total total, b.total - a.free used,
a.free free , round(100 * ((b.total - a.free)/b.total))||'%' used_space
from (select tablespace_name, sum(bytes) free from dba_free_space group by tablespace_name) a,
(select tablespace_name, sum(bytes)total from dba_data_files group by tablespace_name) b
where b.tablespace_name = a.tablespace_name
/
/* tablespace extent현황 */
select segment_name object_name , segment_type object_type,
tablespace_name , round(sum(bytes)/1024/1024,2) Mb,
sum(decode(extent_id,0, bytes, 0))/1024 initial_ex,
sum(decode(extent_id, 1, bytes, 0))/1024 next_ex,
max(extent_id)+1 extents , sum(bytes)/1024 ttlsize
from user_extents Where segment_type='TABLE'
group by segment_name, segment_type, tablespace_name
/
/* tablespace 사용현황 */
select
a.tablespace_name ts_name,
a.file_name f_name,
a.bytes/1024/1024 Mbytes,
b.phyrds p_reads,
b.phywrts p_writes,
b.phyblkrd p_b_reads,
b.phyblkwrt p_b_writes
from dba_data_files a,
v$filestat b
where a.file_id =b.file#
/
/* system tablespace 사용정보 */
select ts.name tb_space,
tf.file# file_id,
tf.blocks blocks,
sum(f.length) free,
count(*) pieces,
max(f.length) biggest,
min(f.length) smallest,
round(avg(f.length)) average,
sum(decode(sign(f.length-5),-1,f.length)) dead
from sys.fet$ f,
sys.file$ tf,
sys.ts$ ts
where ts.ts#=f.ts#
and ts.ts#=tf.ts#
and tf.file#=f.file#
group by ts.name,tf.file#,tf.blocks
order by ts.name,tf.file#,tf.blocks
/
/* table 사용현황 */
create or replace
procedure show_space
( p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE' )
as
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
procedure p( p_label in varchar2, p_num in number )
is
begin
dbms_output.put_line( rpad(p_label,40,'.') || p_num );
end;
begin
dbms_space.free_blocks
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
freelist_group_id => 0,
free_blks => l_free_blks );
dbms_space.unused_space
( segment_owner => p_owner,
segment_name => p_segname,
segment_type => p_type,
total_blocks => l_total_blocks,
total_bytes => l_total_bytes,
unused_blocks => l_unused_blocks,
unused_bytes => l_unused_bytes,
LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId,
LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId,
LAST_USED_BLOCK => l_LAST_USED_BLOCK );
p( 'Free Blocks', l_free_blks );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
end;
/
exec show_space( 'EMP' );
[출처] 테이블스페이스가 얼마나 사용중인지...알수있는방법|작성자 새내기
