Flashback queries in Oracle

Reference taken from one of my article that was published on Experts-Exchange .

With the help of flashback queries we have the option to look back in time to a certain state of a database, for example say we did some operation yesterday at 10 AM and today we want to go back to the same state where we started yesterday. So using Oracle flashback queries we have the ability to go back into time to a certain state of a database.

This can be achieved by using the system change number (SCN) or using a timestamp. We also have the option to select a range of system change number or timestamp values that will help us to go back to the point of time. This option will only help us to see the past stage of the database, not the future.

How this is possible is the first question that we can think about. This is possible as because all the data in databases are stored in data files and the redo log file is more essential here; in other words we are doing a rollback.

Syntax of flashback queries:


SELECT … FROM [schema.]table [alias] [ AS OF { SCN | TIMESTAMP } expr | VERSIONS BETWEEN { SCN | TIMESTAMP} { expr | MINVALUE } AND { expr | MAXVALUE } ] [ WHERE … ] [ GROUP BY … ] [ ORDER BY … ]

AS OF” will take you to back in time to a previous state of a table against a time or system change number.  For example we want to see the records in a table 10 minutes ago then we can use the “AS OF” condition with the SCN or timestamp with additional conditions, if required, like “where” / ”group by” / “order by” . The “VERSIONS BETWEEN” will take you between ranges of time where a table was earlier. In this we can get multiple sets of versions or records of the table with expressions like MAX or MIN values of any columns.

There were two different types of flashback queries in Oracle shown below:

  • AS OF { SCN | TIMESTAMP } expr
  • VERSIONS BETWEEN { SCN | TIMESTAMP} { expr | MINVALUE } AND { expr | MAXVALUE }

There are a number of pseudocolumns that are used in flashback queries to identify the information of the same row having different versions:

  • ORA_ROWSCN: This pseudo column returns the system change number for the most recent change done on the row.
  • VERSIONS_STARTTIME  &  VERSIONS_ENDTIME: This type of pseudo column identifies first and last time version of the row based on timestamp. In other words we can say that it identifies the timestamp when the row version was created or modified on the database.
  • VERSIONS_STARTSCN &  VERSIONS_ENDSCN: This type of pseudo column identifies the first and last version of the row that was either created or modified based on system change number .
  • VERSIONS_XID:  This pseudo column identifies a raw number that created a row version during a transaction.
  • VERSIONS_OPERATION: This pseudo column will return the information on if the row was inserted, updated or deleted for a transaction. Transactions that were performed on database are identified by “I” for insertion, “D” for deletion, or “U” for update as an operation version.

Now let us see some examples on flashback queries. To demonstrate I am using my local database where we will create a table and insert few records to see how we can use flashback queries:


<em>Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0 </em>
<em>Connected as sloba</em>

<em>SQL> </em>
<em>SQL> create table link_details</em>
<em> 2 (</em>
<em> 3 id number ,</em>
<em> 4 name varchar2(500) ,</em>
<em> 5 site_url varchar2(4000)</em>
<em> 6 );</em>

<em>Table created</em>

<em>SQL> insert into link_details values(1, 'Oracle', 'https://www.microsoft.com') ;</em>

<em>1 row inserted</em>

<em>SQL> insert into link_details values(2, 'Microsoft','http://www.oracle.com/index.html');</em>

<em>1 row inserted</em>

<em>SQL> commit;</em>

<em>Commit complete</em>

<em>SQL></em>
<em>SQL> select * from link_details;</em>

<em>ID NAME SITE_URL</em>
<em>---------- ------------------------ --------------------------------------</em>
<em> 1 Oracle https://www.microsoft.com</em>
<em> 2 Microsoft http://www.oracle.com/index.html</em>

<em>SQL></em>

From the above you can we have only two records which we have inserted,now let us see if we can find what operation was done on this table by using the below SQL:


<em>SQL> </em>
<em>SQL> SELECT id</em>
<em> ,NAME</em>
<em> ,to_char(versions_starttime</em>
<em> ,'DD-MON-YYYY HH24:MI:SS') AS stime</em>
<em> ,to_char(versions_endtime</em>
<em> ,'DD-MON-YYYY HH24:MI:SS') "ETime"</em>
<em> ,versions_startscn "SSCN"</em>
<em> ,versions_endscn "ESCN"</em>
<em> ,versions_operation</em>
<em> ,decode(versions_operation</em>
<em> ,'I'</em>
<em> ,'Insert'</em>
<em> ,'U'</em>
<em> ,'Update'</em>
<em> ,'D'</em>
<em> ,'Delete'</em>
<em> ,'Error') "DML"</em>
<em> FROM link_details versions BETWEEN TIMESTAMP minvalue AND maxvalue</em>
<em> ORDER BY stime NULLS FIRST;</em>

