Close Menu

    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:

    Download the Oracle sample schema
    Modify the schema installation scripts to use password as the default password and provide defaults for prompts
    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:

    Run each query in sample/query-scripts to get the required CSV output files and store the CSV files in the data/ directory
    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.

    name
    type
    table dependencies

    EMP_DETAILS_VIEW
    VIEW
    12

    REGIONS
    TABLE
    10

    COUNTRIES
    TABLE
    9

    CUSTOMER_ORDER_PRODUCTS
    VIEW
    8

    PRODUCT_ORDERS
    VIEW
    8

    PRODUCT_REVIEWS
    VIEW
    5

    STORE_ORDERS
    VIEW
    5

    CUSTOMERS
    TABLE
    4

    PRODUCTS
    TABLE
    4

    STORES
    TABLE
    4

    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.

    Source: Read More

    Related Posts

    Leave A Reply