Description of Oracle ORA_ROWSCN pseudo Columns

1. Description of the pseudo column on the official website

From: 11gR2

Http://download.Oracle.com/docs/cd/E11882_01/server.112/e17118/pseudocolumns007.htm#SQLRF50953

Foreach row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row in the current session. thispseudo docolumn is useful for determining approxiely matwhen a row was lastupdated.

Itis not absolutely precise, because Oracle tracks SCNs by transaction committedfor the block in which the row resides. you can obtain a more fine-grainedapproximation of the SCN by creating your tables with row-level dependencytracking. refer to create table... NOROWDEPENDENCIES | ROWDEPENDENCIES for more information on row-level dependency tracking.

 

ROWDEPENDENCIESSpecifyROWDEPENDENCIES if you want to enable row-level dependency tracking. This setting is useful primarily to allow for parallel propagation inreplication environments. It increases the size of each row by 6 bytes.

NOROWDEPENDENCIESSpecifyNOROWDEPENDENCIES if you do not want table to use the row-leveldependency tracking feature. This is the default.

 

That is to say, when norowdependencies is used as the default table creation parameter, ora_rawscn is taken from the SCN of the data block header. At this time, for the row in the same block, their ora_rowscn is the same.

When rowdependencies is specified during table creation,An ora_rowscn will be saved for each row. In this way, there will be multiple ora_rowscn values for the row in the same block. With the dump block, you can find that each row has an additional dscn information, which is used to save ora_rowscn.

For example:

Tab 0, row 1, @ 0 × 1f88
Tl: 12 fb:-H-FL-lb: 0 × 2 cc: 1
Dscn 0 × 0000.00000000 

Tab 0, row 1, @ 0 × 1f88
Tl: 12 fb:-H-FL-lb: 0x0 cc: 1
Dscn 0x0005. 105a6pc3 

This is the dump information of the same row. The first dscn is 0. The information is obtained from the itl Scn/Fsc,When cleanout occurs in itl, the Scn/Fsc will be flushed to dscn, which is the second information above.. This part will be proved by experiment later.

For details about block dump and itl, refer:

Oracle datafile block format description http://www.bkjia.com/Linux/2011-08/40286.htm

Effece ITL (InterestedTransaction List) Description

You cannot use this pseudo column in a query to a view. however, you can use it to refer to the underlying table when creating a view. you can also use this pseudo column in the WHERE clause of an UPDATE or DELETEstatement.

-- The ora_rowscn pseudo Column cannot be used in the view. 

ORA_ROWSCN is not supported for Flashback Query. instead, use the version query pseudo docolumns, which are provided explicitly forFlashback Query. refer to the SELECT... flashback_query_clausefor information on Flashback Query and "VersionQuery pseudo docolumns" for additional information on thosepseudo docolumns.

-- Ora_rowscn does not support Flashbackquery. 

Restriction on ORA_ROWSCN: This pseudo column is notsupported for external tables.

-- Ora_rowscn does not support external tables 

Example:

Thefirst statement below uses the ORA_ROWSCN pseudo docolumn to get the system changenumber of the last operation on the employees table.

SELECT ORA_ROWSCN, last_name

FROM employees

WHERE employee_id = 188;

-- Use ORA_ROWSCN to obtain the SCN of the last update of the row

 

The second statement uses the pseudo columnwith the SCN_TO_TIMESTAMP function to determine the timestamp of the operation:

SELECTSCN_TO_TIMESTAMP (ORA_ROWSCN), last_name

FROM employees

WHERE employee_id = 188;

-- Use SCN_TO_TIMESTAMP and ORA_ROWSCN to obtain the last row modification time.

 

Ii. Description of ORA_ROWSCN

The ORA_ROWSCN pseudo column is introduced by Oracle10g. You can query the SCN of the last change recorded in the table. This new pseudo-column is useful in some environments, such as Optimistic Locking or incremental data extraction. However, by default, the ORA_ROWSCN record in each row is Block-based, unless the row-level tracking (createtable... Rowdependencies ).

 

2.1 optimistic locks and ORA_ROWSCN

When select... for update is required for pessimistic locking, you can change it to Optimistic Locking by using ORA_ROWSCN. Check ORA_ROWSCN at the beginning of select data. If you want to write the data back to the database after modification, check the latest ORA_ROWSCN to see whether the data has changed during this period.

 

2.2 incremental data extraction and ORA_ROWSCN

The largest ORA_ROWSCN record after each extraction. The next extraction is based on the last SCN to obtain the latest modified data. Before 10 Gb, many systems need to extract incremental data by parsing logs, adding triggers, or adding a time cut field to the table.

ORA_ROWSCN is actually the third method, but this field is maintained by Oracle, which can avoid problems caused by some applications updating other fields by bypassing the time cut. However, if the system uses logical standby database or streams and other logical replication solutions, and data extraction is based on the Logical standby database, ORA_ROWSCN may affect the data analysis after extraction, because the obtained time is the time for recording changes on the logical standby database, rather than the time of the source database.

Of course, if we only extract data and do not need to use this time for analysis, it is still not a problem. However, we still need to consider redo if the logical standby database fails, then how to deal with this incremental extraction problem.

1 2 3 Next Page