본문 바로가기

프로그램/오라클 관련팁

오라클 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;
출처 : http://energ.tistory.com/entry/Oracle-%EB%82%B4-table%EC%97%90-%EA%B1%B8%EC%B3%90%EC%9E%88%EB%8A%94-constraint-%EC%B0%BE%EA%B3%A0-disable-%ED%95%98%EA%B8%B0
  • custom research papers 2011.12.09 12:15

    존 거기에 걸려 있으며, 때문에 오류 사용할 수 있기 때문에이 게시물을 참을 수가 너무 좋아요. 검색어 dba_constraints를 사용하는 경우 다음 이제 다 잘 될 것입니다. 감사합니다.