Shulou(Shulou.com)06/01 Report--

The content comes from the official website:

Http://docs.oracle.com/cd/E11882_01/server.112/e41084/functions161.htm#BABEHBCB

Its usage is very simple.

Scn_to_timestamp (number), where number refers to the SCN currently produced by the system

The NOTE section of the document mentions one thing that is worth noting:

The association between an SCN and a timestamp when the SCN is generated is remembered by the database for a limited period of time. This period is the maximum of the auto-tuned undo retention period, if the database runs in the Automatic Undo Management mode, and the retention times of all flashback archives in the database, but no less than 120 hours. The time for the association to become obsolete elapses only when the database is open. An error is returned if the SCN specified for the argument to SCN_TO_TIMESTAMP is too old.

It is said here that when SCN is generated, the database retains the relationship between SCN and TIMESTAMP for a limited period of time, which is determined by the maximum preservation time of undo. If the database runs the automatic management of UNDO, and the flash regression file is stored for not less than 120h, then the relationship will expire only when the database is open, and an error prompt that the parameter is too old will be returned when queried through the SCN_TO_TIMESTAMP function.

At the same time, an interesting use is mentioned in the document. Oracle provides a virtual column called ORA_ROWSCN. When you query some tables, specify the virtual field in the SCN_TO_NUMBER () parameter, and the result returned is the time when the rows in the table were last upgraded.

SQL > select scn_to_timestamp (ORA_ROWSCN) from tbilllog3;SCN_TO_TIMESTAMP (ORA_ROWSCN)-05-JAN-15 02.25.28.000000000 PM05-JAN-15 05.28.56.000000000 PM

Its main purpose is to flashback the query and time stamp the data back to a certain point in time. For an example, take a look at the introduction on the official website.

If you specify a future number of SCN, the following error occurs

SQL > select scn_to_timestamp (2095591) from dual;select scn_to_timestamp (2095591) from dual * ERROR at line 1:ORA-08181: specified number is not a valid system change numberORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.