Browse All Articles > Flashback queries in Oracle
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:
There were two different types of flashback queries in Oracle shown below:
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:
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.
We have now three records into the table.
So let's try to check what was earlier in past before adding the third record.
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 :
Now let us run the earlier SQL to indentify the changes performed on the table:
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
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 … ]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 }
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>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> 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 completeWe 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.comSo 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> 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> 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> 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
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)