반응형
|
출처 : http://javaservice.net/
--테이블별 현황보기
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;
/
set serveroutput on
exec show_space( 'EMP' );
--테이블스페이스 현황보기
column dummy noprint
column pct_used format 999.9 heading "%|Used"
column name format a16 heading "Tablespace Name"
column Kbytes format 999,999,999 heading "KBytes"
column used format 999,999,999 heading "Used"
column free format 999,999,999 heading "Free"
column largest format 999,999,999 heading "Largest"
break on report
compute sum of kbytes on report
compute sum of free on report
compute sum of used on report
select nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc kbytes,
kbytes_alloc-nvl(kbytes_free,0) used,
nvl(kbytes_free,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/
kbytes_alloc)*100 pct_used,
nvl(largest,0) largest
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
tablespace_name
from sys.dba_data_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
order by 1
/
--테이블스페이스 현황보기(덤으로)
SELECT
sysdate inst_date ,
ddf.tablespace_name tspace ,
ROUND(fst.FRAG_INDEX,2) fragindex ,
allspc.KB totalaloc ,
(ddf.fretot - fst.total_free ) /1024 totalused ,
fst.total_free /1024 totalfree ,
ROUND((fst.total_free /ddf.fretot),2) * 100 frepct ,
fst.max_hole /1024 maxdiv ,
ROUND((fst.avg_hole /1024),2) avgdiv ,
fst.holes num_of_div
FROM dual,
(SELECT
tablespace_name tablespace_name ,
SUM(bytes) fretot
FROM dba_data_files
WHERE tablespace_name != 'TEMP'
group by tablespace_name) ddf,
(
SELECT tablespace_name tablespace_name,
SQRT(MAX(BLOCKS)/SUM(BLOCKS))*(100/SQRT(SQRT(COUNT(BLOCKS)) ))
FRAG_INDEX,
SUM(bytes) total_free,
MAX(bytes) max_hole,
AVG(bytes) avg_hole,
COUNT(*) holes
FROM dba_free_space
WHERE tablespace_name != 'TEMP'
GROUP BY tablespace_name
) fst,
(select table_space tablespace_name , sum(x) KB from
(select tablespace_name table_space,sum(BYTES/1024) x from
dba_data_files
where maxbytes=0 and
tablespace_name != 'TEMP'
group by tablespace_name
union all
select tablespace_name table_space,sum(MAXBYTES/1024) x from
dba_data_files
where tablespace_name != 'TEMP'
group by tablespace_name
)group by table_space) allspc
WHERE ddf.tablespace_name = fst.tablespace_name(+)
|
