"ORACLE 테이블스페이스"의 두 판 사이의 차이

2.4.2 TABLESPACE별 사이즈[편집]

SELECT  Substr(df.tablespace_name,1,20) "Tablespace Name",
        Substr(df.file_name,1,80) "File Name",
        Round(df.bytes/1024/1024,0) "Size (M)",
        decode(e.used_bytes,NULL,0,Round(e.used_bytes/1024/1024,0)) "Used (M)",
        decode(f.free_bytes,NULL,0,Round(f.free_bytes/1024/1024,0)) "Free (M)",
        decode(e.used_bytes,NULL,0,Round((e.used_bytes/df.bytes)*100,0)) "% Used"
FROM    DBA_DATA_FILES DF,
       (SELECT file_id,
               sum(bytes) used_bytes
        FROM dba_extents
        GROUP by file_id) E,
       (SELECT sum(bytes) free_bytes,
               file_id
        FROM dba_free_space
        GROUP BY file_id) f
WHERE    e.file_id (+) = df.file_id
AND      df.file_id  = f.file_id (+)
ORDER BY df.tablespace_name,
         df.file_name

2.4.3 테이블 TABLESPACE 사이즈[편집]

-- 테이블 사이즈  

SELECT A.SEGMENT_NAME
      , A.SEGMENT_TYPE      
      , ROUND(SUM(A.BYTES)/1024/1024/1024) "SIZE_GB"      
  FROM DBA_SEGMENTS A
     , DBA_TABLES B
 WHERE A.SEGMENT_NAME = B.TABLE_NAME
   AND A.SEGMENT_TYPE IN ('TABLE','TABLE PARTITION')
--     AND A.OWNER = '유저아이디'
 GROUP BY A.SEGMENT_NAME
        , A.SEGMENT_TYPE

2.4.4 인덱스 TABLESPACE 사이즈[편집]

-- INDEX SIZE

SELECT A.SEGMENT_NAME
      , A.SEGMENT_TYPE
      , ROUND(SUM(A.BYTES)/1024/1024/1024) "SIZE_GB"
  FROM DBA_SEGMENTS A
     , DBA_INDEXES B
 WHERE A.SEGMENT_NAME = B.INDEX_NAME   
   AND A.SEGMENT_TYPE IN ('INDEX','INDEX PARTITION')                    
--     AND A.OWNER = '유저아이디'
 GROUP BY A.SEGMENT_NAME
        , A.SEGMENT_TYPE
 ORDER BY 2 DESC;

2.4.5 LOB TABLESPACE 사이즈[편집]

-- LOB 사이즈

SELECT TABLE_NAME
      , sum(bytes)
   FROM (SELECT B.table_name AS table_name
              , A.bytes
           FROM DBA_SEGMENTS A
              , DBA_LOBS B
          WHERE A.segment_name = B.segment_name                          
          )
group by table_name;

2.4.6 데이터파일 별 테이블스페이스 사이즈 조회[편집]

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

2.4.7 TABLESPACE별 사용하는 파일의 크기 합[편집]

SELECT SUBSTRB(TABLESPACE_NAME, 1, 10) AS TABLESPACE
     , TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES
     , TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS
  FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME 
UNION ALL 
SELECT '총계', TO_CHAR(SUM(BYTES), '9,999,999,999,990') AS BYTES, TO_CHAR(SUM(BLOCKS), '9,999,999,990') AS BLOCKS 
  FROM DBA_DATA_FILES;

SELECT A.TABLESPACE_NAME AS "TABLESPACE"
     , A.INIT AS "INIT(K)"
     , A.NEXT AS "NEXT(K)"
     , A.MIN AS "MIN"
     , A.MAX AS "MAX"
     , A.PCT_INC AS "PCT_INC(%)"
     , TO_CHAR(B.TOTAL, '999,999,999,990') AS "총량(바이트)"
     , TO_CHAR(C.FREE, '999,999,999,990') AS "남은량(바이트)"
     , TO_CHAR(B.BLOCKS, '9,999,990') AS "총블럭"
     , TO_CHAR(D.BLOCKS, '9,999,990') AS "사용블럭"
     , TO_CHAR(100 * NVL(D.BLOCKS, 0) / B.BLOCKS, '999.99') AS "사용율%"
  FROM (SELECT TABLESPACE_NAME
                , INITIAL_EXTENT / 1024 AS INIT
                , NEXT_EXTENT / 1024 AS NEXT
                , MIN_EXTENTS AS MIN
                , MAX_EXTENTS AS MAX
                , PCT_INCREASE AS PCT_INC
           FROM      DBA_TABLESPACES) A
     , (SELECT    TABLESPACE_NAME, SUM(BYTES) AS TOTAL, SUM(BLOCKS) AS BLOCKS
           FROM      DBA_DATA_FILES
           GROUP BY  TABLESPACE_NAME) B
     , (SELECT    TABLESPACE_NAME, SUM(BYTES) AS FREE
           FROM      DBA_FREE_SPACE
           GROUP BY  TABLESPACE_NAME) C
     , (SELECT    TABLESPACE_NAME, SUM(BLOCKS) AS BLOCKS
           FROM      DBA_EXTENTS
           GROUP BY  TABLESPACE_NAME) D
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME(+) 
   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME(+) 
   AND A.TABLESPACE_NAME = D.TABLESPACE_NAME(+) 
 ORDER BY A.TABLESPACE_NAME;

