How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?

I have this as a result of the query:

select cast(to_date(a.start_time,'mm/dd/yyyy hh:mi:ss pm') as timestamp) date_of_call,
ora_rowscn from calling_table a where rownum <= 10;

       DATE_OF_CALLING          ORA_ROWSCN

26-JUL-13 12.29.28.000000000 PM 8347567733892
26-JUL-13 12.29.35.000000000 PM 8347567733892
26-JUL-13 12.29.35.000000000 PM 8347567733892
26-JUL-13 12.29.38.000000000 PM 8347567733892
26-JUL-13 12.29.44.000000000 PM 8347567733892
26-JUL-13 12.29.47.000000000 PM 8347567733892
26-JUL-13 12.29.48.000000000 PM 8347567733892
26-JUL-13 12.29.48.000000000 PM 8347567733892
26-JUL-13 12.29.48.000000000 PM 8347567733892
26-JUL-13 12.29.56.000000000 PM 8347567733892

But when I try to convert this timestamp into scn using the function timestamp_to_scn, I am getting the following error:

ORA-08180: no snapshot found based on specified time ORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 1 08180. 00000 - "no snapshot found based on specified time" *Cause: Could not match the time to an SCN from the mapping table. *Action: try using a larger time.

And when I am using scn_to_timestamp on ora_rowscn to convert that column into a timestamp, I am getting the following error:

ORA-08181: specified number is not a valid system change number ORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 1 08181. 00000 - "specified number is not a valid system change number" *Cause: supplied scn was beyond the bounds of a valid scn. *Action: use a valid scn.

What is it that I am doing wrong?

You're trying to look too far back. You can only convert to and from SCNs that are in the redo/flashback window maintained by your system. Once changes age out then the mapping is lost.

This is explained in the documentation:

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.

Bear in mind these are part of Oracle's internal mechanism, and so are of limited use to us; though they are useful for flashback queries of course - again within the same window.

The SCN_TO_TIMESTAMP uses some internal algorithms to do the mapping between SCN and TIME when some event happened and it does the job with a good approximation. But there is a limit. You cannot go too far in the past if the UNDO data does not cover your period.

In that case there is a tricky way to create our own mapping when you hit a limit of undo data. It will not be as good as SCN_TO_TIMESTAMP but it will provide approximation depending on your data.

All you need to do is to find a table with constant inserts going on. I use the audit table sys.aud$. You can use your own but the table must have time filed indicating when the rows were inserted. And if you have SCN and DATE you can map SCN and DATE with another table.

If you will use sys.aud$ keep in mind that:

1. You may need your dba to grant access to it or to create a simple view with two fields ora_rowscn and ntimestamp#
2. the more activity is going on on the database the more accurate will be the mapping.Usually using sys.aud$ table I can map old data edit that happened a year ago with accuracy about 60-120 minutes
3. if audit is off then scn_time will not return any rows and you need to find another table for mapping.

The query uses sys.aud$. Replace the [YOU_TABLE] with the table where you need to find the date of insert or update

-- get scn to date interval [begin..end] mapping from audit table      
with scn_time as
 (
     select sc sc_start, 
            lead(sc) over(order by sc) sc_end,
            start_time,
            lead(end_time) over(order by sc) end_time_sc
     from 
      (
        select n.ora_rowscn sc, 
        min( cast(from_tz(ntimestamp#,'00:00') at local as date) ) start_time,
        max( cast(from_tz(ntimestamp#,'00:00') at local as date) ) end_time
            from sys.aud$ n
            -- if audit log is big you need to select only a part of the table
            -- to make query faster
            --where ntimestamp# > sysdate - 365
           group by  n.ora_rowscn 
      ) order by sc 
  )
-- map scn from you table to scn_mapping  
select *
  from (
          select t.ora_rowscn sc, t.*
          from [YOU_TABLE] t
       ) table_inspect
 inner join scn_time s
    on (table_inspect.sc between s.sc_start and s.sc_end)
 -- to filter out bit intervals    
 where (end_time_sc-start_time) < 1

I used the way to restore info when a row was inserted if it was inserted more than a year ago.

Set UNDO_MANAGEMENT to AUTO, and UNDO_RETENTION to value that will cover the period of your longest query back in time. Also set RETENTION GARANTEE to prevent UNDO from being overwritten.

For Oracle 10g you can not flashback longer than 5 days. This is a hard coded limit. For Oracle 11g there is no limitation.