<em>ID NAME STIME ETime SSCN ESCN VERSIONS_OPERATION DML</em>
<em>---------- ----------------------------------------------- --------------------- ---------- ------------------ ------</em>
<em> 1 Oracle 30-JUN-2015 19:18:58 767945 I Insert</em>
<em> 2 Microsoft 30-JUN-2015 19:18:58 767945 I Insert</em>

<em>SQL></em>

From the above we can see that the version “I” shows that there was an insert operation performed by one transaction.  The above query also provides the information on what was the start time this operation was performed, system change number and version.

Now let’s go ahead and insert another record into the same table.


<em>SQL> insert into link_details values(3, 'Experts-Exchange','http://www.experts-exchange.com');</em>

<em>1 row inserted</em>

<em>SQL> commit;</em>

<em>Commit complete</em>

We have now three records into the table.

<em>SQL> select * from link_details;</em>

<em>ID NAME SITE_URL</em>
<em>---------- --------------------- --------------------------------------</em>
<em> 1 Oracle https://www.microsoft.com</em>
<em> 2 Microsoft http://www.oracle.com/index.html</em>
<em> 3 Experts-Exchange http://www.experts-exchange.com</em>

So let’s try to check what was earlier in past before adding the third record.


<em>SQL> </em>
<em>SQL> SELECT * FROM link_details AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' MINUTE);</em>

<em>ID NAME SITE_URL</em>
<em>---------- -----------------------------------------------------------</em>
<em> 1 Oracle https://www.microsoft.com</em>
<em> 2 Microsoft http://www.oracle.com/index.html</em>
<em> 3 Experts-Exchange http://www.experts-exchange.com</em>

<em>SQL> </em>
<em>SQL> SELECT * FROM link_details</em>
<em> 2 AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '3' MINUTE);</em>

<em>ID NAME SITE_URL</em>
<em>---------- ------------------------------------------------------</em>
<em> 1 Oracle https://www.microsoft.com</em>
<em> 2 Microsoft http://www.oracle.com/index.html</em>

<em>SQL></em>

From the above two flashback queries we can see that the time difference provides the details on what records were present before certain period of time.

Now let us run one update statement against the same table and try to get the details what was the first value and what was changed in that row :


<em>SQL> update link_details set NAME = 'Oracle Corporation' where ID = 1;</em>

<em>1 row updated</em>

<em>SQL> commit;</em>

<em>Commit complete</em>

<em>SQL> select * from link_details;</em>

<em>ID NAME SITE_URL</em>
<em>---------- --------------------- ------------------------------------</em>
<em> 1 Oracle Corporation https://www.microsoft.com</em>
<em> 2 Microsoft http://www.oracle.com/index.html</em>
<em> 3 Experts-Exchange http://www.experts-exchange.com</em>

<em>SQL></em>

Now let us run the earlier SQL to identify the changes performed on the table:

SQL>
SQL> SELECT id
,NAME
,to_char(versions_starttime
,'DD-MON-YYYY HH24:MI:SS') AS stime
,to_char(versions_endtime
,'DD-MON-YYYY HH24:MI:SS') "ETime"
,versions_startscn "SSCN"
,versions_endscn "ESCN"
,versions_operation
,decode(versions_operation
,'I'
,'Insert'
,'U'
,'Update'
,'D'
,'Delete'
,'Error') "DML"
FROM link_details versions BETWEEN TIMESTAMP minvalue AND maxvalue
ORDER BY stime NULLS FIRST;

ID NAME STIME ETime SSCN ESCN VERSIONS_OPERATION DML
---------- ----------------------- ----------------------------- ----------------------------- ---------- ---------- ------------------ ------
1 Oracle 30-JUN-2015 19:57:50 768889 Error
2 Microsoft Error
3 Experts-Exchange 30-JUN-2015 19:51:20 768748 I Insert
1 Oracle Corporation 30-JUN-2015 19:57:50 768889 U Update

SQL>

Now we see that the above query provides the information like when the records were initially created as the Stime and version operation is NULL.

The next information we see that the insert operation was done for record having ID as “3”. For the final operation we can see that update done on ID 1 and by changing its name we can also see the difference on the SCN number that can also be used to retrieve the same information.

To have more details on flashback queries, reference can be taken from the below link:
http://docs.oracle.com/cd/E11882_01/appdev.112/e41502/adfns_flashback.htm#ADFNS01003

Advertisements

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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s