如何在Oracle中使用Timestamp_to_scn和Scn_to_timestamp？

Question

作为查询的结果，我得到以下结果：

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

但是，当我尝试使用函数timestamp_to_scn将这个时间戳转换为scn时，我得到了以下错误：

ORA- 08180 :未找到基于指定时间的快照ORA-06512:在"SYS.TIMESTAMP_TO_SCN"，第1行08180。00000 -“基于指定时间找不到快照”*原因:无法将时间与映射表中的SCN匹配。*操作:尝试使用更大的时间。

当我在ora_rowscn上使用scn_to_timestamp将该列转换为时间戳时，我得到了以下错误：

ORA- 08181 :指定的编号不是有效的系统更改编号ORA-06512:位于"SYS.SCN_TO_TIMESTAMP“的第1行08181。00000 -“指定的编号不是有效的系统更改编号”*原因:提供的scn超出了有效scn的范围。*操作:使用有效的scn。

我到底做错了什么？

Answer 1

你试着往前看太远了。您只能对系统维护的重做/闪回窗口中的SCN进行转换。一旦更改过期，映射就会丢失。

in the documentation解释了这一点

生成SCN时，数据库会在有限的时间内记住SCN与时间戳之间的关联。如果数据库在自动还原管理模式下运行，则此时段是自动优化还原保留期的最大值，以及数据库中所有闪回归档的保留时间，但不小于120小时。只有当数据库打开时，关联才会过时。如果为SCN_TO_TIMESTAMP的参数指定的SCN太旧，则会返回错误。

请记住，这些是Oracle内部机制的一部分，因此对我们的使用有限；当然，它们对于闪回查询很有用-同样是在同一个窗口中。

Answer 2

当某个事件发生时，SCN_TO_TIMESTAMP使用一些内部算法来完成SCN和时间之间的映射，并且它以很好的近似值完成了这项工作。但这是有限制的。如果撤消数据不涵盖您的时间段，则您不能在过去走得太远。

在这种情况下，当您遇到撤消数据的限制时，有一种创建我们自己的映射的微妙方法。它不会像SCN_TO_TIMESTAMP那样好，但它将根据您的数据提供近似值。

你所需要做的就是找到一个常量插入的表。我使用sys.aud$的审核表。您可以使用自己的表，但是表必须有时间字段来指示何时插入行。如果您有SCN和DATE，您可以将SCN和DATE映射到另一个表。

如果您要使用sys.aud$，请记住：

1. 您可能需要您的数据库管理员授予对它的访问权限，或者创建一个包含两个字段ora_rowscn和ora_rowscn的简单视图。数据库上进行的活动越多，mapping.Usually就越准确使用sys.aud$表我可以映射一年前发生的旧数据编辑，精确度约为60-120分钟
2. 如果关闭审核，则scn_time不会返回任何行，您需要查找另一个表进行映射。

查询使用sys.aud$。将YOU_TABLE替换为需要查找插入或更新日期的表

-- 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

如果行是一年前插入的，我使用的方法是在插入行时恢复信息。

Answer 3

将UNDO_MANAGEMENT设置为AUTO，并将UNDO_RETENTION设置为覆盖最长查询时间段的值。还要设置保留保证，以防止撤消被覆盖。

对于Oracle 10g，闪回时间不能超过5天。这是一个硬编码的限制。对于Oracle 11g，没有限制。