Range 파티션 테이블 및 테이블 스페이스 삭제 script

대용량 파티션 테이블에 일정기간 데이터만을 보유할 경우 

부분적으로 파티션 테이블 및 테이블 스페이스를 삭제를 해야하는 경우가 있다.

아래 쿼리들은 Range 파티션 테이블 일때 해당 되는 쿼리이다.

partition_name을 날짜로 지정해 주었을 때만 해당기간에 대해서 날짜를 가져와서 출력할 수 있다.

쿼리 실행시 주의할 점은 전부 쿼리를 실행시켜서 script가 출력되게 하여야 한다는 것이다.

무슨말이냐면 아래 쿼리들은 전부 dba_tab_partitions에서 가져온다.

그러므로 파티션 테이블 삭제 스크립트만 돌리고 다음 쿼리(테이블스페이스 삭제 쿼리)를 실행시키고자 하면 데이터가 안나온다.

1. 파티션 테이블 부분 삭제 script 생성

select 'alter table '||table_owner||'.'||table_name||' drop partition '||partition_name||';' script

from dba_tab_partitions 

where table_owner = 'SCOTT'

and   table_name in ('테이블명')

and partition_name>= '20110201' and partition_name<= '20110228'

order by table_name, partition_name

;

subpartition 일때 parttiton 쿼리로 돌리면 엄한 테이블스페이스 및 데이터파일을 삭제할 수가 있으니 유념하자.

2. TABLESPACE 및 DATAFILE 삭제 script 생성

select 'drop tablespace '||tablespace_name||' including contents and datafiles cascade constraints;' script

from dba_tab_partitions 

where table_owner = 'SCOTT'

and   table_name in ('테이블명')

and   partition_name>= '20110201' and partition_name<= '20110228'

order by tablespace_name

;

3. subpartition table 일때 TABLESPACE 및 DATAFILE 삭제 script 생성

select 'drop tablespace '||tablespace_name||' including contents and datafiles cascade constraints;' script

from dba_tab_subpartitions 

where table_owner = 'SCOTT'

and   table_name in ('테이블명')

and   partition_name>= '20110201' and partition_name<= '20110228'

order by tablespace_name

;

4. datafile 삭제 여부 확인(오라클 버그로 간혹 데이터파일이 삭제가 되지 않을 때가 있다. OS 단에서 확인을 해보자)

select c.table_name, b.name tablespace_name, a.name datafile, 'ls '||a.name script

from v$datafile a, v$tablespace b, 

(

    select partition_name, table_name, tablespace_name

    from   dba_tab_partitions

    where  table_name in ('테이블명')

) c

where a.ts#=b.ts#

and   b.name=c.tablespace_name

and   partition_name >= '20110201' and partition_name <= '20110228'

group by c.table_name, b.name, a.name

;