2020년 1월 2일 IT - Oracle Table Space 조회

-- TableSpace 확인 쿼리

 

SELECT
"Check Time"
,TS_INFO.tablespace_name
,TS_INFO."MAX SIZE(MB)"
,TS_INFO."USED(MB)"
,TS_INFO."USED %"
,TS_INFO."STATUS"
FROM (
SELECT
SYSDATE "Check Time"
,tablespace_name
,TO_CHAR(round((total_size/1024),4),'999,990.999') "MAX SIZE(MB)"
,TO_CHAR(round((used/1024),4),'999,990.999') "USED(MB)"
,TO_CHAR((total_size/1024)-(round((used/1024),4)),'999,990.999') "AVAIL(MB)"
,TO_CHAR(round((used/total_size),4)*100,'990.99') "USED %"
,TO_CHAR(round(1-(used/total_size),4)*100,'909.99') "FREE %"
,CASE WHEN (round(1-(used/total_size),4)*100) <= '01' THEN 'Full'
WHEN (round(1-(used/total_size),4)*100) <= '10' THEN 'Risk'
WHEN (round(1-(used/total_size),4)*100) <= '20' THEN 'Warning'
ELSE 'Good' END "STATUS"

FROM (
SELECT
b.tablespace_name ,
CASE When b.AUTOEXTENSIBLE = 'YES' Then b.MAXBYTES /1024
ELSE b.bytes /1024
END total_size,
((b.bytes - sum(nvl(a.bytes,0)))) / 1024 used,
(sum(nvl(a.bytes,0))) / 1024 free_size
FROM DBA_FREE_SPACE a, DBA_DATA_FILES b
WHERE a.file_id(+) = b.file_id
AND b.tablespace_name IN ('TRTDATA') -- 확인할 Tablespace 명을 추가 한다.
GROUP BY b.tablespace_name, b.AUTOEXTENSIBLE, b.MAXBYTES, b.file_name, b.bytes
)
) TS_INFO, DBA_FREE_SPACE DFS
WHERE TS_INFO.TABLESPACE_NAME = DFS.TABLESPACE_NAME
AND TS_INFO.TABLESPACE_NAME NOT LIKE '%SYS%'
GROUP BY TS_INFO.tablespace_name,"MAX SIZE(MB)","USED(MB)","AVAIL(MB)","USED %","STATUS","Check Time"
ORDER BY "USED %";

 

-- 테이블 스페이스 확인
SELECT * FROM USER_TABLESPACES;

 

-- CURSOR수 확인
SELECT count(*) cnt, sid FROM v$open_cursor
GROUP BY sid
ORDER BY cnt DESC

 

※ 이 글은 개인적인 공부를 위해 작성함을 알려드립니다.