Is it true you can recover package or procedure or function or any other objects apart from table? Guess its true, starting with 9i this can be performed with flashback technology.

Scenario tested on 9i database.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Solaris: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

Creating a temporary procedure.

SQL> create or replace procedure Testproc as
begin
dbms_output.put_line('Test Case');
end;
/  2    3    4    5

Procedure created.

Get the timestamp

SQL> select to_char(sysdate,'dd-Mon-YYYY hh24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
02-Mar-2017 13:18:31


SQL> select text from dba_source where name='TESTPROC';

TEXT
--------------------------------------------------------------------------------
procedure Testproc as
begin
dbms_output.put_line('Test Case');
end;

Lets drop the test procedure.

SQL> drop procedure Testproc;

Procedure dropped.

SQL> select to_char(sysdate,'dd-Mon-YYYY hh24:MI:SS') from dual;

TO_CHAR(SYSDATE,'DD-
--------------------
02-Mar-2017 13:46:03

Lets see if we can find our test procedure post drop.

Either of the command can be used

SQL> select text from dba_source  as of timestamp to_timestamp('02-Mar-2017 13:45:21','DD-Mon-YYYY hh24:MI:SS') where NAME='TESTPROC';

TEXT
--------------------------------------------------------------------------------
procedure Testproc as
begin
dbms_output.put_line('Test Case');
end;

SQL> select text from dba_source  as of timestamp systimestamp - interval '1' minute where NAME='TESTPROC';

TEXT
--------------------------------------------------------------------------------
procedure Testproc as
begin
dbms_output.put_line('Test Case');
end;

oh great, even though the procedure actually do not exist (in dba_objects) I can still get my procedure back with flashback query.

SQL> select object_name, object_type from dba_objects where object_name='TESTPROC';
no rows selected

SQL>

Ref: flashback a package/ procedure / trigger – overwritten or dropped (Doc ID 1622153.1)

Advertisements