Oracle 7.3 - Oracle 23ai 20+ years of experience Version (Released: )

Oracle schema dependencies

19 Apr 2021

Oracle schema dependencies explained

Oracle schema dependencies is the topic of our newsletter. We are defining them by the following decree:

Two database schemas depend on each other, if there is at least one database object in either database schema and another database object in the other database schema that depend on each other.

Dependency between database objects may be found in two ways:

  • There is a foreign key constraint between two tables that reside in different database schemas.
  • There is an object dependency between two database objects that reside in different database schemas.
    It can be found in Oracle’s ALL_DEPENDENCIES respectively DBA_DEPENDENCIES view.

Already one match will do to take the pair of database objects into account.

Oracle schema dependencies diagram

As you remember, maybe, the “Schema dependencies” tab on the “Schema” page already displays a diagram that visualizes super- and subordinated dependencies of the current database schema to and from other schemas. In the left of the box that represents the current schema you can see all ‘master’ schemas the current schema is having by either a foreign key reference or an object dependency to the appropriate ‘master’ schema. In the right of the current schema you can see all ‘detail’ schemas having either a foreign key reference or an object dependency to the current schema.

The following screenshot of Hora’s schema dependencies page displays Oracle schema dependencies for the predefined MDSYS user. BTW, in order to login as MDSYS for demo, we had to unlock the MDSYS account and grant the CREATE SESSION privilege.

Oracle schema dependencies screenshot

Oracle schema dependencies screenshot

Drilling-down object dependencies

In KeepTool 14.2.2, we added a data grid to the right of the diagram. If you click any schema in the diagram, the data grid lists all database objects of the selected schema having a foreign key reference or an object dependency to an object that resides in a directly neighboring database schema.

For the database object currently selected in this data grid, another data grid below shows all dependent objects in any other directly neighboring database schema. Two checkboxes indicate whether the dependeny is based on a foreign key reference, an object dependency, or both.

As of now, our Oracle database tool Hora allows you to drill-down Oracle schema dependencies to Oracle database object dependencies.


See how it works!

Explore possibilities of KeepTool.