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

2011. 4. 14. 09:15Oracle/Oracle Tip

반응형
대용량 파티션 테이블에 일정기간 데이터만을 보유할 경우 
부분적으로 파티션 테이블 및 테이블 스페이스를 삭제를 해야하는 경우가 있다.

아래 쿼리들은 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
;

반응형

'Oracle > Oracle Tip' 카테고리의 다른 글

split - REGEX_SUBSTR, REGEX_REPLACE  (0) 2022.04.22
sqlplus 에서 ed(edit) 시 vi편집기 사용.  (0) 2011.11.08
long형 replace  (0) 2011.01.24
LONG과 CLOB 에 대한 데이터 이전.  (0) 2011.01.04
tablespace 생성 스크립트  (0) 2010.04.30