How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?

by

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:

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.

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.

More Related Contents:

  1. SQL function PIVOT with 2 column
  2. How to load a large number of strings to match with oracle database?
  3. Calculating difference between two timestamps in Oracle in milliseconds
  4. How can I find which tables reference a given table in Oracle SQL Developer?
  5. Oracle SQL escape character (for a ‘&’)
  6. Generating dates between two dates
  7. How to extract week number in sql
  8. Oracle Convert TIMESTAMP with Timezone to DATE
  9. Exporting a CLOB to a text file using Oracle SQL Developer
  10. Splitting string into multiple rows in Oracle
  11. GROUP BY with MAX(DATE) [duplicate]
  12. Can a foreign key be NULL and/or duplicate?
  13. Stored Procedure with optional “WHERE” parameters
  14. subquery in FROM must have an alias
  15. SQL multiple columns in IN clause
  16. SQL (ORACLE): ORDER BY and LIMIT [duplicate]
  17. How can I select from list of values in Oracle
  18. Selecting the second row of a table using rownum
  19. Does Oracle store trailing zeroes for Number data type?
  20. Oracle Update Query using Join
  21. How to convert Rows to Columns in Oracle? [duplicate]
  22. SQL: getting the max value of one column and the corresponding other columns [duplicate]
  23. Oracle: Combine multiple results in a subquery into a single comma-separated value [duplicate]
  24. SQL Keep getting error with ON UPDATE CASCADE
  25. Oracle query to match all values in the list among all rows in table
  26. adding primary key to sql view
  27. How do I call an Oracle stored procedure from an Excel VBA script?
  28. What does a (+) sign mean in an Oracle SQL WHERE clause? [duplicate]
  29. Connecting to Oracle Database using Sql Server Integration Services
  30. Using bind variables with dynamic SELECT INTO clause in PL/SQL

Leave a Comment