Oracle 9.2; integritäten/constraints deaktivieren/aktivieren

[tplanitz]

Hallo und guten Morgen,

ich habe folgendes script zum deaktivieren von constraints gefunden, modifiziert und ausgeführt.

SPOOL c:\logs\disable_constraints_fks_log.sql
SET linesize 1000
SET pagesize 500
SELECT    'ALTER TABLE '
       || LOWER (table_name)
       || CHR (10)
       || 'DISABLE CONSTRAINT '
       || LOWER (constraint_name)
       || ';'
  FROM user_constraints
  AND r_owner = 'DB_WAREN'
AND status = 'ENABLED'
/
spool off

Meine 2 Fragen sind:
1.) kann ich davon ausgehen das die dann wirklich DEAKTIVIERT sind, weil SQLTools ändert den status der FK constraints in der Objektansicht nicht von enabled nach disabled
wenn ich die Tabelle abfrage mit (siehe code) scheint es das die constraints immer noch aktiv sind obwohl im laut log 22 einträge disabled wurden. Auch ein commit, ändert nicht die Anzeige. (Ich nutze SQLTools für Oracle)

select * from user_constraints where owner = 'DB_WAREN'

2.) Ich möchte das gerne automatisiert aufrufen dann muß ich erst die FK constraints disablen und dann die PK disablen, würdet Ihr das auch so machen oder gibts da was bessere? Ich frage nur weil in MS SQL gibt es sowas hier:

/*Deaktivierung*/
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

/'Aktivierung*/
EXEC sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT ALL'

Vielen Dank für Ideen.
Gruß Thorsten

 

[Exceptionfault]

Zunächst einmal musst du aufpassen welche Constraints du wirklich DISABLEN möchtest. Oracle kennt
insgesamt 4 Typen:

• U = Unique Constraint
• C = Check Constraint (z.B. NOT NULL)
• R = Foreign Key (REFERENCE)
• P = Primary Key

Dein Script generiert ein SQL Script welches ALLE Constraints deaktiviert also auch alle NOT NULL
Constraints. Willst du das wirklich? Relevant ist die Spalte "CONSTRAINT_TYPE" in der Tabelle
USER_CONSTRAINTS.

Wenn du feststellen willst, ob die Constraints wirklich deaktiviert sind hilft die Spalte STATUS.
Folgendes SQL zeigt alle aktivierten FK und PK Constraints.

SELECT  TABLE_NAME, 
        CONSTRAINT_NAME
FROM    USER_CONSTRAINTS
WHERE   STATUS          = 'ENABLED'
AND     CONSTRAINT_TYPE IN ( 'R', 'P' );

Für 2. könnte man sich eine kleine PL/SQL Prozedur schreiben, etwa so...

CREATE OR REPLACE PROCEDURE MODIFY_CONSTRAINTS( s_status IN USER_CONSTRAINTS.STATUS%TYPE )
IS
    TYPE lst_constraints IS TABLE OF USER_CONSTRAINTS%ROWTYPE;
    const_list  lst_constraints;    
BEGIN
    
    SELECT  *
    BULK    COLLECT INTO const_list
    FROM    USER_CONSTRAINTS 
    WHERE   STATUS <> s_status
    AND     CONSTRAINT_TYPE IN ( 'R', 'P' )
    AND     TABLE_NAME NOT IN ( 
                SELECT  TABLE_NAME 
                FROM    USER_TABLES
                WHERE   IOT_TYPE = 'IOT'
            )
    ORDER   BY DECODE( CONSTRAINT_TYPE, 'R', 0, 'P', 1 );

    IF s_status = 'DISABLE' THEN
        FOR i IN const_list.FIRST .. const_list.LAST LOOP
        
            EXECUTE IMMEDIATE 'ALTER TABLE ' || 
                                const_list(i).TABLE_NAME  || 
                                ' MODIFY CONSTRAINT ' ||
                                const_list(i).CONSTRAINT_NAME  || 
                                ' ' || s_status;
        
        END LOOP;
    ELSE
        FOR i IN REVERSE const_list.FIRST .. const_list.LAST LOOP 
        
            EXECUTE IMMEDIATE 'ALTER TABLE ' || 
                                const_list(i).TABLE_NAME  || 
                                ' MODIFY CONSTRAINT ' ||
                                const_list(i).CONSTRAINT_NAME  || 
                                ' ' || s_status;
        
        END LOOP;
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        RAISE;
END;
/

