Flashback the procedure/package recover in Oracle

Flashback the procedure/package changes done at specific time by flashback feature in Oracle.
If we drop any procedure accidently then we recover from flashback.

In Example, we restore the dropped or changed procedure/package through flashback

  1. Check the object id of the package/procedure/function.
SQL> select object_id from dba_objects where owner='HR' and object_name='FIND_DEPARTMENT_ID';
OBJECT_ID
----------
5024237

2. Retrieve the flashback code using timestamp.

SQL> select SOURCE from sys.source$ as of timestamp to_timestamp('23-Apr-2017 10:00:20','DD-Mon-YYYY hh24:MI:SS') where obj#=5024237;

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.