Flashback package source in Oracle 9.2

A developer came to me that he messed up his package source he was working on.

He asked me if we could retrieve it from backup, preferable from an hour ago. This is typical a flashback scenario, but he was working on 9.2. Is this a problem? No, we can use Flashback Query in 9.2. We just have to query the dba_source view.

The database (development) is not running in archivemode, so we can flash back to any point within the redo logs. This time we flash back to about half an hour ago.

The output is saved in a spool file.

ALTER SESSION 
   SET NLS_TIMESTAMP_FORMAT = 
   'DD-MON-YYYY HH24:MI:SS.FF3';

set lines 9999

set trimspool on

set pages 9999

set heading off

spool old_version.sql

select text from dba_source  

       as of timestamp to_timestamp('22-SEP-2011 07:30:00.000')

 where owner='SCOTT'

   and name='MYPACKAGE'

 order by type, line;

spool off