CODEWITHSUNDEEP
oracle-databaseddl
Laird Nelson
Laird Nelson

Reputation: 16154

Can I detect the version of a table's DDL in Oracle?

In Informix, I can do a select from the systables table, and can investigate its version column to see what numeric version a given table has. This column is incremented with every DDL statement that affects the given table. This means I have the ability to see whether a table's structure has changed since the last time I connected.

Is there a similar way to do this in Oracle?

Upvotes: 2

Views: 2058

Answers (3)

Adam Musch
Adam Musch

Reputation: 13583

If you really want to do so, you'd have to use Oracle's auditing functions to audit the changes. It could be as simple as:

AUDIT ALTER TABLE WHENEVER SUCCESSFUL on [schema I care about];

That would at least capture the successfuly changes, ignoring drops and creates. Unfortunately, unwinding the stack of the table's historical strucuture by mining the audit trail is left as an exercise to the reader in Oracle, or to licensing the Change Management Pack.

You could also roll your own auditing by writing system-event triggers which are invoked on DDL statements. You'd end up having to write your own SQL parser if you really wantedto see what was changing.

Upvotes: 2

Pop
Pop

Reputation: 4022

You can do that (and more) with a DDL trigger that keeps track of changes to tables. There's an interesting article with example here.

Upvotes: 3

dpbradley
dpbradley

Reputation: 11915

Not really. The Oracle DBA/ALL/USER_OBJECTS view has a LAST_DDL_TIME column, but it is affected by operations other than structure changes.

Upvotes: 3

Related Questions