SELECT X.TABLESPACE_NAME
     , TOTAL_SIZE / 1024 / 1024 TOTAL_SIZE
     , USED_SIZE / 1024 / 1024 USED_SIZE
     , (ROUND(USED_SIZE / TOTAL_SIZE, 2)) * 100 USED_RATIO
  FROM (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL_SIZE
           FROM      DBA_DATA_FILES
           GROUP BY  TABLESPACE_NAME) X
     , (SELECT    TABLESPACE_NAME, SUM(BYTES) USED_SIZE
           FROM      DBA_EXTENTS
           GROUP BY  TABLESPACE_NAME) Y
 WHERE X.TABLESPACE_NAME = Y.TABLESPACE_NAME(+) AND Y.USED_SIZE > .9 * X.TOTAL_SIZE;

SELECT TABLESPACE_NAME
     , TABLE_NAME 
  FROM DBA_TABLES 
 WHERE TABLESPACE_NAME = UPPER('&TABLESPACE명') 
 ORDER BY TABLESPACE_NAME
     , TABLE_NAME;

SELECT DISTINCT E.SEGMENT_NAME, E.TABLESPACE_NAME, F.FILE_NAME
  FROM DBA_EXTENTS E
     , DBA_DATA_FILES F
 WHERE E.FILE_ID = F.FILE_ID 
   AND E.SEGMENT_TYPE = 'TABLE' 
   AND E.TABLESPACE_NAME NOT IN ('SYSTEM', 'TOOLS');

SELECT OWNER
     , TABLESPACE_NAME
     , SUM(DECODE(SEGMENT_TYPE, 'TABLE', 1, 0))          TAB
     , SUM(DECODE(SEGMENT_TYPE, 'INDEX', 1, 0))          IDX
     , SUM(DECODE(SEGMENT_TYPE, 'LOBINDEX', 1, 0))       LOB_IDX
     , SUM(DECODE(SEGMENT_TYPE, 'LOBSEGMENT', 1, 0))     LOB_SEG
  FROM DBA_SEGMENTS 
 WHERE SEGMENT_TYPE IN ('TABLE', 'INDEX', 'LOBINDEX','LOBSEGMENT')
 GROUP BY OWNER, TABLESPACE_NAME;

SELECT SUBSTRB(A.FILE_NAME, 1, 40) AS FILE_NAME
     , A.FILE_ID
     , B.FREE_BYTES / 1024 AS FREE_BYTES
     , B.MAX_BYTES / 1024 AS MAX_BYTES
  FROM DBA_DATA_FILES A
     , (SELECT FILE_ID, SUM(BYTES) AS FREE_BYTES, MAX(BYTES) AS MAX_BYTES
          FROM DBA_FREE_SPACE
         GROUP BY  FILE_ID) B
 WHERE A.FILE_ID = B.FILE_ID 
   AND A.TABLESPACE_NAME = UPPER('&TABLESPACE명') 
 ORDER BY A.FILE_NAME;

SELECT TABLESPACE_NAME
     , OWNER
     , SEGMENT_NAME
     , SEGMENT_TYPE
     , EXTENTS
     , MAX_EXTENTS
  FROM SYS.DBA_SEGMENTS S 
 WHERE EXTENTS / MAX_EXTENTS > .8 AND MAX_EXTENTS > 0 
 ORDER BY TABLESPACE_NAME, OWNER, SEGMENT_NAME;

   세그먼트의 이름,
   해당 세그먼트의 최대 익스텐트 개수,
   익스텐트 아이디
   해당 세그먼트의 최대 익스텐트 개수 - 최대 익스텐트 아이디     

