Please see my other blog for Oracle EBusiness Suite Posts - EBMentors

Search This Blog

Note: All the posts are based on practical approach avoiding lengthy theory. All have been tested on some development servers. Please don’t test any post on production servers until you are sure.

Saturday, December 11, 2010

SCN

System Change Number (SCN) This is a sequential counter, identifying precisely a moment in the database.
This is the most accurate way to identify a moment in time.
SQL> SELECT CURRENT_SCN FROM V$DATABASE; -- to get the current change number.

CURRENT_SCN
-----------
  300121782

OR
SQL> SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
               300121802

ORA_ROWSCN: a pseudocolumn of any table that is not fixed or external. It represents the SCN of the most recent change to a given row, that is, the latest COMMIT operation for the row. 

SQL> SELECT ora_rowscn, ename, sal FROM scott.emp
  2     WHERE empno = 7788;
ORA_ROWSCN ENAME   SAL
---------- ---------- -------
 299751149 SCOTT          3000   --- The latest COMMIT operation for the row took place at approximately 299751149

You can use function SCN_TO_TIMESTAMP to convert an SCN, like ORA_ROWSCN, to the corresponding TIMESTAMP value. 

SQL> SELECT ora_rowscn, ename, sal,SCN_TO_TIMESTAMP(ora_rowscn)
  2     FROM scott.emp
  3     WHERE empno = 7788;

ORA_ROWSCN ENAME             SAL SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------- ---------- ---------- ------------------------------------
 299751149 SCOTT            3000 05-DEC-10 01.19.43.000000000 PM

 SQL> select TIMESTAMP_TO_SCN(to_date('12/5/2010 1:19:43 PM','mm/dd/yyyy HH:MI:SSPM')) SCN from  dual; -- timestamp to SCN, It may have a precision of +/- 3 seconds

       SCN
----------
 299751145



No comments: