Row-level Dependency Tracking ROWDEPENDENCIES Example

This is one in the list of my Example Series in this blog. Check this blog for more possible examples

Oracle 10g provided one of the best features I like using. This is the pseudo column ORA_ROWSCN. Using scn_to_timestamp with ORA_ROWSCN, you can find the timestamp your block or row was changed. But this does not come automatically without the usual changes and space (about 6 bytes per row).

By default, Oracle 10g tables are created with the clause NOROWDEPENDENCIES. This means that Block-level Dependency Tracking is used. You can create your table with ROWDEPENDENCIES thereby enabling Row-level Dependency Tracking.

SQL> CREATE TABLE TESTTAB
(COL1 NUMBER(5),
COL2 VARCHAR2(30),
COL3 DATE)
ROWDEPENDENCIES;
2 3 4 5
Table created.

SQL>

I will demonstrate with EMP table which has NOROWDEPENDENCIES

SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from emp;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.49.00.000000000 AM SMITH 7369 2000
02-MAY-08 11.49.00.000000000 AM ALLEN 7499 1600
02-MAY-08 11.49.00.000000000 AM WARD 7521 1250
02-MAY-08 11.49.00.000000000 AM JONES 7566 2975
02-MAY-08 11.49.00.000000000 AM MARTIN 7654 1250
02-MAY-08 11.49.00.000000000 AM BLAKE 7698 2850
02-MAY-08 11.49.00.000000000 AM CLARK 7782 2450
02-MAY-08 11.49.00.000000000 AM SCOTT 7788 3000
02-MAY-08 11.49.00.000000000 AM KING 7839 5000
02-MAY-08 11.49.00.000000000 AM TURNER 7844 1500
02-MAY-08 11.49.00.000000000 AM ADAMS 7876 1100

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.49.00.000000000 AM JAMES 7900 950
02-MAY-08 11.49.00.000000000 AM FORD 7902 3000
02-MAY-08 11.49.00.000000000 AM MILLER 7934 1300

14 rows selected.

SQL> update emp set sal=3000 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

As you can see from the result below, there was NO row level tracking

SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from emp;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.54.10.000000000 AM SMITH 7369 3000
02-MAY-08 11.54.10.000000000 AM ALLEN 7499 1600
02-MAY-08 11.54.10.000000000 AM WARD 7521 1250
02-MAY-08 11.54.10.000000000 AM JONES 7566 2975
02-MAY-08 11.54.10.000000000 AM MARTIN 7654 1250
02-MAY-08 11.54.10.000000000 AM BLAKE 7698 2850
02-MAY-08 11.54.10.000000000 AM CLARK 7782 2450
02-MAY-08 11.54.10.000000000 AM SCOTT 7788 3000
02-MAY-08 11.54.10.000000000 AM KING 7839 5000
02-MAY-08 11.54.10.000000000 AM TURNER 7844 1500
02-MAY-08 11.54.10.000000000 AM ADAMS 7876 1100

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.54.10.000000000 AM JAMES 7900 950
02-MAY-08 11.54.10.000000000 AM FORD 7902 3000
02-MAY-08 11.54.10.000000000 AM MILLER 7934 1300

14 rows selected.

I will now demonstrate with EMPDPD table which has ROWDEPENDENCIES

SQL> create table empdpd ROWDEPENDENCIES as select * from emp;

Table created.

SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from empdpd;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM SMITH 7369 3000
02-MAY-08 11.55.26.000000000 AM ALLEN 7499 1600
02-MAY-08 11.55.26.000000000 AM WARD 7521 1250
02-MAY-08 11.55.26.000000000 AM JONES 7566 2975
02-MAY-08 11.55.26.000000000 AM MARTIN 7654 1250
02-MAY-08 11.55.26.000000000 AM BLAKE 7698 2850
02-MAY-08 11.55.26.000000000 AM CLARK 7782 2450
02-MAY-08 11.55.26.000000000 AM SCOTT 7788 3000
02-MAY-08 11.55.26.000000000 AM KING 7839 5000
02-MAY-08 11.55.26.000000000 AM TURNER 7844 1500
02-MAY-08 11.55.26.000000000 AM ADAMS 7876 1100

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM JAMES 7900 950
02-MAY-08 11.55.26.000000000 AM FORD 7902 3000
02-MAY-08 11.55.26.000000000 AM MILLER 7934 1300

14 rows selected.

SQL> update empdpd set sal=4000 where empno=7369;

1 row updated.

SQL> commit;

Commit complete.

As you can see from the result below, there was Row level tracking


SQL> select scn_to_timestamp(ora_rowscn),ename,empno,sal from empdpd;

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.56.20.000000000 AM SMITH 7369 4000
02-MAY-08 11.55.26.000000000 AM ALLEN 7499 1600
02-MAY-08 11.55.26.000000000 AM WARD 7521 1250
02-MAY-08 11.55.26.000000000 AM JONES 7566 2975
02-MAY-08 11.55.26.000000000 AM MARTIN 7654 1250
02-MAY-08 11.55.26.000000000 AM BLAKE 7698 2850
02-MAY-08 11.55.26.000000000 AM CLARK 7782 2450
02-MAY-08 11.55.26.000000000 AM SCOTT 7788 3000
02-MAY-08 11.55.26.000000000 AM KING 7839 5000
02-MAY-08 11.55.26.000000000 AM TURNER 7844 1500
02-MAY-08 11.55.26.000000000 AM ADAMS 7876 1100

SCN_TO_TIMESTAMP(ORA_ROWSCN) ENAME EMPNO SAL
--------------------------------------------------------------------------- ---------- ---------- ----------
02-MAY-08 11.55.26.000000000 AM JAMES 7900 950
02-MAY-08 11.55.26.000000000 AM FORD 7902 3000
02-MAY-08 11.55.26.000000000 AM MILLER 7934 1300

14 rows selected.

SQL>select table_name,dependencies from user_tables;

TABLE_NAME DEPENDEN
------------------------------ --------
EMP DISABLED
DEPT DISABLED
BONUS DISABLED
SALGRADE DISABLED
DUMMY DISABLED
EMPDPD ENABLED
TESTTAB ENABLED
EXTERNAL_TEST1 DISABLED
TEST1 DISABLED
TEST2 DISABLED
EXTERNAL_TEST DISABLED

11 rows selected.

SQL>

Note: using ROWDEPENDENCIES will normally add about 6 bytes to each row. You need to read moer from Oracle Documentation for limitations/restrictions