[오라클] 테이블 스페이스 정보 확인 및 테이블 용량 확인

1 .오라클 테이블 스페이스 정보 조회 

SELECT * FROM DBA_TABLESPACES;

 

2. 테이블스페이스별 용량 확인 쿼리문(MB 단위)

SELECT SUBSTR( A.TABLESPACE_NAME, 1, 30 )   AS  TABLESPACE
     , ROUND( SUM( A.TOTAL1 ) / 1024 / 1024 / 1024 , 2 )   AS  "TOTAL_GB"
     , ROUND( SUM( A.TOTAL1 ) / 1024 / 1024 / 1024 , 2 ) - ROUND( SUM( A.SUM1 ) / 1024 / 1024 / 1024 , 2 )    AS  "USED_GB"
     , ROUND( SUM( A.SUM1   ) / 1024 / 1024 / 1024 , 2 )   AS  "FREE_GB"
     , ROUND( ( ( SUM( A.TOTAL1 ) - SUM( A.SUM1 ) ) / SUM( A.TOTAL1 ) ) * 100 , 2 ) AS  "USED%"
  FROM (
        SELECT TABLESPACE_NAME
             , 0                AS  TOTAL1
             , SUM( BYTES )     AS  SUM1
             , MAX( BYTES )     AS  MAXB
             , COUNT( BYTES )   AS  CNT
          FROM DBA_FREE_SPACE
         WHERE TABLESPACE_NAME LIKE 'TS\_%' ESCAPE '\'
         GROUP BY TABLESPACE_NAME
        UNION
        SELECT TABLESPACE_NAME
             , SUM( BYTES )     AS  TOTAL1
             , 0                AS  SUM1
             , 0                AS  MAXB
             , 0                AS  CNT
          FROM DBA_DATA_FILES
         WHERE TABLESPACE_NAME LIKE 'TS\_%' ESCAPE '\'
         GROUP BY TABLESPACE_NAME
     ) A
 GROUP BY A.TABLESPACE_NAME
 ORDER BY A.TABLESPACE_NAME
;

3. 테이블스페이스별 현황 확인 쿼리문(MB 단위)

SELECT TABLESPACE_NAME
     , FILE_NAME
     , ROUND( BYTES     / 1024 / 1024 , 2 )     AS  MB
     , ROUND( USE_BYTES / 1024 / 1024 , 2 )     AS  USE_MB
  FROM (
        SELECT E.TABLESPACE_NAME
             , E.FILE_NAME
             , E.BYTES
             , ( E.BYTES - SUM( F.BYTES ) )     AS  USE_BYTES
          FROM DBA_DATA_FILES   E
             , DBA_FREE_SPACE   F
         WHERE E.FILE_ID            =   F.FILE_ID
--           AND E.TABLESPACE_NAME  LIKE  'TS\_%' ESCAPE '\'
         GROUP BY E.TABLESPACE_NAME
             , E.FILE_NAME
             , E.BYTES
     ) A
;

4. 테이블스페이스별, 파일별 현황 확인 쿼리문(바이트 단위)

