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

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.