Geek Answers Handbook

How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?

I have this as a result of a 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 to scn using the timestamp_to_scn function, I get the following error:

ORA-08180: snapshot not found based on the specified time ORA-06512: in line "SYS.TIMESTAMP_TO_SCN", line 1 08180. 00000 - "snapshot not found based on the specified time" * Reason: Failed to match the time with SCN from mapping tables. * Action: try to use more time.

And when I use scn_to_timestamp for ora_rowscn to convert this column to a timestamp, I get the following error:

ORA-08181: the specified number is not a valid system change number ORA-06512: in the line "SYS.SCN_TO_TIMESTAMP", line 1 08181. 00000 - "the specified number is not a valid system change number" * Reason: the scn provided was outside the valid scn . * Action: use valid scn.

What am I doing wrong?

+7
sql oracle datetime timestamp oracle-sqldeveloper
source share
2 answers

You are trying to look too far back. You can only convert from SCNs that are in the redo / flashback window supported by your system. As soon as the changes increase, the display is lost.

This is explained in the documentation :

The relationship between the SCN and the time stamp when generating the SCN is stored by the database for a limited period of time. This period is the maximum period of saving canceled auto-tuning if the database is in automatic cancellation mode and the storage time of all flashback archives in the database is at least 120 hours. The time when an association becomes obsolete expires only when the database is open. An error occurs if the SCN specified for the argument SCN_TO_TIMESTAMP is too old.

Keep in mind that they are part of the internal mechanism of Oracle and therefore have limited use for us; although they are useful for flashback requests, of course, again in one window.

+15
source share

Set UNDO_MANAGEMENT to AUTO and UNDO_RETENTION so that a value that spans the period of your longest request in time. Also install RETENTION GARANTEE to prevent overwriting UNDO.

For Oracle 10g, you cannot use flashback for more than 5 days. This is a hard-coded limit. There are no restrictions for Oracle 11g.

+2
source share

More articles:


All Articles