Capture changes with a new Oracle 10 Gb row Timestamp

Data Warehouses are often filled with multiple data source systems, and each system runs its own applications. Therefore, in order to provide only new data to the data warehouse, it is very complicated to determine which rows are updated recently. This is especially difficult when dealing with outdated software that does not include the Last Update Time column in its architecture. Naturally, enterprises are reluctant to add columns simply by modifying the work code to track changes.

In Oracle 10 Gb, each row has a new pseudo column called ORA_ROWSCN when the row is last updated. ORA_ROWSCN provides a "conservative upper boundary conservative upper bound)" System Change number to record the transaction of the last modified row. This means that the number of system changes SCN) is an estimate, because the number of system changes in Oracle can only be tracked at the module level by default.

For example, in list A, ORA_ROWSCN is selected when processing A small table. After A row is updated, ORA_ROWSCN is selected again.

List

SQL> SQL> SELECT ora_rowscn, ename, sal 2 FROM emp; ORA_ROWSCN ENAME SAL ---------- ---------- ---------- 529426 SMITH 800 529426 ALLEN 1600 529426 WARD 1250 529426 JONES 2975 529426 MARTIN 1250 529426 BLAKE 2850 529426 CLARK 2450 529426 SCOTT 3000 529426 KING 5000 529426 TURNER 1500 529426 ADAMS 1100 ORA_ROWSCN ENAMESAL ---------- ---------- ---------- 529426 JAMES 950 529426 FORD 3000 529426 MILLER 1300 14 rows selected. SQL> UPDATE emp 2 SETsal = 6000 3 WHERE ename = 'KING'; 1 row updated. SQL> commit; Commit complete. SQL> SELECT ora_rowscn, ename, sal 2 FROM emp; ORA_ROWSCN ENAME SAL ---------- ---------- ---------- 653331 SMITH 800 653331 ALLEN 1600 653331 WARD 1250 653331 JONES 2975 653331 MARTIN 1250 653331 BLAKE 2850 653331 CLARK 2450 653331 SCOTT 3000 653331 KING 6000 653331 TURNER 1500 653331 ADAMS 1100 ORA_ROWSCN ENAME SAL ---------- ---------- ---------- 653331 JAMES 950 653331 FORD 3000 653331 MILLER 1300 14 rows selected. SQL> SELECT SCN_TO_TIMESTAMP(653331) FROM dual; SCN_TO_TIMESTAMP(653331) --------------------------------------------------- 20-JUN-06 11.03.59.000000000 PM

Even if only one row is changed, the remaining one will display a new number of system changes SCN ). More accurately, you can use the row-level system to change the number tracking when a table is created for the first time. Unfortunately, you cannot change the table so that you can add this feature later .) Therefore, if the number of updated blocks is less than the number of blocks in the table, this can also be a method for discovering changes without involving too many additional rows.

What if you need a transaction-related date and time? The SCN_TO_TIMESTAMP function can convert ORA_ROWSCN to a timestamp. You can use it to query or use it as the predicate of a where clause. However, this timestamp is still an estimate.

ORA_ROWSCN is also a convenient method to use the system change value SCN) as a shortcut for flash back query, although RA_ROWSCN itself cannot be selected in the flash back), or you need to use the flash back to query, select the VERSIONS_STARTSCN and VERSIONS_ENDSCN pseudo columns.

List B is a flashback query. It uses a system change value smaller than the current value to obtain the original value of a Data row. We found that King's salary returned to 5000, while ORA_ROWSCN's value was the initial system change value.

List B

SQL> SELECT ora_rowscn, ename, sal 2 FROMemp AS OF SCN (653330) 3 WHERE ename = 'KING'; ORA_ROWSCN ENAME SAL ---------- ---------- ---------- 529426 KING 5000

(