파티션 테이블 TRUNCATE 작업 스크립트

http://kosate.tistory.com/102 퍼옴

http://jhroom.co.kr/23481 -- 여기도 참조

파티션 테이블은 성능향상  혹은 관리적인 목적에 의해 많이 사용되고 있습니다. 관리상의 목적으로 데이터의 보관주기를 적용할경우 특정 파티션만 truncate할수 있습니다. 인덱스가 local 인덱스면 특별한 문제는 없습니다. (경험상)

 20XXXX_trunc_part_table.txt

SET TIME ON
SET TIMING ON
SET ECHO ON
SET PAGES 100
SET LINES 132
COL SEGMENT_NAME FORMAT A30
COL OBJECT_NAME FORMAT A30
COL FROM_OBJECT_NAME FORMAT A47
COL LEVEL FORMAT A3
COL OBJ1 FORMAT 99999
COL OBJ2 FORMAT 99999

spool 20XXXX_trunc_part_table

-- 1. Object 상태확인함. 
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS 
  FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
SELECT INDEX_NAME, PARTITION_NAME, STATUS
  FROM DBA_IND_PARTITIONS
 WHERE STATUS = 'UNUSABLE';
SELECT * FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';
 
-- 2. 세그먼트 크기를 확인함.
SELECT SEGMENT_NAME, PARTITION_NAME, TRUNC(BYTES/1024/1204,0) AS "SIZE(MB)"
  FROM DBA_SEGMENTS
  WHERE SEGMENT_NAME IN ('TEST')
  AND BYTES>  65536
 ORDER BY 2;

-- 3. Global 인덱스가 존재하는지 확인함(있으면 truncate시 unusable됨)
SELECT OWNER, INDEX_NAME, PARTITIONED 
FROM DBA_INDEXES WHERE PARTITIONED <> 'YES' AND  TABLE_NAME IN ( 'TEST' );

-- 4. 관련된 Object를 확인하여 영향도 파악(truncate하여도 영향은 없음)
select to_char(level) as "level",
       '('||c.object_type||')'||c.owner||'.'||c.object_name object_name,
       c.status status,
       c.object_id obj1,
       b.object_id obj2,
       '('||b.object_type||')'||b.owner||'.'||b.object_name from_object_name,
       b.status status,
       'ALTER '||DECODE(B.OBJECT_TYPE,'SYNONYM',DECODE(B.OWNER,'PUBLIC','PUBLIC ')) ||
       DECODE(B.OBJECT_TYPE,'PACKAGE BODY','PACKAGE',B.OBJECT_TYPE)|| ' '||
       DECODE(B.OBJECT_TYPE,'SYNONYM',CASE WHEN B.OWNER <> 'PUBLIC'
       THEN B.OWNER||'.' ELSE ' 'END,B.OWNER||'.')||
       B.OBJECT_NAME||' COMPILE'|| 
       DECODE(B.OBJECT_TYPE,'PACKAGE BODY',' BODY')||';' COMMAND
  from public_dependency a, dba_objects b, dba_objects c
 where b.object_id = a.object_id
   and c.object_id = a.referenced_object_id
connect by prior a.object_id = a.referenced_object_id
start with a.referenced_object_id in (
       select object_id
         from dba_objects
        WHERE OWNER        IN ('SCOTT')
          AND OBJECT_NAME  IN ('TEST')
   );

-- 5. 파티션 테이블의 특정 파티션 truncate작업실시
ALTER TABLE SCOTT.TEST TRUNCATE PARTITION TEST_201007P1;

-- 6. 작업후 해당 파티션의 크기를 확인함.
SELECT SEGMENT_NAME, PARTITION_NAME, 
            TRUNC(BYTES/1024/1204,0) AS "SIZE(MB)"
  FROM DBA_SEGMENTS
  WHERE SEGMENT_NAME IN ('TEST')
    AND BYTES>  65536
 ORDER BY 2;

-- 7. Object 상태 확인(1번 결과와 비교하여 변경사항이 있는지 확인함)
SELECT OWNER, OBJECT_NAME, OBJECT_TYPE, STATUS 
  FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
SELECT INDEX_NAME, PARTITION_NAME, STATUS
  FROM DBA_IND_PARTITIONS
 WHERE STATUS = 'UNUSABLE';
SELECT * FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';

-- 8. Partition Merge는 Package Dependency 때문에 하지 않음