tablespace 질문 올립니다.. 일주일째 고민중인데 답이 안나오네요 ㅠ
총 tablespace(인덱스, 테이블 포함)를 지난 세달동안 매일 시간대별로 총사용량을
구하려고하는데요..
아무리 생각해봐도 도저히 답이 나오지 않아서 질문드립니다. 고수님들 부탁드립니다....
이것저것 자료도 찾아보고 사랑넷 고수님들 답변도 다 훓어보고 했는데..
제가 원하는 데이터가 나오질 않네요....ㅠㅠ
부탁드립니다.
제가 사랑넷 고수님들의 답변으로 고민한 쿼리입니다.. 수정만 하면될런지.... 어렵습니다..
SELECT sysdate as base_date ,
A.tablespace_name as tspace ,
--ROUND(B.FRAG_INDEX,2) as fragindex ,
allspc.KB as totalaloc ,
(A.fretot - B.total_free ) /1024 as totalused ,
B.total_free /1024 as totalfree ,
ROUND((B.total_free /A.fretot),2) * 100 as frepct
--B.max_hole /1024 as maxdiv ,
--ROUND((B.avg_hole /1024),2) as avgdiv ,
--B.holes as num_of_div
FROM dual,
( SELECT tablespace_name as tablespace_name ,
SUM(bytes) as fretot
FROM dba_data_files
WHERE tablespace_name != 'TEMP'
group by tablespace_name
) A,
( SELECT tablespace_name as tablespace_name,
--SQRT(MAX(BLOCKS)/SUM(BLOCKS))*(100/SQRT(SQRT(COUNT(BLOCKS)) )) as FRAG_INDEX,
SUM(bytes) as total_free
--MAX(bytes) as max_hole,
--AVG(bytes) as avg_hole,
--COUNT(*) holes
FROM dba_free_space
WHERE tablespace_name != 'TEMP'
GROUP BY tablespace_name
) B,
( select table_space as tablespace_name ,
sum(x) as KB
from
( select tablespace_name as table_space,
sum(BYTES/1024) as x
from dba_data_files
where maxbytes=0
and tablespace_name != 'TEMP'
group by tablespace_name
union all
select tablespace_name as table_space,
sum(MAXBYTES/1024) as x
from dba_data_files
where tablespace_name != 'TEMP'
group by tablespace_name
) group by table_space
) allspc
WHERE A.tablespace_name = B.tablespace_name(+)
and A.tablespace_name = allspc.tablespace_name(+); |