Oracle中如何使用Timestamp_to_scn和Scn_to_timestamp?

2022-01-13 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 时的时间戳之间的关联会被数据库记住一段有限的时间.如果数据库在自动撤消管理模式下运行，此期限是自动调整的撤消保留期的最大值，以及数据库中所有闪回存档的保留时间，但不少于 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.

相关文章