问题描述
我有这个作为查询的结果:
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 timeORA-06512: at "SYS.TIMESTAMP_TO_SCN", line 108180. 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 numberORA-06512: at "SYS.SCN_TO_TIMESTAMP", line 108181. 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 太旧，则会返回错误.
请记住，这些是 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.
这篇关于Oracle中如何使用Timestamp_to_scn和Scn_to_timestamp?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！