Tuesday, March 2, 2010

Find a schema's dependencies


Create the following, under SYSTEM or any other schema you may use for logging purposes:
CREATE TABLE HELPDESK.DEPSCHEMA
(
   OBJECT_ID              NUMBER,
   REFERENCED_OBJECT_ID   NUMBER,
   NEST_LEVEL             NUMBER,
   SEQ#                   NUMBER
)
TABLESPACE HELPDESK
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;

GRANT ALL ON HELPDESK.DEPSCHEMA TO SYSTEM;

CREATE SEQUENCE HELPDESK.DEPSCHEMASEQ
   START WITH 0
   INCREMENT BY 1
   MINVALUE 0
   MAXVALUE 100000000
   NOCACHE
   CYCLE
   NOORDER;

GRANT ALL ON HELPDESK.DEPSCHEMASEQ TO SYSTEM;

CREATE OR REPLACE FORCE VIEW HELPDESK.DEPSCHEMA_V
(
   SEQ#,
   NEST_LEVEL,
   SCHEMA,
   OBJECT_TYPE,
   OBJECT_NAME,
   STATUS;
)
AS
     SELECT D.SEQ#,
            D.NEST_LEVEL,
            O.OWNER,
            O.OBJECT_TYPE,
            O.OBJECT_NAME,
            O.STATUS
       FROM HELPDESK.DEPSCHEMA D, ALL_OBJECTS O
      WHERE D.OBJECT_ID = O.OBJECT_ID(+)
   ORDER BY SEQ#;

CREATE OR REPLACE PROCEDURE SYSTEM.DEPSCHEMA_FILL (SCHEMA CHAR)
IS
   TYPE T_CURSOR IS REF CURSOR;
   V_CURSOR   T_CURSOR;
   OBJ_ID     NUMBER;

BEGIN
   DELETE FROM HELPDESK.DEPSCHEMA;
   COMMIT;

   OPEN V_CURSOR FOR
      SELECT OBJECT_ID
        FROM ALL_OBJECTS
       WHERE OWNER = UPPER (DEPSCHEMA_FILL.SCHEMA) AND OBJECT_TYPE != 'INDEX';

   LOOP
      FETCH V_CURSOR INTO OBJ_ID;
      EXIT WHEN V_CURSOR%NOTFOUND;

      INSERT INTO HELPDESK.DEPSCHEMA
           VALUES (OBJ_ID,
                   0,
                   0,
                   HELPDESK.DEPSCHEMASEQ.NEXTVAL);

      INSERT INTO HELPDESK.DEPSCHEMA
             SELECT OBJECT_ID,
                    REFERENCED_OBJECT_ID,
                    LEVEL,
                    HELPDESK.DEPSCHEMASEQ.NEXTVAL
               FROM PUBLIC_DEPENDENCY A
         CONNECT BY PRIOR A.OBJECT_ID = A.REFERENCED_OBJECT_ID
         START WITH REFERENCED_OBJECT_ID = DEPSCHEMA_FILL.OBJ_ID;
   END LOOP;
   COMMIT;
END;
/

Run the procedure:
SQL> EXEC SYSTEM.DEPSCHEMA_FILL('xxe');
PL/SQL procedure successfully completed.

And finally querying the HELPDESK.DEPSCHEMA_V, you will get:
SEQ#NEST_LEVELOWNEROBJECT_TYPEOBJECT_NAMESTATUS
1320XXETABLEXXLL_ERROR_DEBUG_LOGVALID
1331APPSSYNONYMXXLL_ERROR_DEBUG_LOGVALID
1342APPSPACKAGE BODYXXLL_UTILSVALID
1350XXETRIGGERXXLL_PARAGRAPH_ID_TVALID
1360XXETABLEXXLL_ADJUSTMENT_TYPE_CONFVALID
1370XXETABLEXXE_IPTV_SYMPTOMSVALID
1381APPSSYNONYMXXE_IPTV_SYMPTOMSVALID
1391APPSPACKAGE BODYXXNTT_SERVICEREQUEST_PVTVALID
1401APPSVIEWXXSLAM_TICKETS_VVALID
1410XXETABLEXXE_CS_CALC_INACTIVEVALID
1421APPSPACKAGE BODYXXE_ISUPPORT_EXTRA_DATA VALID

So, for instance, table XXE.XXLL_ERROR_DEBUG_LOG is used by synonym APPS.XXLL_ERROR_DEBUG_LOG, which is used by package body APPS.XXLL_UTILS.

Here, I describe a method to find common dependencies between objects in the same schema or not.