Introduction to Oracle Flashback version query

Flashback Version Query, introduced by Oracle 10g, can see how records have changed over a period of time, relative to flashback query can see only a certain point of object state. Based on this history, DBAs can quickly determine at what point in time the data has gone wrong and then revert to the previous state.

First look at a pseudo-column ORA_ROWSCN. The so-called pseudo column, is a false, nonexistent data column, although the user created the table while not specified, but Oracle for maintenance and added some of the internal fields, these fields can be used as normal files.

The most familiar pseudo-column is ROWID, which is the equivalent of a pointer to the location recorded on the disk. ORA_ROWSCN is new to Oracle 10g and is considered to be the SCN when recording the last time it was modified. Flashback Version Query uses this pseudo column to track the history of changes in records.

As an example:

Sql> select * from A;

Id

----------

2

1

3

4

sql> INSERT into A values (5);

1 lines have been created.

Sql> select * from A;

Id

----------

2

1

3

4

5

Sql> commit;

Submit completed.

Sql> Select Ora_rowscn, id from A;

ORA_ROWSCN ID

----------             ----------

1098443 2

1098443 1

1098443 3

1098443 4

1098443 5

Get more historical information

Sql>select VERSIONS_XID,VERSIONS_STARTSCN,VERSIONS_ENDSCN,

DECODE (versions_operation, ' I ', ' Insert ', ' U ', ' Update ', ' D ', ' Delete ', ' Original ') "Operation", ID from A versions between SCN MinValue and MaxValue;

Or

Sql>select Xid,commit_scn,commit_timestamp,operation,undo_sql

From Flashback_transaction_query Q where Q.xid in (select Versions_xid from B versions between SCN 413946 and 413959);

Versions_xid VERSIONS_STARTSCN VERSIONS_ENDSCN operatio ID

---------------- ----------------- --------------- -------- ----------

05001a0054020000 1099482 Update 3

05001a0054020000 1099482 Delete 3

05001a0054020000 1099482 Delete 2

05001a0054020000 1099482 Delete 1

0400150005020000 1098443 Insert 5

Let's say next pseudo column, flashback Version Query Technology actually has many pseudo columns, but ORA_ROWSCN is the most important. It records the SCN when it was last modified, noting that the modification was submitted. If not committed, this pseudo column will not change.

ORA_ROWSCN The default is the block level, where all the records in a block are a ORA_ROWSCN, and any record within the block is modified, and the ORA_ROWSCN of all records in the database block changes at the same time. The query results in the example above are shown.

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/

However, we can use the keyword rowdependencies when we build the table, we can change this default behavior, after using this keyword, each record has its own ORA_ROWSCN.

Example:

Sql> CREATE TABLE B (ID number (2)) rowdependencies;

Table has been created.

sql> INSERT INTO B values (1);

1 lines have been created.

sql> INSERT INTO B values (2);

1 lines have been created.

sql> INSERT into B values (3);

1 lines have been created.

Sql> commit;

Submit completed.