Oracle® Database Application Developer's Guide - Fundamentals 10g Release 2 (10.2) Part Number B14251-01 |
|
|
View PDF |
This chapter discusses the following flashback topics:
Database Administration Tasks Before Using Flashback Features
See Also:
Oracle Database Backup and Recovery Advanced User's Guide and Oracle Database Administrator's Guide for information on flashback features designed for database administration tasks such as Oracle Flashback Database and Oracle Flashback Table
Oracle Database SQL Reference for the syntax of SQL extensions for flashback features
Oracle Database has a group of features, known collectively as flashback, that provide ways to view past states of database objects or to return database objects to a previous state without using point-in-time media recovery.
You can use flashback features of the database to do the following:
Perform queries that return past data.
Perform queries that return metadata that shows a detailed history of changes to the database.
Recover tables or rows to a previous point in time.
Flashback features use the Automatic Undo Management system to obtain metadata and historical data for transactions. They rely on undo data, which are records of the effects of individual transactions. For example, if a user executes an UPDATE
statement to change a salary from 1000 to 1100, then Oracle would store the value 1000 in the undo data.
Undo data is persistent and survives a database shutdown. By using flashback features, you can employ undo data to query past data or recover from logical corruptions. Besides using it in flashback operations, Oracle Database uses undo data to perform the following actions:
Roll back active transactions
Recover terminated transactions by using database or process recovery
Provide read consistency for SQL queries
See Also:
Oracle Database Concepts for more information about flashback features and automatic undo managementIn application development, you can use flashback features to report on historical data or undo erroneous changes. Flashback features include the following:
Oracle Flashback Query
You can use this feature to retrieve data for a time in the past that you specify using the AS OF
clause of the SELECT
statement.
Oracle Flashback Version Query
You can use this feature to retrieve metadata and historical data for a specific time interval. For example, you can view all the rows of a table that ever existed during a given time interval. Metadata about the different versions of rows includes start and end time, type of change operation, and identity of the transaction that created the row version. You use the VERSIONS BETWEEN
clause of the SELECT
statement to create a Flashback Version Query.
Oracle Flashback Transaction Query
You can use this feature to retrieve metadata and historical data for a given transaction or for all transactions within a given time interval. You can also obtain the SQL code to undo the changes to particular rows affected by a transaction. Typically, you use Flashback Transaction Query in conjunction with a Flashback Version Query that provides the transaction IDs for the rows of interest. To perform a Flashback Transaction Query, select from the FLASHBACK_TRANSACTION_QUERY
view.
DBMS_FLASHBACK
package
You can use this feature to set the internal Oracle clock to a time in the past so that you can examine data current at that time.
You can use the following features for application development or interactively as a database user or administrator:
DBMS_FLASHBACK
package
Flashback Query
Flashback Version Query
Flashback Transaction Query
Typically, you use the following flashback features only in database administration:
Oracle Flashback Table
You can use this feature to recover a table to its state at a previous point in time. You can restore table data while the database is on line, undoing changes to only the specified table.
Oracle Flashback Drop
You can use this feature to recover a dropped table. This reverses the effects of a DROP
TABLE
statement.
Oracle Flashback Database
You can use this feature to quickly return the database to an earlier point in time, by undoing all of the changes that have taken place since then. This is fast, because you do not have to restore database backups.
Flashback Database, Flashback Table, and Flashback Drop are primarily data recovery mechanisms and are therefore documented elsewhere. The other flashback features, while valuable in data recovery scenarios, are useful for application development. They are the focus of this chapter.
See Also:
Oracle Database Administrator's Guide to learn about the Flashback Drop feature
Oracle Database Administrator's Guide to learn about the Flashback Table feature
Oracle Database Administrator's Guide to learn about Automatic Undo Management
Before you can use flashback features in your application, you must perform the following administrative tasks to configure your database. Consult with your database administrator to perform these tasks:
Create an undo tablespace with enough space to keep the required data for flashback operations. The more often users update the data, the more space is required. Calculating the space requirements is usually performed by a database administrator. You can find the calculation formula in the Oracle Database Administrator's Guide.
Enable Automatic Undo Management, as explained in Oracle Database Administrator's Guide. In particular, you must set the following database initialization parameters:
UNDO_MANAGEMENT
UNDO_TABLESPACE
Note that for an undo tablespace with a fixed size, Oracle Database automatically performs the following actions:
Tunes the system to give the best possible undo retention for the undo tablespace.
For an automatically extensible undo tablespace, Oracle Database retains undo data longer than the longest query duration as well as the low threshold of undo retention specified by the UNDO_RETENTION
parameter.
Note:
You can queryV$UNDOSTAT.TUNED_UNDORETENTION
to determine the amount of time for which undo is retained for the current undo tablespace.Specify the RETENTION GUARANTEE
clause for the undo tablespace to ensure that unexpired undo is not discarded. Setting UNDO_RETENTION
is not, by itself, a strict guarantee. If the system is under space pressure, then Oracle can overwrite unexpired undo with freshly generated undo. Specifying RETENTION GUARANTEE
prevents this behavior.
Grant flashback privileges to users, roles, or applications that need to use flashback features as follows:
For the DBMS_FLASHBACK
package, grant the EXECUTE
privilege on DBMS_FLASHBACK
to provide access to the features in this package.
For Flashback Query and Flashback Version Query, grant FLASHBACK
and SELECT
privileges on specific objects to be accessed during queries or grant the FLASHBACK ANY TABLE
privilege to allow queries on all tables.
For Flashback Transaction Query, grant the SELECT ANY TRANSACTION
privilege.
For Execution of undo SQL code, grant SELECT
, UPDATE
, DELETE
, and INSERT
privileges for specific tables, as appropriate, to permit execution of undo SQL code retrieved by a Flashback Transaction Query.
To use the Flashback Transaction Query feature in Oracle Database 10g, the database must be running with version 10.0 compatibility, and must have supplemental logging turned on with the following SQL statement:
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
To enable flashback operations on specific LOB
columns of a table, use the ALTER TABLE
command with the RETENTION
option. Because undo data for LOB
columns can be voluminous, you must define which LOB
columns to use with flashback operations.
See Also:
Oracle Database Backup and Recovery Advanced User's Guide and Oracle Database Administrator's Guide to learn about DBA tasks such as setting up automatic undo management and granting privileges
Oracle Database Application Developer's Guide - Large Objects to learn about LOB storage and the RETENTION
parameter
You perform a Flashback Query by using a SELECT
statement with an AS OF
clause. You use a Flashback Query to retrieve data as it existed at some time in the past. The query explicitly references a past time by means of a timestamp or SCN. It returns committed data that was current at that point in time.
Potential uses of Flashback Query include:
Recovering lost data or undoing incorrect, committed changes. For example, if you mistakenly delete or update rows, and then commit them, you can immediately undo the mistake.
Comparing current data with the corresponding data at some time in the past. For example, you might run a daily report that shows the change in data from yesterday. You can compare individual rows of table data or find intersections or unions of sets of rows.
Checking the state of transactional data at a particular time. For example, you could verify the account balance of a certain day.
Simplifying application design, by removing the need to store some kinds of temporal data. By using a Flashback Query, you can retrieve past data directly from the database.
Applying packaged applications such as report generation tools to past data.
Providing self-service error correction for an application, thereby enabling users to undo and correct their errors.
This example uses a Flashback Query to examine the state of a table at a previous time. Suppose that a DBA discovers at 12:30 PM that the row for employee Chung had been deleted from the employees
table. The DBA also knows that at 9:30AM the data for Chung was correctly stored in the database. The DBA can use a Flashback Query to examine the contents of the table at 9:30 to find out what data had been lost. If appropriate, the DBA can then re-insert the lost data.
Example 10-1 retrieves the state of the record for Chung
at 9:30AM, April 4, 2004:
Example 10-1 Retrieving a Row with Flashback Query
SELECT * FROM employees AS OF TIMESTAMP TO_TIMESTAMP('2004-04-04 09:30:00', 'YYYY-MM-DD HH:MI:SS') WHERE last_name = 'Chung';
The update in Example 10-2 restores Chung's information to the employees
table:
Keep the following in mind when using a Flashback Query (SELECT
... AS OF
):
You can specify or omit the AS OF
clause for each table and specify different times for different tables. Use an AS OF
clause in a query to perform DDL operations (such as creating and truncating tables) or DML operations (such as inserting and deleting) in the same session as the query.
To use the results of a Flashback Query in a DDL or DML statement that affects the current state of the database, use an AS OF
clause inside an INSERT
or CREATE TABLE AS SELECT
statement.
When choosing whether to use a timestamp or an SCN in Flashback Query, remember that Oracle Database uses SCNs internally and maps these to timestamps at a granularity of 3 seconds. If a possible 3-second error (maximum) is important to a Flashback Query in your application, then use an SCN instead of a timestamp. Refer to "Flashback Tips – General".
You can create a view that refers to past data by using the AS OF
clause in the SELECT
statement that defines the view. If you specify a relative time by subtracting from the current time on the database host, then the past time is recalculated for each query. For example:
CREATE VIEW hour_ago AS SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE); -- SYSTIMESTAMP refers to the time zone of the database host environment
You can use the AS OF
clause in self-joins, or in set operations such as INTERSECT
and MINUS
, to extract or compare data from two different times. You can store the results by preceding a Flashback Query with a CREATE TABLE AS SELECT
or INSERT INTO TABLE SELECT
statement. For example, the following query reinserts into table employees
the rows that existed an hour ago:
INSERT INTO employees (SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE)) -- SYSTIMESTAMP refers to the time zone of the database host environment MINUS SELECT * FROM employees);
In general, the DBMS_FLASHBACK
package provides the same functionality as Flashback Query, but Flashback Query is sometimes more convenient.
The DBMS_FLASHBACK
package acts as a time machine: you can turn back the clock, carry out normal queries as if you were at that time in the past, and then return to the present. Because you can use the DBMS_FLASHBACK
package to perform queries on past data without special clauses such as AS OF
or VERSIONS BETWEEN
, you can reuse existing PL/SQL code to query the database at times in the past.
You must have the EXECUTE
privilege on the DBMS_FLASHBACK
package.
To use the DBMS_FLASHBACK
package in your PL/SQL code:
Call DBMS_FLASHBACK.ENABLE_AT_TIME
or DBMS_FLASHBACK.ENABLE_AT_SYSTEM_CHANGE_NUMBER
to turn back the clock to a specified time in the past. Afterwards all queries retrieve data that was current at the specified time.
Perform normal queries, that is, without any special flashback-feature syntax such as AS OF
. The database is automatically queried at the specified past time. Perform only queries; do not try to perform DDL or DML operations.
Call DBMS_FLASHBACK.DISABLE
to return to the present. You must call DISABLE
before calling ENABLE
again for a different time. You cannot nest ENABLE
/DISABLE
pairs.
You can use a cursor to store the results of queries. To do this, open the cursor before calling DBMS_FLASHBACK.DISABLE
. After storing the results and then calling DISABLE
, you can do the following:
Perform INSERT
or UPDATE
operations to modify the current database state by using the stored results from the past.
Compare current data with the past data. After calling DISABLE
, open a second cursor. Fetch from the first cursor to retrieve past data; fetch from the second cursor to retrieve current data. You can store the past data in a temporary table, and then use set operators such as MINUS
or UNION
to contrast or combine the past and current data.
You can call DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
at any time to obtain the current System Change Number (SCN). Note that the current SCN is always returned; this takes no account of previous calls to DBMS_FLASHBACK.ENABLE*
.
See Also:
Oracle Database PL/SQL Packages and Types Reference for details about the DBMS_FLASHBACK
package
Oracle Database Reference and Oracle Database Backup and Recovery Reference for information about SCNs
ORA_ROWSCN
is a pseudocolumn of any table that is not fixed or external. It represents the SCN of the most recent change to a given row, that is, the latest COMMIT
operation for the row. For example:
SELECT ora_rowscn, last_name, salary FROM employees WHERE employee_id = 7788; ORA_ROWSCN NAME SALARY ---------- ---- ------ 202553 Fudd 3000
The latest COMMIT
operation for the row took place at approximately SCN 202553
. You can use function SCN_TO_TIMESTAMP
to convert an SCN, like ORA_ROWSCN
, to the corresponding TIMESTAMP
value.
ORA_SCN
is in fact a conservative upper bound of the latest commit time: the actual commit SCN can be somewhat earlier. ORA_SCN
is more precise (closer to the actual commit SCN) for a row-dependent table (created using CREATE TABLE
with the ROWDEPENDENCIES
clause).
Noteworthy uses of ORA_ROWSCN
in application development include concurrency control and client cache invalidation. To see how you might use it in concurrency control, consider the following scenario.
Your application examines a row of data, and records the corresponding ORA_ROWSCN
as 202553
. Later, the application needs to update the row, but only if its record of the data is still accurate. That is, this particular update operation depends, logically, on the row not having been changed. The operation is therefore made conditional on the ORA_ROWSCN
being still 202553
. Here is an equivalent interactive command:
UPDATE employees SET salary = salary + 100 WHERE employee_id = 7788 AND ora_rowscn = 202553; 0 rows updated.
The conditional update fails in this case, because the ORA_ROWSCN
is no longer 202553
. This means that some user or another application changed the row and performed a COMMIT
more recently than the recorded ORA_ROWSCN
.
Your application queries again to obtain the new row data and ORA_ROWSCN
. Suppose that the ORA_ROWSCN
is now 415639
. The application tries the conditional update again, using the new ORA_ROWSCN
. This time, the update succeeds, and it is committed. Here is an interactive equivalent:
SQL> UPDATE employees SET salary = salary + 100 WHERE empno = 7788 AND ora_rowscn = 415639; 1 row updated. SQL> COMMIT; Commit complete. SQL> SELECT ora_rowscn, name, salary FROM employees WHERE empno = 7788; ORA_ROWSCN NAME SALARY ---------- ---- ------ 465461 Fudd 3100
The SCN corresponding to the new COMMIT
is 465461
.
Besides using ORA_ROWSCN
in an UPDATE
statement WHERE
clause, you can use it in a DELETE
statement WHERE
clause or the AS OF
clause of a Flashback Query.
See Also:
You use a Flashback Version Query to retrieve the different versions of specific rows that existed during a given time interval. A new row version is created whenever a COMMIT
statement is executed.
You specify a Flashback Version Query using the VERSIONS BETWEEN
clause of the SELECT
statement. Here is the syntax:
VERSIONS {BETWEEN {SCN | TIMESTAMP} start AND end}
where start
and end
are expressions representing the start and end of the time interval to be queried, respectively. The interval is closed at both ends: the upper and lower limits specified (start
and end
) are both included in the time interval.
The Flashback Version Query returns a table with a row for each version of the row that existed at any time during the time interval you specify. Each row in the table includes pseudocolumns of metadata about the row version, described in Table 10-1. This information can reveal when and how a particular change (perhaps erroneous) occurred to your database.
Table 10-1 Flashback Version Query Row Data Pseudocolumns
Pseudocolumn Name | Description |
---|---|
Starting System Change Number (SCN) or If this is |
|
SCN or If this is |
|
Identifier of the transaction that created the row version. |
|
Operation performed by the transaction: Note: For user updates of an index key, a Flashback Version Query may treat an |
A given row version is valid starting at its time VERSIONS_START*
up to, but not including, its time VERSIONS_END*
. That is, it is valid for any time t such that VERSIONS_START*
<= t < VERSIONS_END*
. For example, the following output indicates that the salary was 10243 from September 9, 2002, included, to November 25, 2003, not included.
VERSIONS_START_TIME VERSIONS_END_TIME SALARY ------------------- ----------------- ------ 09-SEP-2003 25-NOV-2003 10243
Here is a typical Flashback Version Query:
SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, name, salary FROM employees VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS') WHERE name = 'JOE';
Pseudocolumn VERSIONS_XID
provides a unique identifier for the transaction that put the data in that state. You can use this value in connection with a Flashback Transaction Query to locate metadata about this transaction in the FLASHBACK_TRANSACTION_QUERY
view, including the SQL required to undo the row change and the user responsible for the change – see "Using Flashback Transaction Query".
See Also:
Oracle Database SQL Reference for information on the Flashback Version Query pseudocolumns and the syntax of theVERSIONS
clauseA Flashback Transaction Query is a query on the view FLASHBACK_TRANSACTION_QUERY
. You use a Flashback Transaction Query to obtain transaction information, including SQL code that you can use to undo each of the changes made by the transaction.
See Also:
Oracle Database Backup and Recovery Advanced User's Guide. and Oracle Database Administrator's Guide for information on how a DBA can use the Flashback Table feature to restore an entire table, rather than individual rowsAs an example, the following statement queries the FLASHBACK_TRANSACTION_QUERY
view for transaction information, including the transaction ID, the operation, the operation start and end SCNs, the user responsible for the operation, and the SQL code to undo the operation:
SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('000200030000002D');
As another example, the following query uses a Flashback Version Query as a subquery to associate each row version with the LOGON_USER
responsible for the row data change.
SELECT xid, logon_user FROM flashback_transaction_query WHERE xid IN (SELECT versions_xid FROM employees VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2003-07-18 14:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2003-07-18 17:00:00', 'YYYY-MM-DD HH24:MI:SS'));
This example demonstrates the use of a Flashback Transaction Query in conjunction with a Flashback Version Query. The example assumes simple variations of the employees
and departments
tables in the sample hr
schema.
In this example, a DBA carries out the following series of actions in SQL*Plus:
connect hr/hr CREATE TABLE emp (empno NUMBER PRIMARY KEY, empname VARCHAR2(16), salary NUMBER); INSERT INTO emp VALUES (111, 'Mike', 555); COMMIT; CREATE TABLE dept (deptno NUMBER, deptname VARCHAR2(32)); INSERT INTO dept VALUES (10, 'Accounting'); COMMIT;
At this point, emp
and dept
have one row each. In terms of row versions, each table has one version of one row. Next, suppose that an erroneous transaction deletes employee id 111
from table emp
:
UPDATE emp SET salary = salary + 100 WHERE empno = 111; INSERT INTO dept VALUES (20, 'Finance'); DELETE FROM emp WHERE empno = 111; COMMIT;
Subsequently, a new transaction reinserts employee id 111
with a new employee name into the emp
table.
INSERT INTO emp VALUES (111, 'Tom', 777); UPDATE emp SET salary = salary + 100 WHERE empno = 111; UPDATE emp SET salary = salary + 50 WHERE empno = 111; COMMIT;
At this point, the DBA detects the application error and needs to diagnose the problem. The DBA issues the following query to retrieve versions of the rows in the emp
table that correspond to empno 111
. The query uses Flashback Version Query pseudocolumns.
connect dba_name/password
SELECT versions_xid XID, versions_startscn START_SCN,
versions_endscn END_SCN, versions_operation OPERATION,
empname, salary FROM hr.emp
VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE
where empno = 111;
XID START_SCN END_SCN OPERATION EMPNAME SALARY
---------------- ---------- --------- ---------- ---------- ----------
0004000700000058 113855 I Tom 927
000200030000002D 113564 D Mike 555
000200030000002E 112670 113564 I Mike 555
3 rows selected
The results table reads chronologically, from bottom to top. The third row corresponds to the version of the row in emp
that was originally inserted in the table when the table was created. The second row corresponds to the row in emp
that was deleted by the erroneous transaction. The first row corresponds to the version of the row in emp
that was reinserted with a new employee name.
The DBA identifies transaction 000200030000002D
as the erroneous transaction and issues the following Flashback Transaction Query to audit all changes made by this transaction:
SELECT xid, start_scn START, commit_scn COMMIT, operation OP, logon_user USER, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('000200030000002D'); XID START COMMIT OP USER UNDO_SQL ---------------- ----- ------ -- ---- --------------------------- 000200030000002D 195243 195244 DELETE HR insert into "HR"."EMP" ("EMPNO","EMPNAME","SALARY") values ('111','Mike','655'); 000200030000002D 195243 195244 INSERT HR delete from "HR"."DEPT" where ROWID = 'AAAKD4AABAAAJ3BAAB'; 000200030000002D 195243 195244 UPDATE HR update "HR"."EMP" set "SALARY" = '555' where ROWID = 'AAAKD2AABAAAJ29AAA'; 000200030000002D 195243 113565 BEGIN HR 4 rows selected
The rightmost column (undo_sql
) contains the SQL code that will undo the corresponding change operation. The DBA can execute this code to undo the changes made by that transaction. The USER
column (logon_user
) shows the user responsible for the transaction.
A DBA might also be interested in knowing all changes made in a certain time window. In our scenario, the DBA performs the following query to view the details of all transactions that executed since the erroneous transaction identified earlier (including the erroneous transaction itself):
SELECT xid, start_scn, commit_scn, operation, table_name, table_owner FROM flashback_transaction_query WHERE table_owner = 'HR' AND start_timestamp >= TO_TIMESTAMP ('2002-04-16 11:00:00','YYYY-MM-DD HH:MI:SS'); XID START_SCN COMMIT_SCN OPERATION TABLE_NAME TABLE_OWNER ---------------- --------- ---------- --------- ---------- ----------- 0004000700000058 195245 195246 UPDATE EMP HR 0004000700000058 195245 195246 UPDATE EMP HR 0004000700000058 195245 195246 INSERT EMP HR 000200030000002D 195243 195244 DELETE EMP HR 000200030000002D 195243 195244 INSERT DEPT HR 000200030000002D 195243 195244 UPDATE EMP HR 6 rows selected
The following tips and restrictions apply to using flashback features.
For better performance, generate statistics on all tables involved in a Flashback Query by using the DBMS_STATS
package, and keep the statistics current. Flashback Query always uses the cost-based optimizer, which relies on these statistics.
The performance of a query into the past depends on how much undo data must be accessed. For better performance, use queries to select small sets of past data using indexes, not to scan entire tables. If you must do a full table scan, consider adding a parallel hint to the query.
The performance cost in I/O is the cost of paging in data and undo blocks that are not already in the buffer cache. The performance cost in CPU use is the cost of applying undo information to affected data blocks. When operating on changes in the recent past, flashback features essentially CPU bound.
Use index structures for Flashback Version Query: the database keeps undo data for index changes as well as data changes. Performance of index lookup-based Flashback Version Query is an order of magnitude faster than the full table scans that are otherwise needed.
In a Flashback Transaction Query, the type of the xid
column is RAW(8)
. To take advantage of the index built on the xid
column, use the HEXTORAW
conversion function: HEXTORAW(xid)
.
Flashback Query against a materialized view does not take advantage of query rewrite optimizations.
See Also:
Oracle Database Performance Tuning GuideUse the DBMS_FLASHBACK
package or other flashback features? Use ENABLE
/DISABLE
calls to the DBMS_FLASHBACK
package around SQL code that you do not control, or when you want to use the same past time for several consecutive queries. Use Flashback Query, Flashback Version Query, or Flashback Transaction Query for SQL that you write, for convenience. A Flashback Query, for example, is flexible enough to do comparisons and store results in a single query.
To obtain an SCN to use later with a flashback feature, use DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
.
You can compute or retrieve a past time to use in a query by using a function return value as a timestamp or SCN argument. For example, you can perform date and time calculations by adding or subtracting an INTERVAL
value to the value of the SYSTIMESTAMP
function.
You can query locally or remotely (Flashback Query, Flashback Version Query, or Flashback Transaction Query). for example here is a remote Flashback Query:
(SELECT * FROM employees@some_remote_host AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '60' MINUTE);
To ensure database consistency, always perform a COMMIT
or ROLLBACK
operation before querying past data.
Remember that all flashback processing is done using the current session settings, such as national language and character set, not the settings that were in effect at the time being queried.
Some DDLs that alter the structure of a table, such as drop/modify column, move table, drop partition, and truncate table/partition, invalidate any existing undo data for the table. It is not possible to retrieve data from a point earlier than the time such DDLs were executed. Trying such a query results in error ORA-1466. This restriction does not apply to DDL operations that alter the storage attributes of a table, such as PCTFREE
, INITRANS
, and MAXTRANS
.
Use an SCN to query past data at a precise time. If you use a timestamp, the actual time queried might be up to 3 seconds earlier than the time you specify. Internally, Oracle Database uses SCNs; these are mapped to timestamps at a granularity of every 3 seconds.
For example, assume that the SCN values 1000 and 1005 are mapped to the times 8:41 and 8:46 AM respectively. A query for a time between 8:41:00 and 8:45:59 AM is mapped to SCN 1000; a Flashback Query for 8:46 AM is mapped to SCN 1005.
Due to this time-to-SCN mapping, if you specify a time that is slightly after a DDL operation (such as a table creation) the database might actually use an SCN that is just before the DDL operation. This can result in error ORA-1466.
You cannot retrieve past data from a V$
view in the data dictionary. Performing a query on such a view always returns the current data. You can, however, perform queries on past data in other views of the data dictionary, such as USER_TABLES
.