Skip to Main Content

 

Auswahl  

Komplett Übersicht aller Oracle Tipps

Truncate / Drop Schutz für wichtige Objekte 

Oracle
DBA
25.06.18 (MP)
05.08.23(MP)
DBA, PL/SQL, Trigger

Passende Schulungen zum Thema

In diesem  widmen wir uns dem Thema Schutz vor dem unbeabsichtigten Löschen von wichtigen Objekten.
Fast jeder hat schon einmal aus Versehen das falsche Objekt gelöscht. Wenn es sich dabei um ein wichtige Applikationstabelle handelt, ist der Reparaturaufwand oft sehr erheblich.

Deswegen erstellen wir uns eine Liste mit schützenswerten Objekten in einer eigenen Tabelle:

connect system/sys
CREATE TABLE prio_objects (
owner          VARCHAR2(30),
object_type    VARCHAR2(30),
object_name    VARCHAR2(30));

Dann erzeugen wir eine Applikationstabelle die gegen DROP und TRUNCATE geschützt werden soll:

CREATE TABLE scott.gehalts_tab as select * from dual;

Diese Tabelle wird in die Liste der geschützten Objekte aufgenommen:

INSERT INTO prio_objects (owner,object_type,object_name)
VALUES('SCOTT','TABLE','GEHALTS_TAB');
COMMIT;

Ein DDL Trigger kümmert sich um die Prüfung, welche DROP/TRUNCATE abggebrochen werden:

CREATE OR REPLACE TRIGGER system.check_drop
BEFORE DROP OR TRUNCATE ON DATABASE 
BEGIN
FOR rec in (SELECT * FROM system.prio_objects) LOOP
    IF  sys.dictionary_obj_type=rec.object_type AND
    sys.dictionary_obj_name=rec.object_name THEN
     IF sys.dictionary_obj_owner=rec.owner  
        OR sys.dictionary_obj_type ='TABLESPACE' THEN
        RAISE_APPLICATION_ERROR(-20002,'Objekt steht auf Prio-Liste kann nicht gelöscht werden');
      END IF;
    END IF;
END LOOP;
END;
/

Wir testen den DROP und siehe da...

DROP TABLE scott.gehalts_tab;
DROP TABLE scott.gehalts_tab
*
FEHLER in Zeile 1:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-20002: Objekt steht auf Prio-Liste kann nicht gelöscht werden
ORA-06512: in Zeile 7

Fall 2: Oracle Tablespace gegen Löschen schützen:

INSERT INTO system.prio_objects (owner,object_type,object_name)
VALUES ('SYS','TABLESPACE','CRASH_TBS');
COMMIT;

DROP TABLESPACE crash_tbs;
DROP TABLESPACE crash_tbs
*
FEHLER in Zeile 1:
ORA-00604: Fehler auf rekursiver SQL-Ebene 1
ORA-20002: Objekt steht auf Prio-Liste kann nicht gelöscht werden
ORA-06512: in Zeile 6

Wenn man ein Objekt nun doch dropen oder truncaten möchte, nimmt man es einfach von der Liste:

DELETE FROM system.prio_objects 
WHERE object_type='TABLESPACE' 
AND object_name='CRASH_TBS';
COMMIT;

DROP TABLESPACE crash_tbs;

Tablespace wurde gelöscht.

DELETE FROM system.prio_objects 
WHERE object_type='TABLE' 
AND object_name='GEHALTS_TAB';
COMMIT;

DROP TABLE scott.gehalts_tab;

Sie sehen, auch ein DBA braucht hin und wieder mal PL/SQL-Kenntnisse, die er sich gerne in einem unserer fünf verschiedenen PL/SQL-Kurse aneignen kann. :-)