Reverse Engineering an Oracle Database

How an Oracle schema maps to a database user

For database architects who need to inspect an existing Oracle database and verify whether their model captures every table and relation.

In Oracle Database 23ai, a schema and a database user are the same technical identity. When you create a database user account, Oracle creates a schema of the same name to contain all database objects that the user creates and owns. This design differs from relational engines such as PostgreSQL or MySQL, where schemas exist as separate logical namespaces within a database catalog.

When you configure a database design tool to reverse engineer an Oracle instance, the schema selection step is a choice among database users. You select the specific user accounts that own your application tables rather than selecting directories or database names. If an application stores its data under an account named HR or SALES_APP, selecting that user in the connection dialog tells the introspection query to inspect only the objects owned by that account.

• Application owner accounts (such as APP_CORE or SALES_DATA)
• Shared utility schemas (such as COMMON_UTILS)
• System administrative accounts (such as SYS, SYSTEM, or AUDSYS, which you omit from application diagrams)

Comparing the USER, ALL, and DBA dictionary views

Oracle exposes its data dictionary through views, and many dictionary tables have three corresponding views distinguished by their prefix[1]. Each prefix represents a different level of visibility based on the privileges granted to the connecting database user.

• USER_ views: Show only the objects owned directly by the connecting database user.
• ALL_ views: Show all objects the connecting user has permission to access, whether owned directly or granted through explicit privileges, roles, or PUBLIC.
• DBA_ views: Show every object across the entire database instance, requiring administrative privileges like DBA or SELECT_CATALOG_ROLE.

Database introspection reads a small set of static views to build an entity-relationship model: ALL_TABLES for table definitions, ALL_TAB_COLUMNS, which describes the columns of the tables, views, and clusters accessible to the current user, including each column's data type and default value[2], ALL_CONSTRAINTS for primary, unique, check, and referential definitions, and ALL_CONS_COLUMNS, which describes columns that are accessible to the current user and that are specified in constraints[3].

Foreign key relationships are recorded in ALL_CONSTRAINTS with the CONSTRAINT_TYPE column set to 'R' for referential integrity. The R_CONSTRAINT_NAME column references the primary or unique constraint on the parent table, while ALL_CONS_COLUMNS defines the exact foreign key and referenced column pairs.

Why a low-privilege connection returns a partial model

When you connect to Oracle using a low-privilege user account, the database does not raise an error when tables exist outside your grant scope. Instead, Oracle filters ungranted objects out of the ALL_ views silently. An introspection run against ALL_TABLES finishes without warnings and generates a clean diagram, yet tables belonging to the application can be missing if the connecting user lacks SELECT or REFERENCES privileges on them.

To verify that your introspection captured every table in the target schema, run a verification query in your SQL client. Compare the count of tables visible in ALL_TABLES against DBA_TABLES when your account has access, or compare against the schema owner's expected table count:

``sql SELECT owner, COUNT(*) AS visible_tables FROM all_tables WHERE owner = 'SALES_APP' GROUP BY owner; ``

When connected with administrative privileges, compare that total directly against the database catalog:

``sql SELECT owner, COUNT(*) AS catalog_tables FROM dba_tables WHERE owner = 'SALES_APP' GROUP BY owner; ``

If visible_tables is lower than catalog_tables, your connecting account lacks dictionary visibility. Granting the SELECT_CATALOG_ROLE role to the connecting user fixes the gap across data dictionary views without granting data modification rights on the underlying application tables.

Reading structure with DBMS_METADATA.GET_DDL

Database introspection reads schema structure (tables, views, columns, data types, constraints, indexes, sequences, and comments) rather than table row data. Oracle provides the DBMS_METADATA PL/SQL package to extract complete creation DDL for individual objects directly from the data dictionary[4].

You can execute GET_DDL in SQL*Plus to extract the full SQL definition of a table, including column definitions, constraints, and storage parameters:

``sql SET LONG 2000000 SET PAGESIZE 0 SELECT DBMS_METADATA.GET_DDL('TABLE', 'ORDERS', 'SALES_APP') FROM DUAL; ``

The query returns the complete CREATE TABLE script with all constraints declared in the dictionary:

