SELECT CURRENT_SCN FROM V$DATABASE;
To get the current SCN
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL
1.1 ORA_ROWSCN
Is 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. For example:
SELECT ora_rowscn, last_name, salary FROM employees
WHERE employee_id = 7788;
ORA_ROWSCN NAME SALARY
———- —- ——
202553 Fudd 3000
1.2 SCN_TO_TIMESTAMP( nSCN NUMBER)
Converts a SCN to TIMESTAMP
It has a precision of +/- 3 seconds
SQL> select scn_to_timestamp(884871) as timestamp from dual;
TIMESTAMP
—————————————————————————
09/03/2007 14:52:02,000000000
1.3 TIMESTAMP_TO_SCN(dTimestamp)
Converts TIMESTAMP to SCN
It has a precision of +/- 3 seconds
SQL> select timestamp_to_scn(to_timestamp(’08/03/2007 14:24:54′,’DD/MM/YYYY HH24:MI:SS’)) as scn from dual;
SCN
———-
845396
1.4 Table: SMON_SCN_TIME
This table translate time to SCN approximately.
When you use time based flashback you get the data in a period between +- 5 mn.
DBMS_FLASHBACK.ENABLE_AT_TIME and AS OF TIMESTAMP maps to an SCN value. As the SCN-time is recorded every X minutes. The time you specify is rounded down by up to X minutes from database startup.
This situation could create the ORA-01466 unable to read data – table definition has changed. There is only track of times up to a maximum of 5 days (Database up time).
SELECT TO_CHAR(TIME_DP,’DDMONYYYY HH24:MI’) DATE_TIME,SCN
FROM SMON_SCN_TIME
Date_time SCN
04MAY2006 16:20 576601
04MAY2006 16:26 576799
04MAY2006 16:30 577003
04MAY2006 16:36 577393
04MAY2006 16:41 577585
04MAY2006 16:46 577790
LINK WIKI ORACLE
Mohib Alvi 