Analyze database object dependencies

Visulate for Oracle provides a UI and APIs to identify database object dependencies.

Every Oracle database maintains a record of the dependencies between its objects in the SYS schema’s DEPENDENCY$ table. It queries this table to identify the status of an object. For example, a stored procedure is marked as invalid if you drop a table that it relies on. Visulate for Oracle provides APIs which read the same table.

Using the UI

Dependency reports are included at the bottom of each database object report. For example, to view the dependencies for a table use the navigation menu or search box to open its object report.

Table details

Scroll to the bottom of the page to see a list of object (e.g. packages, package bodies and views) that reference the table.

Dependencies

Clicking on an object name in the dependency list will take you to a report showing its definition. For example, selecting a package body will open a report that shows the source code for the package body and a list of the SQL statement it contains.

Using the object report API

The database object report that the UI displays can also be accessed via an API call. Call the /api endpoint passing the database, schema, object type and object name as path parameters /api/{database}/{schema}/{object type}/{object name} Example:

curl -X GET "https://my-domain.com/api/my-db/WIKI/PACKAGE/RNT_MENUS_PKG" \
-H  "accept: application/json" | json_pp

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  8390  100  8390    0     0  15594      0 --:--:-- --:--:-- --:--:-- 15594
[
   {
      "description" :
        ... database object report items
   }

   {
      "description" : "Dependencies other objects have on this one",
      "display" : [
         "Object Name",
         "Object Type",
         "Line"
      ],
      "link" : "Object Name",
      "rows" : [
         {
            "LINK" : "WIKI/PACKAGE BODY/RNT_MENUS_PKG",
            "Line" : "1, 4, 110",
            "OBJECT_ID" : 73477,
            "Object Name" : "RNT_MENUS_PKG",
            "Object Type" : "PACKAGE BODY",
            "Owner" : "WIKI"
         }
      ],
      "title" : "Used By"
   },
   {
      "description" : "Dependencies this object has on others",
      "display" : [
         "Object Name",
         "Object Type",
         "Line"
      ],
      "link" : "Object Name",
      "rows" : [
         {
            "LINK" : "WIKI/TABLE/RNT_MENUS",
            "Line" : "1, 4, 5, 11, 12, 13, 15, 16, 17, 18, 19, 21, 22, 23, 24, 26, 27, 29",
            "Object Name" : "RNT_MENUS",
            "Object Type" : "TABLE",
            "Owner" : "WIKI",
            "P_OBJ#" : 73457
         },
         {
            "LINK" : "WIKI/VIEW/RNT_MENUS_V",
            "Line" : "13, 19",
            "Object Name" : "RNT_MENUS_V",
            "Object Type" : "VIEW",
            "Owner" : "WIKI",
            "P_OBJ#" : 73469
         }
      ],
      "title" : "Uses"
   }
]

Using the object collection API

The object collection API is used to identify a collection of objects along with the dependent objects that are needed to create them. It is designed to support partial schema migrations where a subset of the objects in a schema are to be migrated to a new database.

The object collection API accepts one or more object patterns as input and returns a collection of matching objects and their “uses” dependencies. An object pattern is an object describing a combination of owner, type, name and status. For example, the following pattern identifies objects in the “APPS” schema with an object name that start with “AP_BANK” of any type and status

[
  {
    "owner": "APPS",
    "type": "*",
    "name": "AP_BANK*",
    "status": "*"
  }
]

The API converts “*” characters to “%” and assembles a list of matching objects by running the following query

select  object_id, object_name, object_type, owner
from dba_objects
where  owner = :owner
and object_type like :object_type
and object_name like :object_name
and status like :status
and rownum < 3000
and object_type not in ('INDEX PARTITION','INDEX SUBPARTITION', 'LOB','LOB PARTITION','TABLE PARTITION','TABLE SUBPARTITION')
order by owner, object_type, object_name

For each matching object it queries the SYS.DEPENDENCY$ table to identify its “uses” dependencies. These are objects that need to exist for a valid install of the matched object. For example, the API would return FND_CURRENCIES in the result set for the AP_BANK* pattern if it matched a package body called AP_BANK_CHARGE_PKG which included a SQL statement that selects from FND_CURRENCIES

The object collection API is accessed by calling the /api/collection/ endpoint passing the database as a path parameter /api/collection/{database} and an object pattern in the body of a POST request. Example:

curl \
-L 'https://my-domain.com/api/collection/vis115' \
-H 'Content-Type: application/json' \
-o collection_dependencies.json \
-d @- << EOF
[
  {
    "owner": "RNTMGR2",
    "type": "%",
    "name": "PR_RECORDS_PKG",
    "status": "*"
  },
  {
    "owner": "RNTMGR2",
    "type": "%",
    "name": "PR_PUMS_PKG",
    "status": "*"
  }
]
EOF

  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100 12416  100 12234  100   182  13933    207 --:--:-- --:--:-- --:--:-- 14125

$ json_pp < collection_dependencies.json
[
   {
      "OBJECT_ID" : 88898,
      "OBJECT_NAME" : "PR_RECORDS_PKG",
      "OBJECT_TYPE" : "PACKAGE",
      "OWNER" : "RNTMGR2"
   },
   {
      "OBJECT_ID" : 88717,
      "OBJECT_NAME" : "PROPERTY_LIST_T",
      "OBJECT_TYPE" : "TYPE",
      "OWNER" : "RNTMGR2",
      "REQUIRED_BY" : [
         {
            "OBJECT_ID" : 88898,
            "OBJECT_NAME" : "PR_RECORDS_PKG",
            "OBJECT_TYPE" : "PACKAGE",
            "OWNER" : "RNTMGR2"
         },
         {
            "OBJECT_ID" : 89244,
            "OBJECT_NAME" : "PR_RECORDS_PKG",
            "OBJECT_TYPE" : "PACKAGE BODY",
            "OWNER" : "RNTMGR2"
         }
      ]
   },

...

]