``text CREATE TABLE "SALES_APP"."ORDERS" ( "ORDER_ID" NUMBER(10,0) NOT NULL ENABLE, "CUSTOMER_ID" NUMBER(10,0) NOT NULL ENABLE, "ORDER_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE, "TOTAL_AMOUNT" NUMBER(12,2), CONSTRAINT "PK_ORDERS" PRIMARY KEY ("ORDER_ID") ENABLE, CONSTRAINT "FK_ORDERS_CUSTOMER" FOREIGN KEY ("CUSTOMER_ID") REFERENCES "SALES_APP"."CUSTOMERS" ("CUSTOMER_ID") ENABLE ) ``

While DBMS_METADATA.GET_DDL supplies exact DDL text for one object at a time, it produces a flat text output. It does not generate visual entity-relationship diagrams, does not group tables into logical layouts, and cannot identify relationships that the database dictionary never recorded.

Connecting to Oracle over JDBC

Introspection over a JDBC thin driver needs no local Oracle Client or OCI installation, because the thin driver is written in pure Java and talks to the listener directly. A design tool that ships or downloads the driver itself therefore connects with nothing more than a host, a port, a service name, and credentials.

The standard JDBC connection string uses this format:

``text jdbc:oracle:thin:@//hostname:1521/service_name ``

In the connection dialog for Oracle database management, you enter the host name, listener port (default 1521), service name, and user credentials. Once connected, you select which schemas to introspect, and the tool queries the data dictionary views to extract tables, columns, foreign keys, and indexes, placing them directly into an interactive diagram layout.

Drawing relations the dictionary does not declare

Many production schemas, including data warehouses and ERP databases, omit declarative foreign key constraints to improve write throughput or because referential integrity is handled in PL/SQL packages and application code. When you reverse engineer these databases, a standard dictionary query returns isolated tables with no connecting relationship lines.

A modelling tool can let you create virtual foreign keys directly on the diagram canvas. A virtual foreign key defines a logical relation between a child column and a parent primary key column, stored entirely inside your local design model.

Virtual foreign keys give you two distinct capabilities: they draw relationship lines on the diagram canvas for clear schema documentation, and they enable the relational data explorer to traverse related records across tables. Because virtual foreign keys exist only in the local model, adding them executes no ALTER TABLE statements, modifies no dictionary tables, and places no locks on production tables.

What the offline design model file holds

When you introspect an Oracle schema, the structural definition is saved to a local XML model file (.dbs). This file stores the table metadata, data types, physical constraints, virtual foreign keys, diagram layouts, groups, colors, and callouts.

Because the model is a standard XML document, you can inspect it in any text editor, commit it to Git version control, and review schema diffs across branches. The model file operates completely offline, allowing you to examine schemas and produce database documentation without maintaining an open session to the database server.

You can download DbSchema Community Edition at https://dbschema.com/download.html to connect to your Oracle database, reverse engineer your schema into an interactive diagram, and check whether your model captures the complete database structure.

Frequently asked questions

Why does reverse engineering an Oracle database return missing tables?

If you connect with a low-privilege account, Oracle's ALL_TABLES and ALL_CONSTRAINTS views only report objects your account owns or has been granted access to. Objects you cannot see are silently omitted, producing a partial model. You must use an account with the SELECT_CATALOG_ROLE or DBA role to view the complete database structure via DBA_ views.

What is the difference between USER_, ALL_, and DBA_ dictionary views in Oracle?

The USER_ views show only the objects the current user owns. The ALL_ views show all objects the current user has been granted permission to access. The DBA_ views display every object in the database, but querying them requires administrative privileges like the SELECT_CATALOG_ROLE.

Can I extract the DDL of an Oracle database object from SQL*Plus?

Yes, you can use the DBMS_METADATA.GET_DDL function to extract the exact Data Definition Language script for a specific object, such as a table or index. This is useful for reading structure, but it does not map cross-table relations or generate visual entity-relationship diagrams.

How do I draw relationships in a data warehouse where foreign keys are missing?

When integrity is enforced in PL/SQL or the application rather than by database constraints, you can use virtual foreign keys. These relations live only in the offline design model to help you explore data and draw diagrams, without executing any ALTER TABLE commands or locking production tables.

What does an offline design model file hold?

The design model is an XML file that stores the schema structure plus every diagram, layout, group, colour and callout. It reads structure rather than rows, allowing architects to work on the design offline or behind a jump host without keeping a live session open.

Sources

1. docs.oracle.com
2. docs.oracle.com
3. docs.oracle.com
4. docs.oracle.com