[ORACLE]TABLESPACE 확인

--테이블스페이스 사용량 사이즈 확인

SELECT   SUBSTR(A.TABLESPACE_NAME,1,30) TABLESPACE,
         ROUND(SUM(A.TOTAL1)/1024/1024,1) "TOTALMB",
         ROUND(SUM(A.TOTAL1)/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024,1) "USEDMB",
         ROUND(SUM(A.SUM1)/1024/1024,1) "FREEMB",
         ROUND((ROUND(SUM(A.TOTAL1)/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024,1))/ROUND(SUM(A.TOTAL1)/1024/1024,1)*100,2) "USED%"
FROM
         (SELECT   TABLESPACE_NAME,0 TOTAL1,SUM(BYTES) SUM1,MAX(BYTES) MAXB,COUNT(BYTES) CNT
          FROM     DBA_FREE_SPACE
          GROUP BY TABLESPACE_NAME
          UNION
          SELECT   TABLESPACE_NAME,SUM(BYTES) TOTAL1,0,0,0
          FROM     DBA_DATA_FILES
          GROUP BY TABLESPACE_NAME) A
GROUP BY A.TABLESPACE_NAME

-- 계정별 default tablespace로  확인
SELECT USERNAME,DEFAULT_TABLESPACE
FROM DBA_USERS
WHERE ACCOUNT_STATUS='OPEN'
AND DEFAULT_TABLESPACE='SYSTEM'

--SYSTEM TABLESPACE에 저장된 OBJECT 확인
SELECT OWNER, SEGMENT_NAME,SEGMENT_TYPE, BYTES FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME ='SYSTEM'
AND OWNER NOT IN ('SYS','SYSTEM')