How to Identify Who Uses Oracle Objects in PostgreSQL

Galaxy Glossary

How do I check which PostgreSQL objects depend on an Oracle-migrated table?

Shows all database objects that depend on, or reference, a specified Oracle-migrated table or view inside PostgreSQL.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

Why would I need to know who uses an Oracle-migrated table?

Before altering or dropping a table migrated from Oracle, verify which PostgreSQL views, functions, triggers, or foreign keys depend on it to avoid breaking production code.

What catalog tables expose object dependencies?

Use pg_depend, pg_class, pg_namespace, and pg_proc. These catalogs record every relationship between source and dependent objects.

How do I list all objects that reference a table?

Join pg_depend to pg_class twice—once for the referenced table, once for the dependent object. Filter on classid and objid to capture views, materialized views, functions, triggers, indexes, and constraints.

Which parameters are essential in the query?

:schema_name and :table_name identify the Oracle table. Optionally add :object_types to limit results to VIEW, FUNCTION, TRIGGER, etc.

Best practice for production databases?

Run dependency checks in a transaction and on a read-replica when possible. Export the list to code review tools so application teams can confirm impact before DDL changes.

Does this work for Oracle FDW objects?

Yes. Foreign tables created by oracle_fdw appear in pg_class; the same dependency logic applies.

Why How to Identify Who Uses Oracle Objects in PostgreSQL is important

How to Identify Who Uses Oracle Objects in PostgreSQL Example Usage


-- Who uses the migrated "Products" table?
WITH target AS (
    SELECT c.oid AS tbl_oid
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = 'public'
      AND c.relname  = 'Products'
)
SELECT DISTINCT n2.nspname AS dependent_schema,
                c2.relname AS dependent_object,
                CASE c2.relkind
                    WHEN 'v' THEN 'VIEW'
                    WHEN 'm' THEN 'MATERIALIZED VIEW'
                    WHEN 'r' THEN 'TABLE'
                    WHEN 'f' THEN 'FOREIGN TABLE'
                    WHEN 'i' THEN 'INDEX'
                END AS object_type
FROM pg_depend d
JOIN target    t  ON d.refobjid = t.tbl_oid
JOIN pg_class  c2 ON c2.oid = d.objid
JOIN pg_namespace n2 ON n2.oid = c2.relnamespace
WHERE d.deptype IN ('n','a')
ORDER BY object_type, dependent_schema, dependent_object;

How to Identify Who Uses Oracle Objects in PostgreSQL Syntax


-- Core dependency-check template
WITH target AS (
    SELECT c.oid AS tbl_oid
    FROM pg_class c
    JOIN pg_namespace n ON n.oid = c.relnamespace
    WHERE n.nspname = :schema_name
      AND c.relname  = :table_name
)
SELECT DISTINCT
       n2.nspname  AS dependent_schema,
       c2.relname  AS dependent_object,
       CASE c2.relkind
           WHEN 'v' THEN 'VIEW'
           WHEN 'm' THEN 'MATERIALIZED VIEW'
           WHEN 'r' THEN 'TABLE'
           WHEN 'i' THEN 'INDEX'
           WHEN 'f' THEN 'FOREIGN TABLE'
           ELSE 'OTHER'
       END         AS object_type
FROM pg_depend      d
JOIN target         t  ON d.refobjid = t.tbl_oid
JOIN pg_class       c2 ON c2.oid   = d.objid
JOIN pg_namespace   n2 ON n2.oid   = c2.relnamespace
WHERE d.deptype IN ('n','a');

Common Mistakes

Running the query in the wrong schema. Fix: always filter by both schema and table name; similarly named tables in other schemas will otherwise appear.
Ignoring functions and triggers. Fix: include <code>pg_proc</code> joins or use <code>pg_get_functiondef</code> to parse code for references when the dependency catalog shows none.