Flashback a package - from the current schema

With response to the post Recover a dropped or changed package , there was a question raised “we can do this ONLY logging as SYS. How to recover from the very same schema it was overwritten??”, the answer is Yes, but we need to take a different route.

SQL> conn thomas/thomas
Connected.

SQL> create or replace procedure myproc as
 2 begin
 3 dbms_output.put_line('Thia is a test Procedure');
 4 end;
 5 /
Procedure created.

SQL> select to_char(sysdate,'dd-Mon-YYYY hh24:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD-
--------------------
31-May-2012 10:31:52

So, I have my original package is in place and timestamp is captured for easy recovery.  Now I am going to over write the package with a new version.

SQL> create or replace procedure myproc as
 2 begin
 3 dbms_output.put_line('Thia is my new procedure');
 4 end;
 5 /
Procedure created.

To undo the changes, I will try to flashback the procedures from own schema.

SQL> select TEXT from dba_source as of timestamp to_timestamp('31-May-2012 10:31:52','DD-Mon-YYYY hh24:MI:SS')
where NAME='MYPROC' and owner='THOMAS';
select TEXT from dba_source as of timestamp to_timestamp('31-May-2012 10:31:52','DD-Mon-YYYY hh24:MI:SS')
where NAME='MYPROC' and owner='THOMAS'
 *
ERROR at line 1:
ORA-00942: table or view does not exist

The statement error out as my user do not have DBA privileges and can’t see DBA_SOURCE view or synonym. So, granted DBA to the account.

SQL> conn / as sysdba
Connected.

SQL> grant DBA to thomas
 2 ;
Grant succeeded.

SQL> conn thomas/thomas
Connected.

SQL> select TEXT from dba_source as of timestamp
 2 to_timestamp('31-May-2012 10:31:52','DD-Mon-YYYY hh24:MI:SS')
 3 where NAME='MYPROC' and owner='THOMAS';
select TEXT from dba_source as of timestamp
 *
ERROR at line 1:
ORA-01031: insufficient privileges

Still, I was not able to flashback the DBA_SOURCE view, Oracle do not support it directly, but using SYS user i was able to get the past image.

SQL> select TEXT from dba_source as of timestamp
 2 to_timestamp('31-May-2012 10:31:52','DD-Mon-YYYY hh24:MI:SS')
 3 where NAME='MYPROC' and owner='THOMAS';
TEXT
------------------------------------------------------------------------------------------------------------------------------------
procedure myproc as
begin
dbms_output.put_line('Thia is a test Procedure');
end;

Now,  how can I do this from my schema!  Looking at the DBA_SOURCE view definition, it is built on SYS.SOURCE$  and  SYS.USERS$ which are the Oracle base tables.  The view definition is

SQL> select TEXT from dba_views where VIEW_NAME ='DBA_SOURCE';
TEXT
--------------------------------------------------------------------------------
select u.name, o.name,
decode(o.type#, 7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
 11, 'PACKAGE BODY', 12, 'TRIGGER', 13, 'TYPE', 14, 'TYPE BODY',
 'UNDEFINED'),
s.line, s.source
from sys."_CURRENT_EDITION_OBJ" o, sys.source$ s, sys.user$ u
where o.obj# = s.obj#
 and o.owner# = u.user#
 and ( o.type# in (7, 8, 9, 11, 12, 14) OR
 ( o.type# = 13 AND o.subname is null))
union all
select u.name, o.name, 'JAVA SOURCE', s.joxftlno, s.joxftsrc
from sys."_CURRENT_EDITION_OBJ" o, x$joxfs s, sys.user$ u
where o.obj# = s.joxftobn
 and o.owner# = u.user#
 and o.type# = 28

And when falshback DBA_SOURCE, in fact we are flashingback the SYS.SOURCE$.  If we have explicit privilage “FLASHBACK” on a table, then we can falshback this base table directly.  So, let us grant flashback to my account for the table SYS.SOURCE$.

SQL> conn / as sysdba
Connected.

SQL> grant flashback on source$ to thomas;
Grant succeeded.

Now let us try to flashback using the obj# from SOURCE$ base table directly.

SQL> select object_id from dba_objects
 2 where object_name='MYPROC' and owner='THOMAS';
OBJECT_ID
----------
 52519

SQL> conn thomas/thomas
Connected.

SQL> show user
USER is "THOMAS"

SQL> select SOURCE from sys.source$ as of timestamp
 2 to_timestamp('31-May-2012 10:31:52','DD-Mon-YYYY hh24:MI:SS')
 3 where obj#=52519 ;
SOURCE
------------------------------------------------------------------------------------------------------------------------------------
procedure myproc as
begin
dbms_output.put_line('Thia is a test Procedure');
end;

That’s it.  You got your flashback done in your own schema.

About these ads