Retrieve old versions of procedures with Flashback Query

Each stored procedure (or trigger, package etc.) is reflected in DBA_SOURCE, of course. Now imagine, you modified or even dropped a procedure without having the code available to recreate it properly. In this case, you may save the day by using Flashback Query (possible since 9i) against DBA_SOURCE, like in this example:

SQL> select text from dba_source where name='UPD_SALES' order by line;

TEXT
--------------------------------------------------------------------------------
procedure upd_sales as
begin
for i in 1..10000 loop
update sales set amount_sold=amount_sold*1 where rownum<2;
commit;
end loop;
end;

7 rows selected.

SQL> drop procedure upd_sales;

Procedure dropped.

SQL> connect / as sysdba
Connected.

SQL> select text from dba_source 
     as of timestamp systimestamp - interval '5' minute  
     where name='UPD_SALES' order by line;

TEXT
--------------------------------------------------------------------------------
procedure upd_sales as
begin
for i in 1..10000 loop
update sales set amount_sold=amount_sold*1 where rownum<2;
commit;
end loop;
end;

7 rows selected.

I could spool the output from above into a text file and use it afterwards to recreate my procedure as it was before 5 minutes! By the way, I know that the commit inside the loop is not a good idea here :-)

About these ads

  1. #1 by coskan on June 3, 2009 - 15:05

    Never thought to use it that way nice one. Thank you UWE

  2. #2 by Kamran Agayev A. on September 4, 2009 - 12:39

    Nice solution :)

  3. #3 by Junko Greto on December 20, 2010 - 03:02

    I don’t unremarkably comment but I gotta admit appreciate it for the post on this special one : D.

  4. #4 by wii guitar on June 29, 2011 - 21:13

    would love to forever get updated great site ! .

  5. #5 by Edgar on February 23, 2013 - 22:02

    very cool solution, thanks for sharing this!!

  6. #6 by Joshua on April 15, 2013 - 23:21

    Can we use the same query to retrieve the a package that was compiled 10 days ago? If yes, can you please let me know how?

  7. #7 by Uwe Hesse on April 16, 2013 - 08:42

    Joshua, that is a bit unlikely, because the flashback query relies on before images in the undo tablespace – which are probably overwritten after 10 days. Just replace interval ’5′ minute with interval ’10′ day to try.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 2,429 other followers

%d bloggers like this: