Analyze Amazon RDS for Oracle database object dependencies

Without the right tools, it is difficult to understand database object interdependencies in large, monolithic Oracle databases with thousands of database objects. When undergoing a modernization project for monolithic databases, for example, you need a data-driven analysis of the existing database objects and their interdependencies to understand the order in which to modernize database objects.

In this post, we show you an analysis tool which serves as a starting point to your database analysis journey by highlighting specific interdependencies between database objects: object dependencies, object constraints, and trigger references. Through a combination of SQL queries on the source Oracle database dictionary and Excel filters, the solution in this post can capture interdependent database objects for a target schema and generate a visual dependency diagram.

This analysis can be used to help identify groupings of database objects which rely on one another. For a modernization project specifically, this analysis illustrates which objects in your database are candidates for initial modernization based on its usages and dependencies. Further investigation on your application logic and database objects without proper schema references would be needed after this analysis is complete and is outside the scope of this tool.

Solution overview

The following figure shows an example of the dependency graphs that the database object dependency analyzer tool can generate.

The tool presented in this solution doesn’t use AWS services. However, the data provided to the tool can be retrieved from an Amazon Relational Database Service (Amazon RDS) for Oracle instance.

The steps to use the tool are:

1 – Retrieve database objects metadata

• Retrieve database object dependencies
• Retrieve parent-child relationships
• Retrieve triggers, tables, and views metadata

2 – Configure and run analysis tool

• Dependency installation
• Tool configuration options
• Tool execution

3 — Review results

• Review visualization graphs
• Review command line output

Prerequisites

To use the solution in this post, you must have the following prerequisites:

• NodeJS (v18 or later is preferred)
• Docker (v25 or later – v25 was used for testing)
• The GitHub repo cloned to your local machine
• Microsoft Excel (or similar) to open and analyze a CSV file
• SQL Developer (or similar) with connectivity to the Oracle database you want to analyze
• Oracle database v10.1 or newer with appropriate user privileges

Gather database details

A system schema is the set of metadata (data dictionary) used by the Oracle database, typically generated using data definition language (DDL) statements. A schema defines attributes of the database, such as tables, columns, views, and other database objects and their interdependencies. A database schema is a description of the data in an Oracle database. You need to collect the metadata about the database schema by querying the Oracle system schema.

Every schema is different depending on the application needs and database design. You need to extract the information you’re looking for by filtering and sorting the metadata so that you can focus on your dependency analysis. The queries used to extract this metadata will be used in the walkthrough section to retrieve the database metadata in CSV format for use by the database dependency analyzer tool.

The following queries contain references to the CO, HR, and SH users that are found in the sample Oracle schema used later in the walkthrough.

Dependencies

A dependency query gets the details of Oracle object dependences. For example, when a view is created on a table, a dependency query will return the child tables referencing a parent table and code objects such as packages, procedures, functions, and triggers when referenced in the object bodies. The following query lists each dependency from all_dependencies that belong to the CO, HR, and SH users.

SELECT name,
       type,
       referenced_name,
       referenced_type
FROM   all_dependencies
WHERE  referenced_name IN (SELECT table_name
                           FROM   dba_tables
                           WHERE  owner IN ( 'CO', 'HR', 'SH' ))
       AND referenced_owner IN ( 'CO', 'HR', 'SH' );

Parent-child

Parent-child dependency can be queried from Oracle system tables. The following query lists each active and unique parent-child relationship on objects that belong to the CO, HR, and SH users.

SELECT DISTINCT b.table_name "parent",
                a.table_name "child"
FROM   all_constraints a,
       all_constraints b,
       all_cons_columns c,
       all_cons_columns d
WHERE  a.r_constraint_name = b.constraint_name
       AND a.constraint_type = 'R'
       AND b.constraint_type IN ( 'P', 'U' )
       AND a.r_owner IN ( 'CO', 'HR', 'SH' )
       AND a.constraint_name = c.constraint_name
       AND b.constraint_name = d.constraint_name
       AND a.owner IN ( 'CO', 'HR', 'SH' )
       AND a.table_name = c.table_name
       AND b.owner IN ( 'CO', 'HR', 'SH' )
       AND b.table_name = d.table_name
       AND a.status = 'ENABLED'
ORDER  BY b.table_name;

Tables

A list of the storage objects, such as tables, can be queried from Oracle system tables. The following query lists each table object owned by the CO, HR, and SH users.

SELECT table_name
FROM   all_tables
WHERE  owner IN ( 'CO', 'HR', 'SH' );

Trigger details

A list of the triggers, including the trigger code body, can be queried from Oracle system tables. The following query lists each table trigger owned by the CO, HR, and SH users.

SELECT table_name,
       trigger_name,
       trigger_type,
       triggering_event,
       trigger_body
FROM   all_triggers
WHERE  owner IN ( 'CO', 'HR', 'SH' )
ORDER  BY 1;

Views

A list of the views can be queried from Oracle system tables. The following query lists each view object owned by the CO, HR, and SH users.

SELECT view_name
FROM   all_views
WHERE  owner IN ( 'CO', 'HR', 'SH' );

