Oracle 내 table에 걸쳐있는 constraint 찾고, disable 하기

Oracle Database Migration이나 데이터 정리 등을 하려 TRUNCATE 명령 등을 수행할 때 ORA-2266에러를 만날 수 있습니다. 

요 에러는 reference constraint key가 걸려 있어 명령을 수행할 수 없다는 에러인데, 내가 작업하려 하는 table의 PK를 누가 참조하고 있다는 의미죠. 

> oerr ora 2266

02266, 00000, "unique/primary keys in table referenced by enabled foreign keys"

// *Cause: An attempt was made to truncate a table with unique or 

//         primary keys referenced by foreign keys enabled in another table.

//         Other operations not allowed are dropping/truncating a partition of a

//         partitioned table or an ALTER TABLE EXCHANGE PARTITION.

// *Action: Before performing the above operations the table, disable the 

//          foreign key constraints in other tables. You can see what 

//          constraints are referencing a table by issuing the following 

//          command:

//          SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = "tabnam";

이런 경우 dba_constraints를 query해서 어떤 table에서 reference 하고 있는지 확인할 수 있습니다. 

간단한 SQL이지만 계속 만들기 귀찮아 한번 정리해 봅니다. 

column r_owner format a20

column r_constraint_name format a30

column owner format a20

column table_name format a30

column constraint_name format a30

column "Constraint Disable COMMAND" format a150

set linesize 200

set pagesize 200

select r_owner,r_constraint_name,owner,table_name,constraint_name,constraint_type

from dba_constraints

where r_constraint_name in (select constraint_name from dba_constraints where table_name = upper('&&TABLE_NAME'))

/

select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';' "Constraint Disable COMMAND"

from dba_constraints

where r_constraint_name in (select constraint_name from dba_constraints where table_name = upper('&TABLE_NAME'))

/

위의 SQL을 돌리면 아래 처럼 reference 하고 있는 table과 해당 constraint가 나옵니다. 

또 요놈들을 disable 해 줄 수 있는 명령도 같이 출력하게 됩니다. 

R_OWNER              R_CONSTRAINT_NAME              OWNER                TABLE_NAME                     CONSTRAINT_NAME                CON

-------------------- ------------------------------ -------------------- ------------------------------ ------------------------------ ---

DBSNMP               BSLN_BASELINES_PK2             DBSNMP               BSLN_STATISTICS                BSLN_STATISTICS_FK             R

DBSNMP               BSLN_BASELINES_PK2             DBSNMP               BSLN_THRESHOLD_PARAMS          BSLN_THRESHOLDS_FK             R

Constraint Disable COMMAND

------------------------------------------------------------------------------------------------------------------------------------------------------

alter table DBSNMP.BSLN_STATISTICS disable constraint BSLN_STATISTICS_FK;

alter table DBSNMP.BSLN_THRESHOLD_PARAMS disable constraint BSLN_THRESHOLDS_FK;

물론 아래처럼 cascade로 다 disable 시킬 수 있지만, 눈으로 한번 확인하고 작업하는 이 신중함 땜에.. ㅋ

ALTER TABLE dept DISABLE PRIMARY KEY CASCADE;

* 여기저기서 좋다는 책 몇권 추천합니다. 고수가 되는 그날까지.. 파이팅!!