Tracking Database Objects: Bringing Oracle's DBA_OBJECTS to PostgreSQL

If you've worked with Oracle databases, you know how handy DBA_OBJECTS is, its like having a live inventory of everything in your database that tracks every table, index, function, and view. You can see when objects were created, last modified, and whether they're valid or broken.

PostgreSQL doesn't have this out of the box. But what if you're migrating from Oracle or just want that same visibility? That's exactly why we built a PostgreSQL wrapper that mimics DBA_OBJECTS for DDL tracking using event triggers features.

What Does DBA_OBJECTS Do in Oracle?

Think of DBA_OBJECTS as your database's logbook. It automatically tracks:

• What exists: Tables, views, indexes, sequences, functions—everything

• When it changed: Creation dates and last modification times

• Its health: Whether objects are valid or have errors that can be cause due to dependencies.

  
  

When you create a table, it shows up immediately. Alter it? The timestamp updates. Drop it? It disappears from the view. No setup required—it just works.

The PostgreSQL Challenge

PostgreSQL has system catalogs like pg_class and pg_namespace, but they don't track modification history or object status the way Oracle does. You can see what exists now, but you can't easily answer questions like "What changed last week?" or "When was object created?" Some of the options available in PostgreSQL to track DDL activities are not as seamless as in databases like Oracle.

One common approach is setting log_statement = 'ddl' in postgresql.conf, which captures DDL statements in server logs. However, this information resides only in text log files not within the database itself making it harder to query or analyze changes directly from SQL. While it provides basic visibility into DDL operations, it lacks the convenience of an in-database, queryable audit trail that DBAs often rely on for efficient change tracking and compliance.

Our Solution: An Event Trigger-Based Tracker

We built a custom tracking system that brings Oracle's DBA_OBJECTS experience to PostgreSQL currenly only for DDL tracking.

How it works:

1. Initial population – We scan existing objects and load them into a tracking table

2. Event triggers – These automatically capture DDL operations (CREATE, ALTER, DROP) as they happen

3. Real-time updates – Every change updates the tracker with timestamps as last DDL and operation types.

   
   

Github Repo - pg_dba_objects

What you can track:

• Tables, views, and materialized views

• Indexes and sequences

• Functions and procedures

• Schemas and custom types

• object's creation time, last DDL time, and status

What you cannot track:

• Dependencies based object validity.

Why This Matters

For Oracle DBAs moving to PostgreSQL, this wrapper feels instantly familiar. You get the same auditing capabilities you're used to, without learning entirely new system catalogs.

Even if you're a PostgreSQL-native team, having centralized object tracking means:

• Easier troubleshooting – Quickly identify when and how objects changed

• Better auditing – See who created what and when

• No log parsing – Query structured data instead of grep-ing through text files

• Zero application changes – Works transparently at the database level

The Bottom Line

Object tracking is more than a convenience—it’s essential for managing production databases effectively. PostgreSQL already provides powerful hooks like event triggers for tracking schema changes, but setting them up efficiently can take effort.

That’s where we’ve stepped in. We’ve built an wrapper that brings Oracle-like object tracking simplicity to PostgreSQL. Whether you're migrating from Oracle or looking to streamline object visibility, our solution delivers the objects insights every DBA needs.