-- Comments AND Script Documentation
-- Script Name   : extract_schema_dependencies.sql
-- Purpose       : Extract schema dependencies to verify successful run of ddl scripts
-- Requirements  : User calling script must be logged in with DBA privs
--                 The directory the script is being called FROM must be writeable by Oracle
-- Parameters    : &1 - schema_owner - Username of the schema owner
-- Author        : Daniel W. Fink
-- Created Date  : July 12, 2004
-- Updates
--               : 07/12/2004 dwf Original version
-- Comments      : This can be called standalone or as part of extract_schema_menu.sql
--                 Compatible with Oracle9i/10g.


DEFINE schema_owner = &1
WHENEVER SQLERROR EXIT FAILURE

SET LINESIZE 132 PAGESIZE 0 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON LONG 1000000

COLUMN rundate FORMAT a8 NEW_VALUE ddl_date NOPRINT
COLUMN dbname FORMAT a10 NEW_VALUE db_name NOPRINT
COLUMN schemaid NEW_VALUE schema_id NOPRINT
COLUMN sort_order NOPRINT
COLUMN row_order NOPRINT
COLUMN dependency_string FORMAT a495

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD') rundate FROM dual
/

SELECT UPPER(SYS_CONTEXT('USERENV', 'DB_NAME')) dbname FROM dual
/

SELECT u.user# schemaid
FROM sys.user$ u
WHERE u.name = UPPER('&&schema_owner');

-- SPOOL schema_&&db_name\_&&schema_owner\_&&ddl_date\_dependencies.lst
SPOOL schema_dependencies.lst

SELECT 0 sort_order,
       ROWNUM row_order,
       'Username ==> '||u.name||CHR(10)||
       RPAD('     Default Tablespace',30)||'==> '||(SELECT dt.name FROM sys.ts$ dt WHERE dt.ts# = u.datats#)||CHR(10)||
       RPAD('     Temporary Tablespace',30)||'==> '||(SELECT tt.name FROM sys.ts$ tt WHERE tt.ts# = u.tempts#)||CHR(10)||
       RPAD('     Resource Profile',30)||'==> '||(SELECT p.name FROM sys.profname$ p WHERE p.profile# = u.resource$) dependency_string
FROM sys.user$ u
WHERE u.user# = &&schema_id
UNION
SELECT 1 sort_order,
       ROWNUM row_order,
       CASE WHEN ROWNUM = 1 THEN RPAD('     Roles',30)||'==> '||u.name 
            ELSE lpad('==> ',34)||u.name 
       END dependency_string
FROM sys.sysauth$ s, 
     sys.user$ u
WHERE s.privilege# = u.user#
  AND s.grantee# = &&schema_id
UNION
SELECT 2 sort_order,
       ROWNUM row_order,
       CASE WHEN ROWNUM = 1 THEN RPAD('     Tablespaces',30)||'==> '||t.name
            ELSE lpad('==> ',34)||t.name
       END dependency_string
FROM (SELECT ts.name
      FROM sys.obj$ o,
           sys.ts$ ts,
           sys.sys_objects so
      WHERE ts.ts# = so.ts_number
        AND o.obj# = so.object_id
        AND o.owner# = &&schema_id
      UNION
      SELECT ts.name
      FROM sys.ts$ ts,
           sys.tsq$ tq
      WHERE tq.user# = &&schema_id
        AND ts.ts# = tq.ts#) t
WHERE t.name NOT IN (SELECT dt.name FROM sys.ts$ dt, user$ u WHERE dt.ts# = u.datats# AND u.user# = &&schema_id)
UNION ALL
SELECT 3 sort_order,
       ROWNUM row_order,
       CASE WHEN ROWNUM = 1 THEN RPAD('     Objects',30)||'==> '||uo.ownername||'.'||uo.objectname
            ELSE lpad('==> ',34)||uo.ownername||'.'||uo.objectname 
       END||' ('||DECODE(uo.objecttype, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER', 4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE', 7, 'PROCEDURE',
                      8, 'FUNCTION', 9, 'PACKAGE', 10, 'NON-EXISTENT', 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY',
                      28, 'JAVA SOURCE', 29, 'JAVA CLASS', 42, 'MATERIALIZED VIEW', 43, 'DIMENSION', 56, 'JAVA DATA', 'UNDEFINED')||')'
      dependency_string
FROM (SELECT DISTINCT u.name ownername, o.name objectname, o.type# objecttype
      FROM sys.user$ u,
           sys.obj$ o,
           sys.objauth$ oa
      WHERE oa.grantee# = &&schema_id
        AND oa.grantor# = u.user#
        AND oa.obj# = o.obj#
      UNION
      SELECT DISTINCT pu.name ownername, po.name objectname, po.type# objecttype
      FROM sys.obj$ o,
           sys.disk_and_fixed_objects po,
           sys.dependency$ d,
           sys.user$ pu
       WHERE o.obj# = d.d_obj#
         AND o.owner# = &&schema_id
         AND po.obj# = d.p_obj#
         AND po.owner# = pu.user#
         AND pu.user# != &&schema_id
         AND pu.name not in ('SYS', 'SYSTEM', 'PUBLIC', 'OUTLN', 'DBSNMP')) uo
UNION ALL
SELECT 4 sort_order,
       ROWNUM row_order,
       CASE WHEN ROWNUM = 1 THEN RPAD('     Synonyms',30)||'==> '||RPAD(s.name,30)||' for '||s.owner||'.'||o.name
            ELSE lpad('==> ',34)||RPAD(s.name,30)||' for '||s.owner||'.'||o.name 
       END dependency_string
FROM sys.syn$ s, 
     sys.user$ u, 
     sys.obj$ o
WHERE s.obj# = o.obj#
  AND o.owner# = u.user#
  AND u.name = '&&schema_owner'
ORDER BY sort_order, row_order
/

SPOOL OFF

-- EXIT