ORA-00604 ORA-02429 테이블스페이스 DROP 시 발생한 제약조건 오류

데이터가 있는 테이블스페이스를 삭제하려고 아래 명령을 수행하였는데 ORA-00604, ORA-02429가 발생하였다.

drop tablespace 테이블스페이스명
including contents and datafiles
cascade constraints;

ORA-00604: 순환 SQL 레벨 1 에 오류가 발생했습니다
ORA-02429: 고유/기본 키 적용을 위한 인덱스를 삭제할 수 없습니다.

 

원인

제약조건 때문에 발생한 오류이다.
 
나는 두 테이블스페이스에 data와 index를 분리하여 관리하고있었는데
index가 저장된 테이블스페이스를 먼저 drop하려고 하니
data가 저장된 테이블스페이스 내 테이블에
인덱스에 대하여 제약조건이 걸려있어 오류가 발생한 것이었다.
data가 저장된 테이블스페이스부터 drop 을 진행하였다면 오류가 발생하지 않았을것이다.
 

해결방법

DROP TABLESPACE를 수행하기 위해서는 해당 테이블 스페이스 내 인덱스에 걸린 제약조건을 삭제해주어야한다.

-- 제약조건 삭제 명령어
ALTER TABLE 스키마명.테이블명 DISABLE CONSTRAINT 제약조건명;
ALTER TABLE 스키마명.테이블명 DISABLE CONSTRAINT 제약조건명 CASCADE; -- PK의 경우

-- 특정 테이블스페이스의 인덱스에 걸린 제약조건 삭제 스크립트
select 'ALTER TABLE ' || owner || '.' || table_name || ' DISABLE CONSTRAINT '|| constraint_name||' CASCADE;'
from dba_constraints
where (index_owner,index_name)
in (
	select owner,index_name
	from dba_indexes
	where tablespace_name = '테이블스페이스명'
)
;