Als PDF Downloaden!

Tipps & Tricks: Juni 2013
Bereich:
SQL, DBA
Erstellung:
06/2013 MP
Versionsinfo:
11.2
Letzte Überarbeitung:
06/2013 MP
Abhängige Objekte einer Tabelle anzeigen bzw. neu erstellen
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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 1 von 5
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);
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 2 von 5
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
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 3 von 5
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;
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 4 von 5
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 Reorgund Wartungskurs sowie im Standard Edition Kurs kennen. Wir freuen uns auf Ihr Kommen!! Wenn Sie nicht zu
kommen können, unser Consulting-Team kommt auch gerne zu Ihnen.
Muniqsoft GmbH
Schulungszentrum, Grünwalder Weg 13a, 82008 Unterhaching, Tel. 089 / 679090-40
IT-Consulting & Support, Witneystraße 1, 82008 Unterhaching, Tel. 089 / 6228 6789-0
Seite 5 von 5