How to recover a dropped or overriden PL/SQL using Flashback.

Well, things happen. Just recently I've mistakenly run an older script that does "CREATE OR REPLACE PACKAGE ...". :(
And that happen the latest changes I've made using SQL*Developer have gone also. :(

So, what to do?
I could restore the entire DB to the point in time before the change.
But, I just was too lazy to do it. Also, in some cases such a global approach can eliminate other recent works. It was not mine case though, but you have to consider it.

Fortunately Oracle has a Flashback!

So I decided to try this. My plan was to execute following

SELECT * FROM DBA_SOURCE AS OF TIMESTAMP
TO_TIMESTAMP('2010-01-20 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE TEXT LIKE '%1.0.1.38%';

Here 1.0.1.38 is a version number in a comment line that I usually embed into my code.
And the older version of my PL/SQL, that had been written over is 1.0.1.37.
So, I needed my 1.0.1.38 back.
However the result returned by the above query always was the current package code with a version 1.0.1.37, which was not my target.

Then I've decided to try with querying the table directly. The table is SYS.SOURCE$.

SELECT * FROM SYS.SOURCE$ AS OF TIMESTAMP
TO_TIMESTAMP('2010-01-20 16:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE TEXT LIKE '%1.0.1.38%';

Voila! It was there!

Then I just took the proper OBJ# and spooled all rows of that object into a text file.
Ta-daa!

However, to be successful with this method, you have to be really quick.
Otherwise you can receive following error if some significant time has gone from the moment of overriding.

ORA-01555: snapshot too old: rollback segment number 6 with name "_SYSSMU6_1192467665$" too small
01555. 00000 - "snapshot too old: rollback segment number %s with name \"%s\" too small"
*Cause: rollback records needed by a reader for consistent read are
overwritten by other writers
*Action: If in Automatic Undo Management mode, increase undo_retention
setting. Otherwise, use larger rollback segments

In this case you have to resort to traditional recovery methods, like a point-in-time DB recovery or an import, or a Log Miner if archived logs for that time have not been removed.