Abhängige Objekte einer Tabelle anzeigen bzw. neu erstellen

05.
Juni
2013
Veröffentlicht von: Marco Patzwahl

An einer Tabelle hängen viele schöne Objekte, die man aber auf den ersten Blick gar nicht sieht.

An einer Tabelle hängen viele schöne Objekte, die man aber auf den ersten Blick gar nicht sieht.
So gibt es z. B.:

  • Indizes
  • Constraints
  • Kommentare
  • Synonyme
  • Sequenzen
  • Trigger
  • Rechte
  • Audits
  • Statistiken
  • Materialized Views

Sequenzen stehen eigentlich nicht mit einer Tabelle in direktem Zusammenhang. Wenn sie aber in einem Trigger referenziert werden, der auf der Tabelle basiert, kann man (meistens) davon ausgehen, dass sie zum Füllen der Primärschlüsselspalte verwendet werden. Sequenzen, die durch die Applikation direkt aufgerufen werden, kann man leider keiner Tabelle zuordnen.

Der folgende Select zeigt Ihnen eine Zusammenfassung aller Objekte, die mit einer gegebenen Tabelle in Verbindung stehen.
In der ersten Zeile (WITH t ...) gibt man einfach den Benutzernamen und den Tabellennamen ein.
Hier wurde die Tabelle emp des Benutzers Scott verwendet:

 WITH t AS (SELECT UPPER('&username') as tab_owner,
                   UPPER('&tabname') as tab_name
            FROM dual)
  SELECT 'Index:' as Object, index_name AS Name, index_type as Typ, status
  FROM all_indexes, t
  WHERE owner = t.tab_owner
  AND table_name = t.tab_name
UNION ALL
  SELECT 'Constraints:' , constraint_name, constraint_type, null
  FROM all_constraints, t
  WHERE owner = t.tab_owner
  AND table_name = t.tab_name
UNION ALL
  SELECT 'Trigger:' , trigger_name, trigger_type, status
  FROM all_triggers, t
  WHERE owner = t.tab_owner
  AND table_name = t.tab_name
UNION ALL
  SELECT 'Privilege:', 'Von:'||grantor||' An:'||grantee, privilege, null
  FROM all_tab_privs, t
  WHERE grantor = t.tab_owner
  AND table_name = t.tab_name
UNION ALL
  SELECT 'Roles:', 'Spalte:'||column_name, privilege , null
  FROM role_tab_privs, t
  WHERE owner = t.tab_owner
  AND table_name = t.tab_name
UNION ALL
  SELECT 'Synonyme:', 'Owner:'||owner, synonym_name , null
  FROM all_synonyms, t
  WHERE owner = t.tab_owner
  AND table_name = t.tab_name
UNION ALL
  SELECT 'Mat. Views:', mv.mview_name, mv.refresh_mode||':'||
           mv.build_mode, mv.compile_state
  FROM all_mview_detail_relations amd, all_mviews mv, t
  WHERE amd.owner = mv.owner
  AND amd.mview_name = mv.mview_name
  AND amd.detailobj_owner = t.tab_owner
  AND amd.detailobj_alias = t.tab_name
UNION ALL -- Sequences (die vom Trigger der Tabelle referenziert werden)
  SELECT 'Sequence :', referenced_name , 'Ref by:'||tr.trigger_name, null
  FROM all_dependencies d, all_triggers tr, t
  WHERE d.owner = tr.owner
  AND d.name = tr.trigger_name
  AND type = 'TRIGGER'
  AND referenced_type = 'SEQUENCE'
  AND tr.table_name = t.tab_name
  AND tr.table_owner = t.tab_owner;

Geben Sie einen Wert für username ein: scott
Geben Sie einen Wert für tabname ein: emp

OBJECT       NAME                 TYP                  STATUS
------------ -------------------- -------------------- ----------
Index:       PK_EMP               NORMAL               VALID
Constraints: FK_DEPTNO            R
Constraints: PK_EMP               P
Trigger:     EMP_PK_TRIG          BEFORE EACH ROW      ENABLED
Privilege:   Von:SCOTT An:HR      DELETE
Privilege:   Von:SCOTT An:HR      UPDATE
Sequence :   EMP_SEQ              Ref by:EMP_PK_TRIG

Und weil wir gerade warm gelaufen sind, wäre es doch praktisch, die Statements für alle Objekte zu erzeugen, nur für den Fall, dass man mal ein Objekt verliert oder neu erstellen möchte.
Auch dafür kann man einen SELECT schreiben.

Zunächst sorgen wir dafür, dass jeder der erzeugten DDL-Befehle mit einem Semikolon abgeschlossen und die Storage-Klausel nicht mit ausgegeben wird...

BEGIN   
    DBMS_METADATA.SET_TRANSFORM_PARAM(
           DBMS_METADATA.SESSION_TRANSFORM, 'SQLTERMINATOR', true);
    DBMS_METADATA.SET_TRANSFORM_PARAM(
           DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false);
END;


... und benutzen das Package DBMS_METADATA für die Erstellung der nötigen SQL-Befehle:

WITH s as (SELECT UPPER('&username') as tab_owner,
                  UPPER('&tabname') as tab_name
           FROM dual)
 SELECT -- Indizes
        DBMS_METADATA.GET_DDL(
           object_type => 'INDEX',
                schema => i.owner,
                  name => i.index_name) sql_stmts
  FROM s, all_indexes i
  WHERE owner   = s.tab_owner
  AND table_name = s.tab_name
UNION ALL -- Constraints
  SELECT  DBMS_METADATA.GET_DDL(
           object_type => (CASE WHEN a.constraint_type= 'R'
                                THEN 'REF_CONSTRAINT'
                                ELSE 'CONSTRAINT'
                           END),
                  name => a.constraint_name,
                schema => a.owner)
  FROM s, all_constraints a
  WHERE owner    = s.tab_owner
  AND table_name = s.tab_name