Wenn du jetzt 'ENABLE' oder 'DISABLE' übergibst, aktiviert oder deaktiviert die Prozedur alle
Constraints (PK & FK). Wichtig ist, dass man nicht versucht die PK Constraints von IOT Tabellen
zu deaktivieren, daher die Einscrhänkung

    AND     TABLE_NAME NOT IN ( 
                SELECT  TABLE_NAME 
                FROM    USER_TABLES
                WHERE   IOT_TYPE = 'IOT'
            )

Werden die Constraints wieder aktiviert muss die Reihenfolge natürlich genau umgekehrt wie beim
Deaktivieren sein, daher die Schleife mit REVERSE. Ist noch nicht ganz so schön die Prozedur, tut
aber ihren Zweck.

 

[tplanitz]

Hallo,

also echt super was man hier so alles lernt. Das DISABLEN hat geklappt. Ich probiere gerade das ENABLEN und dabei fällt mir auf das das verdammt viel länger dauert als das DISABLEN. Kannst Du mir verraten warum das so ist?

Und als zweites noch die Frage zum Codestück:

BULK  COLLECT INTO const_list

, was bewirkt das ? Ich habs nachgelesen aber werde nicht ganz schlau draus. Im groben kann man Paketweise Daten aus einer Datenbank holen, geht das auch schreibend ?

Ich habe öfter mit großen Datenbankabgleichen zu tun (50 - 100 Millionen Datensätze) die transformiert/gecheckt werden müssen. Da hört sich Paketweise schreiben besser an als Zeilenweise.

Vielen dank erstmal für die super Procedur !!

Gruß aus Berlin

 

[Exceptionfault]

Ich probiere gerade das ENABLEN und dabei fällt mir auf das das verdammt
viel länger dauert als das DISABLEN. Kannst Du mir verraten warum das so ist?

Klar, ist doch eigentlich logisch. Beim DISABLEN deaktiviert Oracle die Constraints einfach nur.
Technisch gesehen wird nichts anderes gemacht als im Dictionary die Spalte STATUS mit "DISABLED"
upgedated, fertig. Wenn ich dagegen einen UNIQUE (od. Primary) Key ENABLE wird ja nicht nur der
Status upgedated sondern Oracle muss auch sicherstellen, dass deine Spalte auf welcher der
Constraint liegt wirklich Unique ist. Ist dies nicht der Fall könntest du den Constraint
gar nicht aktivieren. Bei Foreign Keys muss geprüft werden ob auch alle Werte in der Parent
Tabelle existieren, also ob die Integrität noch gewahrt ist. Die längere Dauer ist also durchaus
begründet.

Zur 2. Frage:
Oracle arbeitet bei PL/SQL mit 2 "Engines". Das eine ist die "normale" SQL Engine. Die wird
immer benutzt, wenn ich ein SQL Statement absetze, inserte, update, etc... Darüber liegt die
PL/SQL Engine also die Ausführungsschicht für Prozeduren, Functions und Trigger. Wenn ich aus
einer Prozedur SQL ausführe findet ein sog. Context Switch statt, in dem Oracle zwischen den beiden
Engines wechseln muss und z.B. Bind Variablen übergibt.

Früher hat man z.B. in PL/SQL einen Cursor geöffnet und zeilenweise in PL/SQL eingelesen. Das führt
dazu, dass Oracle für jede Zeile von PL/SQL in SQL und zurück gewechselt hat. Ein BULK Collect
verhindert dies indem er genau ein Context Switch ausführt und dabei das komplette Ergebnis in
die PL/SQL Engine überträgt. Großer Vorteil: GESCHWINDIGKEIT. Nachteil: Größerer Speicherbedarf
je nach Größe der Ergebnismenge.

Paketweise schreiben geht natürlich nur dann wenn dir auch die Daten schon in einem Block vorliegen.
Wenn ja empfiehlt sich das MERGE Statement.