如何在 Oracle 中使用 Timestamp_to_scn 和 Scn_to_timestamp?

2021-12-06 00:00:00 datetime timestamp sql oracle oracle-sqldeveloper

我有这个作为查询的结果:

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

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

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

ORA-08180: 没有找到基于指定时间的快照ORA-06512:在SYS.TIMESTAMP_TO_SCN"，第 1 行08180. 00000 - 未找到基于指定时间的快照"*原因:无法将时间与映射表中的 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_rowscn 上使用 scn_to_timestamp 将该列转换为时间戳时，我收到以下错误:

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: 指定的编号不是有效的系统更改编号ORA-06512:在SYS.SCN_TO_TIMESTAMP"，第 1 行08181. 00000 - 指定的号码不是有效的系统变更号码"*原因:提供的 scn 超出了有效 scn 的范围.*操作:使用有效的 scn.

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.

我做错了什么?

推荐答案

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

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:

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

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.

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

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.

相关文章