An einer Tabelle hängen viele schöne Objekte, die man aber auf den ersten Blick gar nicht sieht.
So gibt es zum Beispiel:
- Indizes
- Constraints
- Kommentare
- Synonyme
- Sequenzen
- Trigger
- Rechte
- Audits
- Statistiken
- Materialized Views
- Annotations (ab 23c)
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;
Wenn Sie schon die Oracle Version 23c besitzen können Sie auch noch Annotations einer Tabelle anzeigen. Dann entfernen Sie bitte im obigen Select das letzte “;” und hängen folgenden Teil dran:
UNION ALL
SELECT 'Annotations',a.column_name, a.annotation_name, a.annotation_value
FROM all_annotations_usage a,t
WHERE a.owner=t.tab_owner
AND a.object_name = t.tab_name
ORDER BY column_name NULLS FIRST,domain_name NULLS FIRST,annotation_name;
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 z.B. 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!!