[ORACLE 11GR2] Flashback Query Example

Flashback Query Example Expert Oracle Database Tips by Donald BurlesonMarch 25, 2015

RMAN - Oracle Flashback Query

Using Oracle flashback query,  you can retrieve the committed data as it was at a past point in time. As most of other flashback technologies, this feature retrieves data from the undo tablespace. It is possible to get undo data based on a specific time or scn number. Using the select as of statement with both OF TIMESTAMP and OF SCN clauses, you can use this feature easily. It can even be performed by any application developer without any DBA intervention. Developers can use the dbms_flashback package to perform flashback query directly from their applications, but the execute privilege should be granted on the dbms_flashback package. Now see it in action through the following scenario:

• Scenario 1:  At 12.00, Bob got a call from an application developer who told him that somebody deleted all the rows from one of the tables by omitting the WHERE clause and committing the transaction. He wanted to know which rows were deleted from the table. Bob decided to use flashback query to get the before image of the deleted rows.

To test a case similar to Bob's situation, perform the following steps:

1. Create a table as it was created by the application developer and query it as follows:

SQL>
create
 table tbl_seg_tbs as
  2  select
 segment_name, tablespace_name
from
 dba_segments
  3  where
 rownum<7
order by
 bytes desc;
Table created.

SQL>
select
 count(*)
from
 tbl_seg_tbs;

  COUNT(*)
----------
         6
SQL>

2. In order to return back to this state of the table anytime, get the current scn number and timestamp:

SQL>
select
 to_char(sysdate,'dd-mm-yyyy  hh24:mi:ss') ddate,
dbms_flashback.get_system_change_number() scn from dual;

DDATE                       SCN
-------------------- ----------
07-02-2010  15:14:21     460141

This step was not performed by Bob or by the application developer. The developer knows the time when the table was in a correct state and told that time to Bob. Here you get the date and current scn number just for testing purposes.

3. Now run the delete command to clear the table and commit the transaction:

SQL>
delete from
 tbl_seg_tbs;
6 rows deleted.

SQL>
commit;
Commit complete.

SQL>
select * from
 tbl_seg_tbs;
no rows selected

4. As Bob knows the exact time of the correct state of the table, he uses flashback query and retrieves the before state of deleted rows from the undo tablespace as follows:

SQL>
select
count(*)
from tbl_seg_tbs
  2  as of
 timestamp to_timestamp('07-02-2010 15:14:21','dd-mm-yyyy hh24:mi:ss');

  COUNT(*)
----------
         6
SQL>

In case he knows the scn number, he uses the OF SCN clause to view the data of the table at the specified scn as follows:

SQL>
select * from
 tbl_seg_tbs
  2  as of scn 460135;

  COUNT(*)
----------
         6
SQL>

To view the data of the table as it was 15 minutes ago, use the following query:

SQL>
select
 count(*)
from tbl_seg_tbs
2       as of timestamp (systimestamp -interval '15' minute);
COUNT(*)
----------
         6
SQL>

It is possible to convert scn to timestamp and timestamp to scn using scn_to_timestamp and timestamp_to_scn functions:

SQL>
select scn_to_timestamp(460141) ddate,
timestamp_to_scn(to_timestamp('07-02-2010 15:14:21','dd-mm-yyyy
hh24:mi:ss')) scn from dual;
DDATE                                     SCN
------------------------------------      ----------
07-FEB-10 03.14.21.000000000 PM     460141
SQL>

Viewing/Retrieving the Dropped PL/SQL Object codes using Flashback Query
You can retrieve any dropped PL/SQL object using flashback query. How? It is very easy. The source of these objects is stored in an internal table:  sys.source$. It means that when you create a trigger, the line that contains its source is added to that table. The same works for dropping the procedure.  Please note that recreating the objects relies on the dependencies being valid; thus, any tables/views referenced in the procedure must be present or recreated before recreating the procedure. So see how it works:

1. Create a simple trigger and get the current scn valueof the database:

SQL>
create or replace
 trigger trg_logon
  2  after logon on database
  3  begin
  4  insert into tbl_logons values(sysdate, user);
  5  end;
  6  /

Trigger created.

SQL>
select
 current_scn
from
 v$database;

CURRENT_SCN
-----------
     528857
SQL>

2. Now drop the trigger.

SQL>
drop
 trigger trg_logon;
Trigger dropped.
SQL>

3. Using the scn value that has been noted before dropping the trigger, query the dba_source view as follows:

SQL>
select
 text
from dba_source
as of
 scn 528857
  2  where name='trg_logon';

TEXT
---------------------------------------------------
trigger trg_logon
after logon on database
begin
insert into tbl_logons values(sysdate, user);
end;
SQL>

Using the about result text code, you can create or replace the trigger again.