如何在Oracle中使用Timestamp_to_scn和Scn_to_timestamp? [英] How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?

问题描述

由于查询，我得到了这个

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.

在文档中对此进行了解释:

数据库在有限的时间内记住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.

这篇关于如何在Oracle中使用Timestamp_to_scn和Scn_to_timestamp?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！