This is an aftermath of a DDL statement executed on an object, that is used, directly or indirectly, by all invalid objects.
Also, this object will, probably, not be invalid, when you discover this situation.
The following query will find an object's dependencies:
SELECT REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE
FROM DBA_DEPENDENCIES
START WITH OWNER = [OBJECT'S OWNER]
AND NAME = [OBJECT'S NAME]
AND TYPE = [OBJECT'S TYPE]
CONNECT BY OWNER = PRIOR REFERENCED_OWNER
AND NAME = PRIOR REFERENCED_NAME
AND TYPE = PRIOR REFERENCED_TYPE;If you want to find N objects' common dependencies, you should intersect the results of the above query for all objects:
SELECT REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE
FROM DBA_DEPENDENCIES
START WITH OWNER = [OBJECT_1'S OWNER]
AND NAME = [OBJECT_1'S NAME]
AND TYPE = [OBJECT_1'S TYPE]
CONNECT BY OWNER = PRIOR REFERENCED_OWNER
AND NAME = PRIOR REFERENCED_NAME
AND TYPE = PRIOR REFERENCED_TYPE
INTERSECT
SELECT REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE
FROM DBA_DEPENDENCIES
START WITH OWNER = [OBJECT_2'S OWNER]
AND NAME = [OBJECT_2'S NAME]
AND TYPE = [OBJECT_2'S TYPE]
CONNECT BY OWNER = PRIOR REFERENCED_OWNER
AND NAME = PRIOR REFERENCED_NAME
AND TYPE = PRIOR REFERENCED_TYPE
INTERSECT
...
...
...
INTERSECT
SELECT REFERENCED_OWNER, REFERENCED_NAME, REFERENCED_TYPE
FROM DBA_DEPENDENCIES
START WITH OWNER = [OBJECT_N'S OWNER]
AND NAME = [OBJECT_N'S NAME]
AND TYPE = [OBJECT_N'S TYPE]
CONNECT BY OWNER = PRIOR REFERENCED_OWNER
AND NAME = PRIOR REFERENCED_NAME
AND TYPE = PRIOR REFERENCED_TYPE;Now, we should create a procedure to get this result easier.
Fist, we will need 3 tables.
In COMP_OBJS table, you will need to insert all objects' names, types and owners, of which you want to find the common dependencies.
SQL> DESC HELPDESK.COMP_OBJS
Name Null? Type
-------------------- -------- -------------
OBJECT_NAME VARCHAR2(128)
OBJECT_TYPE VARCHAR2(30)
OWNER VARCHAR2(30)In COMP_DEP1 table, the 1st object's dependencies will be stored and in this you will find the common dependencies, after you run the procedure.
SQL> DESC HELPDESK.COMP_DEP1
Name Null? Type
-------------------- -------- -------------
OBJECT_NAME VARCHAR2(128)
OBJECT_TYPE VARCHAR2(30)
OWNER VARCHAR2(30)In COMP_DEP2 table, we will store, one by one, the 2nd to Nth objects' dependencies.
SQL> DESC HELPDESK.COMP_DEP2
Name Null? Type
-------------------- -------- -------------
OBJECT_NAME VARCHAR2(128)
OBJECT_TYPE VARCHAR2(30)
OWNER VARCHAR2(30)After you have created them, grant privileges to SYSTEM.
SQL> GRANT ALL ON HELPDESK.COMP_OBJS TO SYSTEM;
Grant succeeded.
SQL> GRANT ALL ON HELPDESK.COMP_DEP1 TO SYSTEM;
Grant succeeded.
SQL> GRANT ALL ON HELPDESK.COMP_DEP2 TO SYSTEM;
Grant succeeded.Procedure COMP_DEPS will check if each COMP_DEP1's row exists in COMP_DEP2.
If not, then it will delete this row from COMP_DEP1.
In the end, COMP_DEP1's contents will be the common dependencies of our N objects.
The procedure is:
CREATE OR REPLACE PROCEDURE SYSTEM.COMP_DEPS
IS
TYPE T_CURSOR IS REF CURSOR;
V_CURSOR1 T_CURSOR;
V_CURSOR2 T_CURSOR;
V_NAME1 VARCHAR2 (128);
V_TYPE1 VARCHAR2 (30);
V_OWNER1 VARCHAR2 (30);
V_NAME2 VARCHAR2 (128);
V_TYPE2 VARCHAR2 (30);
V_OWNER2 VARCHAR2 (30);
V_COUNT1 INTEGER;
V_COUNT2 INTEGER := 0;
BEGIN
EXECUTE IMMEDIATE 'TRUNCATE TABLE HELPDESK.COMP_DEP1';
EXECUTE IMMEDIATE 'TRUNCATE TABLE HELPDESK.COMP_DEP2';
OPEN V_CURSOR1 FOR
SELECT OBJECT_NAME, OBJECT_TYPE, OWNER FROM HELPDESK.COMP_OBJS;
LOOP
FETCH V_CURSOR1
INTO V_NAME1, V_TYPE1, V_OWNER1;
EXIT WHEN V_CURSOR1%NOTFOUND;
IF V_COUNT2 = 0
THEN
INSERT INTO HELPDESK.COMP_DEP1 (OBJECT_NAME, OBJECT_TYPE, OWNER)
( SELECT REFERENCED_NAME, REFERENCED_TYPE, REFERENCED_OWNER
FROM DBA_DEPENDENCIES
START WITH OWNER = V_OWNER1
AND NAME = V_NAME1
AND TYPE = V_TYPE1
CONNECT BY OWNER = PRIOR REFERENCED_OWNER
AND NAME = PRIOR REFERENCED_NAME
AND TYPE = PRIOR REFERENCED_TYPE);
COMMIT;
V_COUNT2 := 1;
ELSE
INSERT INTO HELPDESK.COMP_DEP2 (OBJECT_NAME, OBJECT_TYPE, OWNER)
( SELECT REFERENCED_NAME, REFERENCED_TYPE, REFERENCED_OWNER
FROM DBA_DEPENDENCIES
START WITH OWNER = V_OWNER1
AND NAME = V_NAME1
AND TYPE = V_TYPE1
CONNECT BY OWNER = PRIOR REFERENCED_OWNER
AND NAME = PRIOR REFERENCED_NAME
AND TYPE = PRIOR REFERENCED_TYPE);
COMMIT;
OPEN V_CURSOR2 FOR
SELECT OBJECT_NAME, OBJECT_TYPE, OWNER FROM HELPDESK.COMP_DEP1;
LOOP
FETCH V_CURSOR2
INTO V_NAME2, V_TYPE2, V_OWNER2;
EXIT WHEN V_CURSOR2%NOTFOUND;
SELECT COUNT (*)
INTO V_COUNT1
FROM HELPDESK.COMP_DEP2
WHERE OBJECT_NAME = V_NAME2
AND OBJECT_TYPE = V_TYPE2
AND OWNER = V_OWNER2;
IF (V_COUNT1 = 0)
THEN
DELETE FROM HELPDESK.COMP_DEP1
WHERE OBJECT_NAME = V_NAME2
AND OBJECT_TYPE = V_TYPE2
AND OWNER = V_OWNER2;
COMMIT;
END IF;
END LOOP;
EXECUTE IMMEDIATE 'TRUNCATE TABLE HELPDESK.COMP_DEP2';
CLOSE V_CURSOR2;
END IF;
END LOOP;
CLOSE V_CURSOR1;
END COMP_DEPS;
/Let's do a test.
Initially, we insert into COMP_OBJS table the objects we want to compare:
SQL> SELECT * FROM HELPDESK.COMP_OBJS;| OBJECT_NAME | OBJECT_TYPE | OWNER |
| XXACC_TEST_LOCKBOX | PROCEDURE | XXACC |
| AR_BOE_REMIT_RECEIPTS_V | VIEW | APPS |
| DUN | PACKAGE BODY | DUNNER |
| XXCRM_PLIROMES | VIEW | XXE |
Let's assume these 4 objects were invalidated simultaneously and we want to find their common dependencies, to see if we can discover on which object a DDL was executed and caused the invalidations.
We execute COMP_DEPS:
SQL> EXEC SYSTEM.COMP_DEPS;
PL/SQL procedure successfully completed.Let's see the result:
SQL> SELECT * FROM HELPDESK.COMP_DEP1;| OBJECT_NAME | OBJECT_TYPE | OWNER |
| AR_CASH_RECEIPTS_ALL | TABLE | AR |
So, all 4 initial objects share only one dependency, table AR.AR_CASH_RECEIPTS_ALL.
An ALTER statement on this table invalidated all 4 of them.
Now, you can check your audit mechanisms to find out who executed the ALTER.
No comments:
Post a Comment