IT박스

Oracle에서 모든 테이블 제약 조건 비활성화

itboxs 2020. 9. 12. 09:17
반응형

Oracle에서 모든 테이블 제약 조건 비활성화

단일 명령으로 Oracle의 모든 테이블 제한을 비활성화하려면 어떻게해야합니까? 이는 단일 테이블, 테이블 목록 또는 모든 테이블에 대한 것일 수 있습니다.

임시 스풀 파일을 작성하지 않는 것이 좋습니다. PL / SQL 블록을 사용하십시오. SQL * Plus에서 실행하거나 패키지 나 프로 시저에 넣을 수 있습니다. USER_TABLES에 대한 조인은 뷰 제약을 피하기 위해 있습니다.

모든 제약 조건 (NOT NULL, 기본 키 등 포함)을 실제로 비활성화하고 싶지는 않습니다. WHERE 절에 constraint_type을 넣어야합니다.

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'ENABLED'
   AND NOT (t.iot_type IS NOT NULL AND c.constraint_type = 'P')
   ORDER BY c.constraint_type DESC)
  LOOP
    dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" disable constraint ' || c.constraint_name);
  END LOOP;
END;
/

제약 조건을 다시 활성화하는 것은 약간 까다 롭습니다. 외래 키 제약 조건에서 참조하려면 먼저 기본 키 제약 조건을 활성화해야합니다. constraint_type에서 ORDER BY를 사용하여 수행 할 수 있습니다. 'P'= 기본 키, 'R'= 외래 키.

BEGIN
  FOR c IN
  (SELECT c.owner, c.table_name, c.constraint_name
   FROM user_constraints c, user_tables t
   WHERE c.table_name = t.table_name
   AND c.status = 'DISABLED'
   ORDER BY c.constraint_type)
  LOOP
    dbms_utility.exec_ddl_statement('alter table "' || c.owner || '"."' || c.table_name || '" enable constraint ' || c.constraint_name);
  END LOOP;
END;
/

제약 조건 간의 종속성을 계산하려면 : 

SET Serveroutput ON
BEGIN
    FOR c IN
    (SELECT c.owner,c.table_name,c.constraint_name
    FROM user_constraints c,user_tables t
    WHERE c.table_name=t.table_name
    AND c.status='ENABLED'
    ORDER BY c.constraint_type DESC,c.last_change DESC
    )
    LOOP
        FOR D IN
        (SELECT P.Table_Name Parent_Table,C1.Table_Name Child_Table,C1.Owner,P.Constraint_Name Parent_Constraint,
            c1.constraint_name Child_Constraint
        FROM user_constraints p
        JOIN user_constraints c1 ON(p.constraint_name=c1.r_constraint_name)
        WHERE(p.constraint_type='P'
        OR p.constraint_type='U')
        AND c1.constraint_type='R'
        AND p.table_name=UPPER(c.table_name)
        )
        LOOP
            dbms_output.put_line('. Disable the constraint ' || d.Child_Constraint ||' (on table '||d.owner || '.' ||
            d.Child_Table || ')') ;
            dbms_utility.exec_ddl_statement('alter table ' || d.owner || '.' ||d.Child_Table || ' disable constraint ' ||
            d.Child_Constraint) ;
        END LOOP;
    END LOOP;
END;
/

단일 명령이 아니지만 여기에 내가 수행하는 방법이 있습니다. 다음 스크립트는 SQL * Plus에서 실행되도록 설계되었습니다. 참고로, 현재 스키마 내에서만 작동하도록 의도적으로 작성했습니다.

set heading off

spool drop_constraints.out

select
    'alter table ' || 
    owner || '.' || 
    table_name || 
    ' disable constraint ' || -- or 'drop' if you want to permanently remove
    constraint_name || ';'
from
    user_constraints;

spool off

set heading on

@drop_constraints.out

드롭하는 항목을 제한하려면 select 문에 where 절을 필터 추가합니다.

  • 특정 유형의 제약 조건 만 삭제하도록 constraint_type을 필터링합니다.
  • 하나 또는 몇 개의 테이블에 대해서만 수행하려면 table_name을 필터링하십시오.

현재 스키마 이상에서 실행하려면 user_constraints가 아닌 all_constraints에서 선택하도록 select 문을 수정합니다.

참고 -어떤 이유로 이전 단락에서 이탤릭체처럼 작동하지 않도록 밑줄을 표시 할 수 없습니다. 누군가가 그것을 고치는 방법을 알고 있다면이 답변을 자유롭게 편집하십시오.

Use following cursor to disable all constraint.. And alter query for enable constraints...

DECLARE

cursor r1 is select * from user_constraints;
cursor r2 is select * from user_tables;

