What did my object definition look like a few minutes ago?

Occasionally (maybe after a bad deployment) you may want to see what the old version of the code/objects looked like prior to everything changing. Of course you will of taken a backup of everything that is changed beforehand so you could compare if you needed to wouldn't you....?

Well if you didn't other than restoring the database back there is a way to see what stored code and table/view definitions looked like as long as you haven't waited too long and have enough undo left using flashback query against the data dictionary - lets do a simple example with a table:

Here is the definition of a simple table before i did anything

desc test.test
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COL1                                               NUMBER

Now i add a column to it

alter table test.test add (col2 date);

How can i now see what the columns looked like before i did the change? (in this example i already know of course but what if i didn't)

The current definition can be found from this simple select

select table_name,column_name,data_type from dba_tab_columns  where owner='TEST' and table_name='TEST' order by COLUMN_ID;

TEST                           COL1
NUMBER

TEST                           COL2
DATE

And to see what it looked like in the past (as long as we have the undo) we can run this

select table_name,column_name,data_type from dba_tab_columns as of timestamp sysdate -1/24 where owner='TEST' and table_name='TEST' order by COLUMN_ID;

TEST                           COL1
NUMBER

So it can be as simple as that - this will also work for DBA_SOURCE,DBA_TRIGGERS etc.
A useful trick to have though you may hardly ever have the need.