Walkthrough

To get an idea of how this tool might be useful for your modernization journey, scripts to install a sample Oracle schema have been provided with the tool. This walkthrough guides you to set up a local Oracle Docker container with the sample schema, run queries to gather database object details, then run the database dependency analyzer tool to review the results. The Oracle Docker container in combination with scripts provided by the GitHub repository will provide any additional tools (such as SQL*Plus) necessary to complete this walkthrough.

To use this tool on your own schema, follow this guide from the GitHub repository. It is not recommended to use the sample schema provided in this walkthrough in your own production databases.

Install tool dependencies

From a terminal in the root directory of this project, run:

npm install --omit=dev

Build and run the Oracle Docker container

From a terminal in the root directory of this project, run:

npm run sample:start-sample-db

This command will perform the following:

1. Download the Oracle sample schema
2. Modify the schema installation scripts to use password as the default password and provide defaults for prompts
3. Start a Docker container using a gvenzl/oracle-free image

It might take a few minutes for the Docker container to start up and install the sample schema. You can proceed to the next step after you see the following message in the container logs:

oracle_sample  | #########################
oracle_sample  | DATABASE IS READY TO USE!
oracle_sample  | #########################

Run SQL queries

From a new terminal in the root directory of this project, run:

npm run sample:execute-queries

This command will:

1. Run each query in sample/query-scripts to get the required CSV output files and store the CSV files in the data/ directory
2. Clean up extra spacing in each CSV file as a result of the SQL*Plus output

After these queries are run, you can terminate the Oracle Docker container instance by pressing CTRL-C from the terminal window with the container running.

Build a sample schema graph

From a terminal in the root directory of this project, run:

npm run analyzer build

This command will read the provided CSV files and generate three files:

• data/nodes.json: A complete listing of each database object and dependency
• object_stats.csv: Database objects statistics for further analysis
• data/visualization_data.json.gz: Data used by the visualization tool

Analyze results

With the SQL queries run and the schema graph built, it’s time to analyze the database object dependencies in depth.

Generate a usage report

To show a breakdown of which other objects reference (or use) a given database object, use the following command:

npm run analyzer usages <object> [type

Command parameters:

• <object> REQUIRED – The name of the database object (table, view, and so on.), case insensitive
• [type] OPTIONAL – The type of the database object. Can be TABLE, VIEW, PACKAGE, TRIGGER, FUNCTION, TYPE, SEQUENCE, or SYNONYM

Generate a usage report for the EMPLOYEES table by running the following command from a terminal:

npm run analyzer usages EMPLOYEES

The preceding command will output:

********************** START EMPLOYEES+TABLE **********************
TABLES(2):    DEPARTMENTS, JOBS
VIEWS(1):     EMP_DETAILS_VIEW
PACKAGES(0):
TRIGGERS(0):
FUNCTIONS(0):
TYPES(0):
SEQUENCES(0):
SYNONYMS(0):
********************** END EMPLOYEES+TABLE **********************

This output demonstrates the following:

• Two tables (DEPARTMENTS and JOBS) depend on the EMPLOYEES table
• One view (EMP_DETAILS_VIEW) depends on the EMPLOYEES table

Review database object statistics

Review database object relationship statistics by inspecting the generated object_stats.csv file.

By using sorting and filtering in Excel, you can see that the EMP_DETAILS_VIEW view has the most table dependencies of the database objects. The following table is a subset of the object_stats.csv file sorted by table dependencies in descending order.

Visualize database object dependencies

To visualize the dependencies for a database object, run the following command from a terminal:

npm run view

In the opened web page, select LOCATIONS from the table to observe its dependencies. The visualization will show how the LOCATIONS table has 5 unique dependencies spread across three different levels.

Cleanup

After you finish reviewing the dependencies, you can terminate the HTTP server hosting the web page by entering CTRL-C from the terminal window with the server running.

If you created the local Oracle Docker container as part of the preceding start-sample-db command, you can terminate it by entering CTRL-C from the terminal window in which the command was executed.

Conclusion

In this post, we showed you a custom-built tool to create and analyze a visual representation of Oracle database objects dependencies to aid the modernization of legacy databases. This tool can help you understand database object dependencies to modernize a monolithic Oracle database architecture. Eventually, this helps guide non-technical staff and management to understand interdependencies inside the database and help you break them apart, look for purpose-built AWS databases to migrate the data to, and build micro-services or macro-services.

To learn more about how the tool works, visit the GitHub repo page. For more resources, see:

• Build a Modern Application with Purpose-Built AWS Databases
• Let’s Architect! Leveraging SQL databases on AWS
• AWS Database Blog Category: RDS for Oracle

About the Authors

Suresh Konappanavar is a Team Lead and Sr. DB Consultant with AWS Professional Services. He helps customers migrate and modernize traditional databases on-premises or in the cloud. He specializes in the database arena to architect end-to-end database solutions and deployments.

Sam Wilson is a Cloud Application Architect with AWS Professional Services. He is passionate about accelerating customer growth using the ever-evolving landscape of AWS technologies. His current focus is on AWS serverless technologies and event-driven architecture.