Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Recover dropped package / Procedure

Posted on 2011-10-03
2
Medium Priority
?
3,000 Views
Last Modified: 2012-08-14
I accidentally dropped a procedure on production database and We don't have an up to date copy of that code on Source Safe ( version control).


How to recover the dropped procedure?
0
Comment
Question by:vishali_vishu
2 Comments
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 1000 total points
ID: 36902900
If this wasn't done that long ago try flashback query:

create table source_recovery as
(SELECT * FROM DBA_SOURCE AS OF TIMESTAMP ('03-OCT-11 8:00:00','DD-MON-YY HH24: MI: SS'));

Of course use a timestamp before the drop.

If this doesn't work, you will likely have to restore a backup on a different server.
0
 
LVL 48

Assisted Solution

by:schwertner
schwertner earned 1000 total points
ID: 36909049
select text from dba_source
     as of timestamp systimestamp - interval '1500' minute
     where name='PROCEDURE_NAME_HERE' and type = 'PROCEDURE'
order by line asc;

adjust the clause
 '1500' minute
to point to the time just before you dropped the procedure

0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month20 days, 15 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question