SELECT A.TABLESPACE_NAME        AS  "테이블스페이스명"
     , A.FILE_NAME              AS  "파일경로"
     , ROUND( A.BYTES              / 1024 / 1024 / 1024 , 2 )   AS  "총크기(GB)"
     , ROUND( ( A.BYTES - B.FREE ) / 1024 / 1024 / 1024 , 2 )   AS  "사용공간(GB)"
     , ROUND( B.FREE               / 1024 / 1024 / 1024 , 2 )   AS  "여유공간(GB)"
     , TO_CHAR( ( B.FREE / A.BYTES * 100 ) , '999.99' ) || '%'  AS  "여유공간(%)"
  FROM (
        SELECT FILE_ID
             , TABLESPACE_NAME
             , FILE_NAME
             , SUBSTR( FILE_NAME, 1, 200 )  AS  FILE_NM
             , SUM( BYTES )                 AS  BYTES
          FROM DBA_DATA_FILES
         WHERE TABLESPACE_NAME   LIKE   'TS\_%' ESCAPE '\'
         GROUP BY FILE_ID
                , TABLESPACE_NAME
                , FILE_NAME
                , SUBSTR( FILE_NAME, 1, 200 )
     ) A
     , (
        SELECT TABLESPACE_NAME
             , FILE_ID
             , SUM( NVL( BYTES, 0 ) )       AS  FREE
          FROM DBA_FREE_SPACE
         WHERE TABLESPACE_NAME   LIKE   'TS\_%' ESCAPE '\'
         GROUP BY TABLESPACE_NAME
                , FILE_ID
     ) B
 WHERE A.TABLESPACE_NAME    =   B.TABLESPACE_NAME
   AND A.FILE_ID            =   B.FILE_ID
;

5. 테이블 용량 조회

SELECT OWNER
     , TABLE_NAME
     , TRUNC( SUM( BYTES ) / 1024               , 1 )   AS  KB
     , TRUNC( SUM( BYTES ) / 1024 / 1024        , 1 )   AS  MB
     , TRUNC( SUM( BYTES ) / 1024 / 1024 / 1024 , 1 )   AS  GB
  FROM (
        SELECT SEGMENT_NAME     AS  TABLE_NAME
             , OWNER
             , BYTES
          FROM DBA_SEGMENTS
         WHERE SEGMENT_TYPE IN  ( 'TABLE' , 'TABLE PARTITION' )
           AND OWNER           IN   ( 'MEM', 'SALE_IF', 'PARKING_IF', 'LOUNGE_IF', 'NETPION', 'SCSMS' )
        UNION ALL
        SELECT I.TABLE_NAME
             , I.OWNER
             , S.BYTES
          FROM DBA_INDEXES  I
             , DBA_SEGMENTS S
         WHERE S.SEGMENT_NAME   =   I.INDEX_NAME
           AND S.OWNER          =   I.OWNER
           AND S.SEGMENT_TYPE  IN   ( 'INDEX' , 'INDEX PARTITION' )
           AND S.OWNER         IN   ( 'MEM', 'MOB', 'MOB_IF', 'SALE_IF', 'PARKING_IF', 'LOUNGE_IF', 'NETPION', 'SCSMS' )
        UNION ALL
        SELECT L.TABLE_NAME
             , L.OWNER
             , S.BYTES
          FROM DBA_LOBS     L
             , DBA_SEGMENTS S
         WHERE S.SEGMENT_NAME   =   L.SEGMENT_NAME
           AND S.OWNER          =   L.OWNER
           AND S.SEGMENT_TYPE  IN   ( 'LOBSEGMENT' , 'LOB PARTITION' )
           AND S.OWNER         IN   ( 'MEM', 'MOB', 'MOB_IF', 'SALE_IF', 'PARKING_IF', 'LOUNGE_IF', 'NETPION', 'SCSMS' )
        UNION ALL
        SELECT L.TABLE_NAME
             , L.OWNER
             , S.BYTES
          FROM DBA_LOBS     L
             , DBA_SEGMENTS S
         WHERE S.SEGMENT_NAME   =   L.INDEX_NAME
           AND S.OWNER          =   L.OWNER
           AND S.SEGMENT_TYPE   =   'LOBINDEX'
           AND S.OWNER         IN   ( 'MEM', 'MOB', 'MOB_IF', 'SALE_IF', 'PARKING_IF', 'LOUNGE_IF', 'NETPION', 'SCSMS' )
     ) T
-- WHERE OWNER IN UPPER( '&OWNER' )
 GROUP BY TABLE_NAME
        , OWNER
-- HAVING SUM( BYTES ) / 1024 / 1024 > 10  /* IGNORE REALLY SMALL TABLES */
 ORDER BY SUM(BYTES) DESC
;