programing

Oracle에서 모든 테이블 제약 조건 사용 안 함

copyandpastes 2023. 3. 27. 22:25
반응형

Oracle에서 모든 테이블 제약 조건 사용 안 함

단일 명령으로 Oracle의 모든 테이블 구속을 해제하려면 어떻게 해야 합니까?이것은 단일 테이블, 테이블 목록 또는 모든 테이블 중 하나입니다.

임시 스풀 파일은 쓰지 않는 것이 좋습니다.PL/SQL 블록을 사용합니다.SQL*Plus에서 실행하거나 패키지 또는 프로시저에 넣을 수 있습니다.USER_에의 참가TABLES는 뷰의 제약을 피하기 위해 준비되어 있습니다.

모든 제약 조건(NOT NULL, 프라이머리 키 등)을 정말로 무효로 하고 싶은 것은 아닙니다.WHERE 절에 contraint_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;
/

제약조건을 다시 활성화하는 것은 약간 속임수입니다.기본 키 제약조건을 활성화해야 외부 키 제약조건에서 참조할 수 있습니다.이는 contraint_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 구를 추가합니다.-

  • 특정 유형의 제약 조건만 드롭하려면 contraint_type에 따라 필터링합니다.
  • filter on table_name을 사용하여 1개 또는 몇 개의 테이블에 대해서만 수행합니다.

현재 스키마보다 더 많은 스키마에서 실행하려면 select 문을 user_constraints가 아닌 all_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;
/

이것은 DBA/ALL/USER_CONSTERNS 시스템 뷰에 따라 PL/SQL로 스크립팅할 수 있지만, 다양한 세부 사항으로 인해 들리는 것처럼 단순하지 않습니다.작업 순서에 주의해야 하며 고유 인덱스가 존재하는지 여부도 고려해야 합니다.

외부 키로 참조되는 고유 키 또는 기본 키를 삭제할 수 없으며, 다른 스키마의 테이블에 사용자 자신의 기본 키를 참조하는 외부 키가 있을 수 있으므로 ALTER ANY TABLE 권한이 없는 한 이러한 PK 및 UK를 삭제할 수 없습니다.또한 고유 인덱스를 고유하지 않은 인덱스로 전환할 수 없으므로 제약조건을 폐기해야 합니다(이 때문에 고유하지 않은 인덱스에서 지원되는 "실제" 제약조건으로 구현하는 것이 거의 항상 좋습니다).

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;

이 문은 기본 키, 외부 키 및 다른 제약 조건을 포함하여 모든 제약 조건을 해제하는 명령을 반환합니다.

명령어 하나로 이 작업을 수행할 수 없을 것 같습니다만, 여기 제가 찾을 수 있는 가장 가까운 것이 있습니다.

이것은 제약조건을 디세블로 하는 또 다른 방법입니다(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;

아주 효과가 있다

"disable" 스크립트에서 절별 순서는 다음과 같습니다.

ORDER BY c.constraint_type DESC, c.last_change DESC

이 절의 목적은 올바른 순서로 제약을 비활성화하는 것입니다.

루프용 커서 포함(사용자 = 'TRANEE', 테이블 = '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;
/

(disable을 enable로 변경하면 모든 제약 조건을 netable로 할 수 있습니다.)

다음 쿼리에서 반환되는 모든 명령을 실행할 수 있습니다.

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

반응형
저작자표시

'programing' 카테고리의 다른 글

React Formik 외부에서 submit Form 사용  (0) 2023.03.27
노드/도커를 빌드할 때 uid/gid를 가져올 수 없습니다.  (0) 2023.03.27
wp_mail을 사용하여 첨부 메일을 보내는 방법  (0) 2023.03.27
IntelliJ 15, Spring Boot devtools 라이브 새로고침이 작동하지 않음  (0) 2023.03.27
ng-click 함수의 요소 속성을 angularjs에서 가져오는 중  (0) 2023.03.27

'programing'의 다른글

  • 현재글Oracle에서 모든 테이블 제약 조건 사용 안 함

관련글

댓글

티스토리툴바