Finding time for Specific SCN

 

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
Advertisements

3 responses to this post.

  1. Nice article regarding SCN check and monitoring. You should need to mention oracle version also.

    Reply

  2. Posted by arun on September 6, 2012 at 8:45 pm

    really helpfull scn_to_timestamp

    Reply

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: