如何在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: at"SYS."TIMESTAMP_TO_SCN"，第1行08180. 00000 - "没有根据指定时间找到快照"*原因:无法从映射表中匹配到SCN的时间。*动作:尝试使用更大的时间

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

ORA-08181:指定的号码不是有效的系统变更号码ORA-06512: at"SYS."SCN_TO_TIMESTAMP"，第1行08181. 00000 - "指定的号码不是有效的系统更改号码"*原因:提供的scn超出了有效的scn范围。*动作:使用一个有效的scn.

我做错了什么?

Answer 1

你想看得太远了。您只能在系统维护的重做/闪回窗口中转换或转换scn。一旦更改过期，则映射将丢失。

这在文档中有解释:

生成SCN时，SCN和时间戳之间的关联会被数据库记住一段有限的时间。当数据库运行在"自动撤销管理"模式下时，此时间为自动调优的undo保留时间的最大值，以及数据库中所有闪回档案的保留时间，但不小于120小时。只有当数据库处于打开状态时，关联才会过时。如果为SCN_TO_TIMESTAMP参数指定的SCN太旧，则返回错误。

请记住，这些是Oracle内部机制的一部分，因此对我们的使用有限;虽然它们对于倒叙查询当然是有用的——还是在同一个窗口内。

Answer 2

SCN_TO_TIMESTAMP使用一些内部算法在某些事件发生时在SCN和TIME之间进行映射，并且它以很好的近似完成了这项工作。但这是有限度的。如果UNDO数据没有涵盖你的时期，你就不能在过去走得太远。

在这种情况下，有一个棘手的方法来创建我们自己的映射，当你达到撤消数据的限制。它将不如SCN_TO_TIMESTAMP好，但它将根据您的数据提供近似。

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

如果你将使用sys。aud$请记住:

1. 您可能需要dba授予访问权限或创建一个简单的视图，其中包含两个字段ora_rowscn和ntimestamp#
2. 在数据库上进行的活动越多，映射就越准确。通常使用sys。我可以映射一年前发生的旧数据编辑，精度约为60-120分钟
3. 如果审计是关闭的，那么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设置为覆盖时间最长的查询周期的值。同时设置RETENTION guarantee以防止UNDO被覆盖。

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