SELECT B.SEGMENT_NAME
     , B.MAX_EXTENTS
     , MAX(C.EXTENT_ID) AS EXTENT_ID
     , B.MAX_EXTENTS - MAX(C.EXTENT_ID) AS DIFF
  FROM USER_TABLESPACES A
     , USER_SEGMENTS B
     , USER_EXTENTS C
 WHERE A.EXTENT_MANAGEMENT = 'DICTIONARY' 
   AND B.TABLESPACE_NAME = A.TABLESPACE_NAME 
   AND C.SEGMENT_NAME = B.SEGMENT_NAME 
 GROUP BY B.SEGMENT_NAME, B.MAX_EXTENTS 
HAVING B.MAX_EXTENTS - MAX(C.EXTENT_ID) <= 50 
 ORDER BY B.MAX_EXTENTS - MAX(C.EXTENT_ID);

         ex) 홍길동→일지매로 업데이트 시 홍길동이 Undo Data

SELECT A.TABLESPACE_NAME,A.STATUS, B.TOTAL_MB, A.USE_MB,
       ROUND(RATIO_TO_REPORT(A.USE_MB) OVER(PARTITION BY A.TABLESPACE_NAME) * 100)||'%' AS PCT
  FROM (SELECT TABLESPACE_NAME, STATUS,
               ROUND(SUM(BYTES/1024/1024)) USE_MB
          FROM DBA_UNDO_EXTENTS
         GROUP BY TABLESPACE_NAME,STATUS
       )A,
       (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES)/1024/1024) TOTAL_MB
          FROM DBA_DATA_FILES
         WHERE TABLESPACE_NAME LIKE 'UNDO%'
         GROUP BY TABLESPACE_NAME
       )B
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
 ORDER BY 1,2

DROP TABLESPACE UNDO_T1 INCLUDING CONTENTS AND DATAFILES;

--: EXTENTS = 현재 할당된 EXTENT의 수 --: EXTENDS = 마지막 트랜잭션에 의해 할당된 EXTENT의 수

SELECT SUBSTRB(A.SEGMENT_NAME, 1, 10) AS SEGMENT_NAME
     , SUBSTRB(A.TABLESPACE_NAME, 1, 10) AS TABLESPACE_NAME
     , TO_CHAR(A.SEGMENT_ID, '99,999') AS SEG_ID
     , TO_CHAR(A.MAX_EXTENTS, '999,999') AS MAX_EXT
     , TO_CHAR(B.EXTENTS, '999,999') AS EXTENTS
     , TO_CHAR(B.EXTENDS, '999,999') AS EXTENDS
     , TO_CHAR((A.INITIAL_EXTENT + (B.EXTENTS - 1) * A.NEXT_EXTENT) / 1000000, '9,999.999') AS "ALLOC(MB)"
     , TO_CHAR(XACTS, '9,999') AS XACTS
  FROM DBA_ROLLBACK_SEGS A
     , V$ROLLSTAT B
 WHERE A.SEGMENT_ID = B.USN(+) ORDER BY 1;

SQL> alter TABLESPACE TS_TEST offline ;

Normal이 수행되지 못할 때(Tablespace의 Data file 이상) 사용하는 방법

SELECT *
  FROM DBA_SEGMENTS
 WHERE TABLESPACE_NAME ='TS_TEST_D01';

SELECT OWNER
     , TABLE_NAME
     , TABLESPACE_NAME
     , 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE TABLESPACE TS_TEST_D11;'                    -- 임시로 생성된 T/S로 이동 
     , 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' MOVE TABLESPACE '||TABLESPACE_NAME||';' MV_TS    -- 다시 원래 테이블스페이스로 이동 
  FROM DBA_TABLES
 WHERE TABLESPACE_NAME ='TS_TEST_D01';  -- TS_TEST_D01을 사용하는 테이블

SELECT OWNER
     , TABLE_NAME
     , INDEX_NAME     
     , TABLESPACE_NAME
     , 'ALTER INDEX  '||OWNER||'.'||INDEX_NAME||' REBUILD TABLESPACE TS_TEST_I11;'   RBD_TS
  FROM DBA_INDEXES
 WHERE TABLESPACE_NAME ='TS_TEST_I01'; -- TS_TEST_I01을 사용하는 인덱스

select owner,table_name 
     , 'alter table '||owner||'.'||table_name||' move lob('||col||') store as (tablespace TS_TEST_D01);' mv_lob_ts
  from (
 select a.owner,a.table_name
      , listagg(a.column_name,',') within group (order by 1) col 
   from dba_lobs a where table_name in
                                      (
                                       SELECT DISTINCT TABLE_NAME
                                         FROM DBA_TAB_COLUMNS
                                        WHERE OWNER = 'TEST'
                                          AND DATA_TYPE LIKE '%LOB'
                                      )
group by  a.owner,a.table_name                       
)
--order by 1,2                        
;

ALTER TABLESPACE [asis-TABLE] RENAME TO [tobe-TABLE]