Snippet Name: SCN_TO_TIMESTAMP
Description: This conversion function takes as an argument a number that evaluates to a system change number (SCN), and returns the approximate timestamp associated with that SCN.
The returned value is of TIMESTAMP datatype. This function is useful any time you want to know the timestamp associated with an SCN.
For example, it can be used in conjunction with the ORA_ROWSCN pseudocolumn to associate a timestamp with the most recent change to a row.
Also see:
» Add PSOUG Search to SQL Developer
» Converting Rows to Columns
» UNISTR
» TRANSLATE
» TO_YMINTERVAL
» TO_TIMESTAMP_TZ
» TO_TIMESTAMP
» TO_SINGLE_BYTE
» TO_NUMBER
» TO_NCLOB
» TO_NCHAR
» TO_MULTI_BYTE
» TO_LOB
» TO_DSINTERVAL
» TO_DATE
» TO_CLOB
» TO_CHAR
» TO_BINARY_FLOAT
» TO_BINARY_DOUBLE
» TIMESTAMP_TO_SCN
» ROWIDTONCHAR
» ROWIDTOCHAR
» REFTOHEX
» RAWTONHEX
» RAWTOHEX
» NUMTOYMINTERVAL
» NUMTODSINTERVAL
» HEXTORAW
» DECOMPOSE
» CONVERT
Comment: (none)
Language: PL/SQL
Highlight Mode: PLSQL
Last Modified: March 05th, 2009
|
SCN_TO_TIMESTAMP(<scn>);
-- The following example uses the ORA_ROWSCN pseudocolumn to
-- determine the system change number of the last update to
-- a row and uses SCN_TO_TIMESTAMP to convert that SCN to a
-- timestamp:
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM employees
WHERE employee_id = 188;
-- You could use such a query to convert a system change number
-- to a timestamp for use in an Oracle Flashback Query:
SELECT salary FROM employees WHERE employee_id = 188;
SALARY
----------
3800
UPDATE employees SET salary = salary*10 WHERE employee_id = 188;
COMMIT;
SELECT salary FROM employees WHERE employee_id = 188;
SALARY
----------
38000
SELECT SCN_TO_TIMESTAMP(ORA_ROWSCN) FROM employees
WHERE employee_id = 188;
SCN_TO_TIMESTAMP(ORA_ROWSCN)
---------------------------------------------------------------------------
28-AUG-03 01.58.01.000000000 PM
FLASHBACK TABLE employees TO TIMESTAMP
TO_TIMESTAMP('28-AUG-03 01.00.00.000000000 PM');
SELECT salary FROM employees WHERE employee_id = 188;
SALARY
----------
3800
|
