SQL 如何在 Oracle 中使用 Timestamp_to_scn 和 Scn_to_timestamp？

How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?

提问by MontyPython

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:

但是，当我尝试使用函数 timestamp_to_scn 将此时间戳转换为 scn 时，出现以下错误：

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.

ORA-08180：未找到基于指定时间的快照 ORA-06512：在“SYS.TIMESTAMP_TO_SCN”，第 1 行 08180。00000 - “未找到基于指定时间的快照” *原因：无法将时间与来自映射表。*行动：尝试使用更长的时间。

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_rowscn 上使用 scn_to_timestamp 将该列转换为时间戳时，出现以下错误：

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.

ORA-08181：指定的号码不是有效的系统变更号码 ORA-06512：在“SYS.SCN_TO_TIMESTAMP”，第 1 行 08181。00000 - “指定的号码不是有效的系统变更号码” *原因：提供的 scn 超出范围有效的 scn。*行动：使用有效的scn。

What is it that I am doing wrong?

我做错了什么？

回答by Alex Poole

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.

你试图向后看太远。您只能与系统维护的重做/闪回窗口中的 SCN 相互转换。一旦更改过期，映射就会丢失。

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_TIMESTAMPis too old.

生成 SCN 时 SCN 和时间戳之间的关联会被数据库记住一段有限的时间。该时间为自动调优的undo保留时间的最大值，如果数据库运行在Automatic Undo Management模式下，数据库中所有闪回存档的保留时间不小于120小时。仅当数据库打开时，关联过时的时间才会过去。如果为参数指定的 SCNSCN_TO_TIMESTAMP太旧，则会返回错误。

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.

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

回答by Dmitry.M

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.

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

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.

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

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.

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

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

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

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.

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

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

该查询使用 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

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

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

回答by xDBA

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.

将 UNDO_MANAGEMENT 设置为 AUTO，并将 UNDO_RETENTION 设置为将涵盖您的最长查询时间段的值。同时设置 RETENTION GARANTEE 以防止 UNDO 被覆盖。

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

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