<

Flashback queries in Oracle

Published on
4,888 Points
1,888 Views
Last Modified:
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 … ]

Open in new window

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:  
Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0 
Connected as sloba

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

Table created

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

1 row inserted

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

1 row inserted

SQL> commit;

Commit complete

SQL>
SQL> select * from link_details;

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

SQL>

Open in new window


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:
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:18:58                         767945            I                  Insert
         2 Microsoft         30-JUN-2015 19:18:58                         767945            I                  Insert

SQL> 

Open in new window


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

1 row inserted

SQL> commit;

Commit complete

Open in new window


We have now three records into the table.
SQL> select * from link_details;

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

Open in new window


So let's try to check what was earlier in past before adding the third record. 
SQL> 
SQL> SELECT * FROM link_details AS OF TIMESTAMP(SYSTIMESTAMP - INTERVAL '1' MINUTE);

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

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

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

SQL> 

Open in new window

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 :
SQL> update link_details set NAME = 'Oracle Corporation' where ID = 1;

1 row updated

SQL> commit;

Commit complete

SQL> select * from link_details;

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

SQL> 

Open in new window


Now let us run the earlier SQL to indentify 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> 

Open in new window


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
0
Comment
Author:Swadhin Ray
0 Comments

Featured Post

Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Join & Write a Comment

Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month