Databases Reference
In-Depth Information
In this example the object being analyzed is a table named
D_COMPANIES
. Several synonyms, materialized views,
and one trigger are dependent on this table. For instance, the materialized view
CB_RAD_COUNTS
, owned by
CIA
, is
dependent on the synonym
D_COMPANIES
, owned by
CIA
, which in turn is dependent on the
D_COMPANIES
synonym,
owned by
STAR1
.
The
DBA_DEPENDENCIES
view contains a hierarchical relationship between the
OWNER
,
NAME
and
TYPE
columns and
their referenced column names of
REFERENCED_OWNER
,
REFERENCED_NAME
, and
REFERENCED_TYPE
. Oracle provides
a number of constructs to perform hierarchical queries. For instance,
START WITH
and
CONNECT BY
allow you to
identify a starting point in a tree and walk either up or down the hierarchical relationship.
The previous SQL query in this section operates on only one object. If you want to inspect every object in
a schema, you can use SQL to generate SQL to create scripts that display all dependencies for a schema's objects.
The piece of code in the next example does that. For formatting and output, the code uses some constructs specific to
SQL*Plus, such as setting the page sizes and line size and spooling the output:
UNDEFINE owner
SET LINESIZE 132 PAGESIZE 0 VERIFY OFF FEEDBACK OFF TIMING OFF
SPO dep_dyn_&&owner..sql
SELECT 'SPO dep_dyn_&&owner..txt' FROM DUAL;
--
SELECT
'PROMPT ' || '_____________________________'|| CHR(10) ||
'PROMPT ' || object_type || ': ' || object_name || CHR(10) ||
'SELECT ' || '''' || '+' || '''' || ' ' || '|| LPAD(' || '''' || ' '
|| '''' || ',level+3)' || CHR(10) || ' || type || ' || '''' || ' ' || '''' ||
' || owner || ' || '''' || '.' || '''' || ' || name' || CHR(10) ||
' FROM dba_dependencies ' || CHR(10) ||
' CONNECT BY PRIOR owner = referenced_owner AND prior name = referenced_name '
|| CHR(10) ||
' AND prior type = referenced_type ' || CHR(10) ||
' START WITH referenced_owner = ' || '''' || UPPER('&&owner') || '''' || CHR(10) ||
' AND referenced_name = ' || '''' || object_name || '''' || CHR(10) ||
' AND owner IS NOT NULL;'
FROM dba_objects
WHERE owner = UPPER('&&owner')
AND object_type NOT IN ('INDEX','INDEX PARTITION','TABLE PARTITION');
--
SELECT 'SPO OFF' FROM dual;
SPO OFF
SET VERIFY ON LINESIZE 80 FEEDBACK ON
You should now have a script named
dep_dyn_<owner>.sql
, created in the same directory from which you run
the script. This script contains all the SQL required to display dependencies on objects in the owner you entered. Run
the script to display object dependencies. In this example, the owner is
CIA
:
SQL>@dep_dyn_cia.sql
When the script runs, it spools a file with the format
dep_dyn_<owner>.txt
. You can open that text file with an
OS editor to view its contents. Here is a sample of the output from this example:
TABLE: DOMAIN_NAMES
+ FUNCTION STAR2.GET_DERIVED_COMPANY
+ TRIGGER STAR2.DOMAIN_NAMES_BU_TR1
+ SYNONYM CIA_APP.DOMAIN_NAMES
