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.

  8. #8 by Alecia on July 25, 2014 - 00:49

    You can always rely on the internet for research and more information on different types of air purifier.

    Now after shoveling snow and exposing my face to the elements that had me sold at the gate.
    It contains some robust pre-filter that makes purifying extra efficient.

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,603 other followers

%d bloggers like this: