Author: John Ferguson

Page: 1 2

Almost every database administrator has to deal with database changes every time. Most of the time these changes are usual but sometimes such problems may arise where you need to know the historical changes made to the database tables.

Flashback Query:

In Oracle releases prior to 10g unless one does auditing, it was not possible to view series of changes made to the table. However Oracle has made the life of database administrators easy whereby introducing the flashback query feature in oracle 9i, which gave a view of the table at very specific time in the past.

Flashback Versions Query:

Yet better Oracle introduced Flashback Versions Query in Oracle 10g that has added further advantage by enabling us to view all the changes made to a specific row between two time intervals. Flashback versions query enables us to analyze the historical changes made to the tables. Thus we are able to see all changes made to the row between specific time intervals. A new version of row gets created whenever commit happens.

VERSIONS BETWEEN Clause:

Flashback version query allow us to track the versions of a specific row during a specified time period using the VERSIONS BETWEEN clause. Flashback version query also include some pseudo-columns that describe each version of our data. The pseudo columns can be used to determine the actual operations and change times. The Pseudo columns are as under

Create Table:

First of all we will create a table.

CREATE TABLE MYTABLE
(
MYTABLE_ID NUMBER(9) NOT NULL,
MYTRANSACTION_DATE DATE,
MYTRANSACTION_AMT NUMBER(9,2) NOT NULL,
CONSTRAINT PK_MYTABLE PRIMARY KEY(MYTABLE_ID)
)
/

Add Data:

Now we will populate some data into this table so that we can test our flashback query.

INSERT INTO MYTABLE(MYTABLE_id,MYTRANSACTION_date,MYTRANSACTION_amt) VALUES(101,sysdate,5000);
COMMIT;

UPDATE MYTABLE SET MYTRANSACTION_AMT = MYTRANSACTION_AMT + 2000 WHERE MYTABLE_ID = 101;
COMMIT;

UPDATE MYTABLE SET MYTRANSACTION_AMT = MYTRANSACTION_AMT - 5000 WHERE MYTABLE_ID = 101;
COMMIT;

UPDATE MYTABLE SET MYTRANSACTION_AMT = MYTRANSACTION_AMT + 200 WHERE MYTABLE_ID = 101;
COMMIT;

View Resultset:

Now we will view the result set by running below commands.

SELECT versions_Startscn, versions_endscn,versions_xid,
CASE
WHEN versions_operation = ‘I' THEN ‘INSERT'
WHEN versions_operation = ‘U' THEN ‘UPDATE'
WHEN versions_operation = ‘D' THEN ‘DELETE'
END AS Operation,
MYTRANSACTION_amt
FROM MYTABLE
VERSIONS BETWEEN TIMESTAMP minvalue AND maxvalue
ORDER BY versions_Startscn;

We get the output as follows.

VERSIONS_STARTSCN VERSIONS_ENDSCN   VERSIONS_XID   OPERAT MYTRANSACTION_AMT
————————————————— ——————————————— ———————————————  —————— ———————————————
    4168035             4168037   07001500F6070000 INSERT       5000
    4168037             4168039   09002C00420A0000 UPDATE       7000
    4168039             4168042   04002600C8070000 UPDATE       2000
    4168042                       0800030075090000 UPDATE       2200

Instead of using versions_Startscn, versions_endscn you can also use the versions_starttime and versions_endtime. Similarly you can use interval in the same way as it is used in regular flash back query instead of using minvalue and maxvalue. Continued...

Page: 1 2

 More Database Articles
   Database Security: Step by step guideline
   Best Setting for Oracle Multiblock Read Count!!
   Data Warehousing – Do or Don’t?
   Oracle Replication, Some Concerns
   Data Warehouse Testing: The key to Data Warehouse success