BEGIN
  FOR c1 IN r1
  loop
    for c2 in r2
    loop
       if c1.table_name = c2.table_name and c1.status = 'ENABLED' THEN
        dbms_utility.exec_ddl_statement('alter table ' || c1.owner || '.' || c1.table_name || ' disable constraint ' || c1.constraint_name);
       end if;
    end loop;
  END LOOP;
END;
/

This can be scripted in PL/SQL pretty simply based on the DBA/ALL/USER_CONSTRAINTS system view, but various details make not as trivial as it sounds. You have to be careful about the order in which it is done and you also have to take account of the presence of unique indexes.

The order is important because you cannot drop a unique or primary key that is referenced by a foreign key, and there could be foreign keys on tables in other schemas that reference primary keys in your own, so unless you have ALTER ANY TABLE privilege then you cannot drop those PKs and UKs. Also you cannot switch a unique index to being a non-unique index so you have to drop it in order to drop the constraint (for this reason it's almost always better to implement unique constraints as a "real" constraint that is supported by a non-unique index).

It doesn't look like you can do this with a single command, but here's the closest thing to it that I could find.

This is another way for disabling constraints (it came from https://asktom.oracle.com/pls/asktom/f?p=100:11:2402577774283132::::P11_QUESTION_ID:399218963817)

WITH qry0 AS
       (SELECT    'ALTER TABLE '
               || child_tname
               || ' DISABLE CONSTRAINT '
               || child_cons_name
                 disable_fk
              ,   'ALTER TABLE '
               || parent_tname
               || ' DISABLE CONSTRAINT '
               || parent.parent_cons_name
                 disable_pk
          FROM (SELECT a.table_name child_tname
                      ,a.constraint_name child_cons_name
                      ,b.r_constraint_name parent_cons_name
                      ,LISTAGG ( column_name, ',') WITHIN GROUP (ORDER BY position) child_columns
                  FROM user_cons_columns a
                      ,user_constraints b
                 WHERE a.constraint_name = b.constraint_name AND b.constraint_type = 'R'
                GROUP BY a.table_name, a.constraint_name
                        ,b.r_constraint_name) child
              ,(SELECT a.constraint_name parent_cons_name
                      ,a.table_name parent_tname
                      ,LISTAGG ( column_name, ',') WITHIN GROUP (ORDER BY position) parent_columns
                  FROM user_cons_columns a
                      ,user_constraints b
                 WHERE a.constraint_name = b.constraint_name AND b.constraint_type IN ('P', 'U')
                GROUP BY a.table_name, a.constraint_name) parent
         WHERE child.parent_cons_name = parent.parent_cons_name
           AND (parent.parent_tname LIKE 'V2_%' OR child.child_tname LIKE 'V2_%'))
SELECT DISTINCT disable_pk
  FROM qry0
UNION
SELECT DISTINCT disable_fk
  FROM qry0;

works like a charm

In the "disable" script, the order by clause should be that:

ORDER BY c.constraint_type DESC, c.last_change DESC

The goal of this clause is disable the constraints in the right order.

SELECT 'ALTER TABLE '||substr(c.table_name,1,35)|| 
' DISABLE CONSTRAINT '||constraint_name||' ;' 
FROM user_constraints c, user_tables u 
WHERE c.table_name = u.table_name;

This statement returns the commands which turn off all the constraints including primary key, foreign keys, and another constraints.

with cursor for loop (user = 'TRANEE', table = 'D')

declare
    constr all_constraints.constraint_name%TYPE;
begin
    for constr in
        (select constraint_name from all_constraints
        where table_name = 'D'
        and owner = 'TRANEE')
    loop
        execute immediate 'alter table D disable constraint '||constr.constraint_name;
    end loop;
end;
/

(If you change disable to enable, you can make all constraints enable)

You can execute all the commands returned by the following query :

select 'ALTER TABLE '||substr(c.table_name,1,35)|| ' DISABLE CONSTRAINT '||constraint_name||' ;' from user_constraints c --where c.table_name = 'TABLE_NAME' ;

참고URL : https://stackoverflow.com/questions/128623/disable-all-table-constraints-in-oracle

반응형

'IT박스' 카테고리의 다른 글

background-position-x (background-position-y)는 표준 W3C CSS 속성입니까?  (0) 2020.09.12
컴퓨터 과학에서의 분류 vs. '실제'세계에서의 분류  (0) 2020.09.12
if-modified-since 대 if-none-match  (0) 2020.09.12
Xcode에서 get-task-allow는 무엇을합니까?  (0) 2020.09.12
구성 파일에 암호를 일반 텍스트가 아닌 환경 변수로 저장하는 것이 안전합니까?  (0) 2020.09.12

'IT박스'의 다른글

  • 현재글Oracle에서 모든 테이블 제약 조건 비활성화

관련글

댓글

티스토리툴바