Relationship between SCN and TimeStamp in Oracle 10 GB

The following articles mainly introduce the contents of SCN and TimeStamp in Oracle 10g. We all know that in Oracle 10g, the function is used to convert the SCN and timestamp to improve the flash back operation. This article uses an example to analyze the function:

Example:

In step 1, we 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  
 
 

Then, the scn_to_timestamp function can be used to convert the SCN to the timestamp:

 
 

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

Here, 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 the above two functions, Oracle can establish the relationship between SCN and time. In versions earlier than 10 Gb of Oracle, the correspondence between SCN and time cannot be obtained through Function Conversion, generally, logs can only be obtained through logmnr analysis.

Note: This type of conversion depends on data records in the database, and cannot be converted for persistent SCN. For example:

 
 

  
  
SQL> select min(FIRST_CHANGE#) scn,max(FIRST_CHANGE#) scn from v$archived_log;  
  
  
SCN SCN  
  
  
8907349093953 8908393582271  
  
  
SQL> 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> select scn_to_timestamp(8908393582271) scn from dual;  
  
  
SCN   
 
 

The above content is the description of SCN and TimeStamp in Oracle 10g, hoping to help you in this aspect.