Oracle 12c – When did my row change?

By William Sescu

My good old DEPT table always had 4 rows with 4 different departments. However, I have noticed that a new row was inserted into the DEPT table and the row 50 popped up, and I would like to know when it happened?

Before:

SQL> select  DEPTNO, DNAME, LOC from DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

After:

SQL> select  DEPTNO, DNAME, LOC from DEPT;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 IT             L.A.

Unfortunately, I have no auditing in place, and supplemental logging is also not activated. So, auditing is not an option and LogMiner also not. I’m kind of running out of options to get the timestamp when the department 50 was inserted.

My last resort is the ORA_ROWSCN pseudocolumn. Due to the Oracle documentation, for each row, ORA_ROWSCN returns the conservative upper bound system change number (SCN) of the most recent change to the row. This pseudocolumn becomes useful, in case we want to determine approximately when a row was last updated.

ORA_ROWSCN is a pseudocolumn of any table that is not fixed or external, and be careful, it is not 100% precise, because Oracle tracks SCNs by transaction committed for the block in which the row resides. However, it is better then nothing for my use case.

SQL> select ora_rowscn, DEPTNO, DNAME, LOC from DEPT;

ORA_ROWSCN     DEPTNO DNAME          LOC
---------- ---------- -------------- -------------
   9708226         10 ACCOUNTING     NEW YORK
   9708226         20 RESEARCH       DALLAS
   9708226         30 SALES          CHICAGO
   9708226         40 OPERATIONS     BOSTON
   9708226         50 IT             L.A.

To convert now the SCN to a Timestamp, we can use the SCN_TO_TIMESTAMP function. It converts the SCN to a Timestamp with a precision of +/- 3 seconds.

SQL> select scn_to_timestamp(9708226) as timestamp from dual;

TIMESTAMP
---------------------------------------------------------------------------
27-OCT-16 11.40.07.000000000 AM

Due to the ORA_ROWSCN pseudocolumn, now I know at least, that the block where the department 50 was inserted was changed at the “27-OCT-16 11.40.07”.

If you want it more accurate, you need a feature called Row Level Dependency Tracking. This feature is activated by the keyword ROWDEPENDENCIES during the CREATE TABLE.

CREATE TABLE "SCOTT"."DEPT_RLDT" 
   (	"DEPTNO" NUMBER(2,0), 
	"DNAME" VARCHAR2(14 BYTE), 
	"LOC" VARCHAR2(13 BYTE), 
	 CONSTRAINT "PK_DEPT_RLDT" PRIMARY KEY ("DEPTNO")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ROWDEPENDENCIES ;

With this feature, each row in the table has a system change number (SCN) that represents a time greater than or equal to the commit time of the last transaction that modified the row.

You cannot change this setting after the table is created. Meaning, there is no “alter table … ROWDEPENDENCIES”.

This setting is useful primarily to allow for parallel propagation in replication environments, however, it can also be used to find out quickly the time, when a row was change.

Like always in life, nothing comes for free. The drawback is, that it increases the size of each row by 6 bytes. That’s why the default is NOROWDEPENDENCIES.

But once this feature is enable, like in the following example for my DEPT_RLDT table, you will get
a different SCN number for the new commited row (department 50).

SQL> select ora_rowscn, DEPTNO, DNAME, LOC from DEPT_RLDT;

ORA_ROWSCN     DEPTNO DNAME          LOC
---------- ---------- -------------- -------------
   9701315         10 ACCOUNTING     NEW YORK
   9701315         20 RESEARCH       DALLAS
   9701315         30 SALES          CHICAGO
   9701315         40 OPERATIONS     BOSTON
   9708244         50 IT             L.A.

SQL>

SCN 9701315 for deptno 10,20,30,40 and 9708244 for deptno 50. That’s cool.

SQL> select scn_to_timestamp(9708244) as timestamp from dual;

TIMESTAMP
---------------------------------------------------------------------------
27-OCT-16 11.40.24.000000000 AM

Due to the SCN to a Timestamp precision of +/- 3 seconds, it is still not 100%, but very close to it. Another important point is that the ORA_ROWSCN represents an upper bound SCN. We just know it has not changed after this SCN but may have been changed earlier.

So … does the ORA_ROWSCN replace auditing? No, not at all. But it can help to answer questions like the following: Please tell me when ROW xyz has been modified the last time and recover the table (using Flashback techniques) to that point.
Cheers,
William