Conversion between oracle SCN and TIMESTAMP

SQL code
-- Obtain the current SCN
Copy codeThe Code is as follows:
Select dbms_flashback.get_system_change_number scn1,
Timestamp_to_scn (sysdate) scn2 from dual;

-- Converts SCN to a successful time.
Copy codeThe Code is as follows:
Select to_char (scn_to_timestamp (34607271), 'yyyy-mm-dd hh24: mi: ss') chr,
Timestamp_to_scn (scn_to_timestamp (34607271) dt
From dual;


As an enhancement to flashback, Oracle10g provides functions for converting SCN and timestamp.

First, you can use dbms_flashback.get_system_change_number to obtain the current SCN value of the system:

SQL> col scn for 9999999999999
SQL> select dbms_flashback.get_system_change_number scn from dual;
SCN
--------------
8908390522972

You can use the scn_to_timestamp function to convert SCN to a timestamp:


SQL & gt; select scn_to_timestamp (8908390522972) scn from dual;
SCN
---------------------------------------------------------------------------
05-JAN-07 10.56.30.000000000 AM

You can use timestamp_to_scn to convert the timestamp to SCN:

SQL> select timestamp_to_scn (scn_to_timestamp (8908390522972) scn from dual;
SCN
--------------
8908390522972

Through these two functions, Oracle finally establishes the relationship between SCN and time. Before Oracle10g, there is no way to convert the correspondence between SCN and time through the function, generally, you can use logmnr to analyze logs.

However, this type of conversion depends on data records in the database, and cannot be switched for long-standing SCN. See the following example:


SQL> select min (FIRST_CHANGE #) scn, max (FIRST_CHANGE #) scn from v $ archived_log;
SCN
------------------------------------
8907349093953 8908393582271

SQL>
Copy codeThe Code is as follows:
Select scn_to_timestamp (8907349093953) scn from dual;
Select scn_to_timestamp (8907349093953) scn from dual
*
ERROR at line 1:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS. SCN_TO_TIMESTAMP", line 1
ORA-06512: at line 1


SQL & gt; select scn_to_timestamp (8908393582271) scn from dual;

SCN
---------------------------------------------------------------------------
05-JAN-07 11.45.50.000000000 AM