UNION ALL -- Trigger
  SELECT DBMS_METADATA.GET_DDL(
           object_type => 'TRIGGER',
                  name => t.trigger_name,
                schema => t.owner)
  FROM s, all_triggers t
  WHERE owner = tab_owner
  AND table_name = tab_name
UNION ALL -- Rechte
  SELECT DBMS_METADATA.GET_DEPENDENT_DDL(
                 object_type => 'OBJECT_GRANT',
            base_object_name => s.tab_name,
          base_object_schema => s.tab_owner)
  FROM s, all_tab_privs a
  WHERE a.grantor = s.tab_owner
  AND a.table_name = s.tab_name
UNION ALL -- Synonyme
  SELECT DBMS_METADATA.GET_DDL(
          object_type => 'SYNONYM',
                 name => sy.synonym_name,
               schema => sy.owner)
  FROM s, all_synonyms sy
  WHERE (owner = s.tab_owner or owner = 'PUBLIC')
  AND table_name = s.tab_name
UNION ALL -- MV
  SELECT DBMS_METADATA.GET_DDL (
          object_type => 'MATERIALIZED_VIEW',
                 name => mdr.mview_name,
               schema => mdr.owner)
  FROM s, ALL_MVIEW_DETAIL_RELATIONS mdr
  WHERE detailobj_owner = s.tab_owner
  AND detailobj_name = s.tab_name AND ROWNUM = 1
UNION ALL -- Audit
  SELECT DBMS_METADATA.GET_DEPENDENT_DDL(
              object_type => 'AUDIT_OBJ',
         base_object_name => s.tab_name,
       base_object_schema => s.tab_owner)
  FROM s, dba_obj_audit_opts oao
  WHERE oao.object_name = s.tab_name
  AND oao.owner = s.tab_owner
  AND ROWNUM = 1
UNION ALL -- Tabellenkommentare
  SELECT TO_CLOB('COMMENT ON TABLE '||table_name||
                   q'[ IS ']'||atc.comments||''';')
  FROM s, all_tab_comments atc
  WHERE owner    = tab_owner
  AND table_name = tab_name
  AND comments IS NOT NULL
UNION ALL -- Spaltenkommentare
  SELECT TO_CLOB('COMMENT ON COLUMN '||acc.table_name||'.'||acc.column_name||
            q'[ IS ']'||acc.comments||''';')
  FROM s, all_col_comments acc
  WHERE owner    = s.tab_owner
  AND table_name = s.tab_name
  AND comments IS NOT NULL
UNION ALL -- Sequenzen, die von Triggern der Tabelle referenziert werden
  SELECT DBMS_METADATA.GET_DDL(
             object_type => 'SEQUENCE',
                  schema => referenced_owner,
                    name => referenced_name)
  FROM all_dependencies d, all_triggers tr, s
  WHERE d.owner = tr.owner
  AND d.name = tr.trigger_name
  AND type = 'TRIGGER'
  AND referenced_type = 'SEQUENCE'
  AND tr.table_name = s.tab_name
  AND tr.table_owner = s.tab_owner
/

SQL_STMTS
--------------------------------------------------------------------------------
  CREATE UNIQUE INDEX "SCOTT"."PK_EMP" ON "SCOTT"."EMP" ("EMPNO")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "USERS" ;

  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
          REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE;

  ALTER TABLE "SCOTT"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  TABLESPACE "USERS"  ENABLE;
  CREATE OR REPLACE TRIGGER "SCOTT"."EMP_PK_TRIG"
BEFORE INSERT ON emp FOR EACH ROW
BEGIN
  :NEW.empno := emp_seq.NEXTVAL;
END;
/
ALTER TRIGGER "SCOTT"."EMP_PK_TRIG" ENABLE;

   CREATE SEQUENCE  "SCOTT"."EMP_SEQ"  MINVALUE 1 MAXVALUE 999999999999999999999
9999999 INCREMENT BY 10 START WITH 8200 CACHE 20 NOORDER  NOCYCLE ;

Anmerkungen:
Man kann leider nicht in jedem Fall auf die ALL_... Variante  der Data Dictionary Views zurückzugreifen, weil es keine ALL_OBJ_AUDIT_OPTS View gibt. Wenn man keine DBA Rechte hat, ersetzt man einfach den Bezug (DBA_OBJ_AUDIT_OPTS) durch USER_OBJ_AUDIT_OPTS und wirft den Filter "AND oao.owner=s.orig_tab_owner" weg.
Die Filter "AND rownum=1" sind nicht zum Spaß da. Da das Package dbms_metadata abstürzt, wenn man nicht vorhandene Audit-Informationen oder Rechte eines Objekts abfragen will, muss man erst mal prüfen, ob es da etwas gibt. Mit einer Zeile bekommt man dann aber alle Rechte/Audit-Einstellungen zurück :-)

Weitere Möglichkeiten, diese Funktionen auch in einer Online Reorg einzusetzen, lernen Sie bei uns im Reorg- und Wartungskurs sowie im Standard Edition Kurs kennen. Wir freuen uns auf Ihr Kommen!! Wenn Sie nicht zu kommen können, unser Opens window for sending emailConsulting-Team kommt auch gerne zu Ihnen.

Audit Analyse Datenbank Verwaltung

Jede Menge Know-how für Sie!

In unserer Know-How Datenbank finden Sie mehr als 300 ausführliche Beiträge zu den Oracle-Themen wie DBA, SQL, PL/SQL, APEX und vielem mehr.
Hier erhalten Sie Antworten auf Ihre Fragen.