8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Flashback New Features and Enhancements in Oracle Database 10g
Oracle9i introduced the DBMS_FLASHBACK
package to allow queries to reference older versions of the database. Oracle 10g has taken this technology a step further making it simpler to use and much more flexible.
Internally Oracle uses SCNs to track changes so any flashback operation that uses a timestamp must be translated into the nearest SCN which can result in a 3 second error.
- Flashback Query
- Flashback Version Query
- Flashback Transaction Query
- Flashback Table
- Flashback Drop (Recycle Bin)
- Flashback Database
- Flashback Query Functions
Related articles.
- Flashback Query
- Flashback Version Query
- Flashback Table
- Flashback Query
- Flashback and LogMiner Enhancements in Oracle Database 11g Release 1
- Flashback Data Archive (FDA) Enhancements in Oracle Database 12c Release 1 (12.1)
Flashback Query
You can read more about this feature here.
Flashback Query allows the contents of a table to be queried with reference to a specific point in time, using the AS OF
clause. Essentially it is the same as the DBMS_FLASHBACK
functionality or Oracle 9i, but in a more convenient form. For example.
CREATE TABLE flashback_query_test ( id NUMBER(10) ); SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database; CURRENT_SCN TO_CHAR(SYSTIMESTAM ----------- ------------------- 722452 2004-03-29 13:34:12 INSERT INTO flashback_query_test (id) VALUES (1); COMMIT; SELECT COUNT(*) FROM flashback_query_test; COUNT(*) ---------- 1 SELECT COUNT(*) FROM flashback_query_test AS OF TIMESTAMP TO_TIMESTAMP('2004-03-29 13:34:12', 'YYYY-MM-DD HH24:MI:SS'); COUNT(*) ---------- 0 SELECT COUNT(*) FROM flashback_query_test AS OF SCN 722452; COUNT(*) ---------- 0
Flashback Version Query
You can read more about this feature here.
Flashback version query allows the versions of a specific row to be tracked during a specified time period using the VERSIONS BETWEEN
clause.
CREATE TABLE flashback_version_query_test ( id NUMBER(10), description VARCHAR2(50) ); INSERT INTO flashback_version_query_test (id, description) VALUES (1, 'ONE'); COMMIT; SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database; CURRENT_SCN TO_CHAR(SYSTIMESTAM ----------- ------------------- 725202 2004-03-29 14:59:08 UPDATE flashback_version_query_test SET description = 'TWO' WHERE id = 1; COMMIT; UPDATE flashback_version_query_test SET description = 'THREE' WHERE id = 1; COMMIT; SELECT current_scn, TO_CHAR(SYSTIMESTAMP, 'YYYY-MM-DD HH24:MI:SS') FROM v$database; CURRENT_SCN TO_CHAR(SYSTIMESTAM ----------- ------------------- 725219 2004-03-29 14:59:36 COLUMN versions_startscn FORMAT 99999999999999999 COLUMN versions_starttime FORMAT A24 COLUMN versions_endscn FORMAT 99999999999999999 COLUMN versions_endtime FORMAT A24 COLUMN versions_xid FORMAT A16 COLUMN versions_operation FORMAT A1 COLUMN description FORMAT A11 SET LINESIZE 200 SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, description FROM flashback_version_query_test VERSIONS BETWEEN TIMESTAMP TO_TIMESTAMP('2004-03-29 14:59:08', 'YYYY-MM-DD HH24:MI:SS') AND TO_TIMESTAMP('2004-03-29 14:59:36', 'YYYY-MM-DD HH24:MI:SS') WHERE id = 1; VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION ------------------ ------------------------ ------------------ ------------------------ ---------------- - ----------- 725212 29-MAR-04 02.59.16 PM 02001C0043030000 U THREE 725209 29-MAR-04 02.59.16 PM 725212 29-MAR-04 02.59.16 PM 0600030021000000 U TWO 725209 29-MAR-04 02.59.16 PM ONE SELECT versions_startscn, versions_starttime, versions_endscn, versions_endtime, versions_xid, versions_operation, description FROM flashback_version_query_test VERSIONS BETWEEN SCN 725202 AND 725219 WHERE id = 1; VERSIONS_STARTSCN VERSIONS_STARTTIME VERSIONS_ENDSCN VERSIONS_ENDTIME VERSIONS_XID V DESCRIPTION ------------------ ------------------------ ------------------ ------------------------ ---------------- - ----------- 725212 29-MAR-04 02.59.16 PM 02001C0043030000 U THREE 725209 29-MAR-04 02.59.16 PM 725212 29-MAR-04 02.59.16 PM 0600030021000000 U TWO 725209 29-MAR-04 02.59.16 PM ONE
The available pseudocolumn meanings are:
VERSIONS_STARTSCN
orVERSIONS_STARTTIME
- Starting SCN and TIMESTAMP when row took on this value. The value of NULL is returned if the row was created before the lower bound SCN or TIMESTAMP.VERSIONS_ENDSCN
orVERSIONS_ENDTIME
- Ending SCN and TIMESTAMP when row last contained this value. The value of NULL is returned if the value of the row is still current at the upper bound SCN or TIMESTAMP.VERSIONS_XID
- ID of the transaction that created the row in it's current state.VERSIONS_OPERATION
- Operation performed by the transaction ((I)nsert, (U)pdate or (D)elete)
The boundaries of the version query can also be defined using the MINVALUE
and MAXVALUE
keywords.
Flashback Transaction Query
You can read more about this feature here.
Flashback transaction query can be used to get extra information about the transactions listed by flashback version queries. The VERSIONS_XID
column values from a flashback version query can be used to query the FLASHBACK_TRANSACTION_QUERY
view.
SELECT xid, operation, start_scn,commit_scn, logon_user, undo_sql FROM flashback_transaction_query WHERE xid = HEXTORAW('0600030021000000'); XID OPERATION START_SCN COMMIT_SCN ---------------- -------------------------------- ---------- ---------- LOGON_USER ------------------------------ UNDO_SQL ---------------------------------------------------------------------------------------------------- 0600030021000000 UPDATE 725208 725209 SCOTT update "SCOTT"."FLASHBACK_VERSION_QUERY_TEST" set "DESCRIPTION" = 'ONE' where ROWID = 'AAAMP9AAEAAAA AYAAA'; 0600030021000000 BEGIN 725208 725209 SCOTT XID OPERATION START_SCN COMMIT_SCN ---------------- -------------------------------- ---------- ---------- LOGON_USER ------------------------------ UNDO_SQL ---------------------------------------------------------------------------------------------------- 2 rows selected.
Flashback Table
You can read more about this feature here.
The FLASHBACK TABLE
command allows point in time recovery of individual tables subject to the following requirements.
- You must have either the
FLASHBACK ANY TABLE
system privilege or haveFLASHBACK
object privilege on the table. - You must have SELECT, INSERT, DELETE, and ALTER privileges on the table.
- There must be enough information in the undo tablespace to complete the operation.
- Row movement must be enabled on the table (
ALTER TABLE tablename ENABLE ROW MOVEMENT;
).
The following example creates a table, inserts some data and flashbacks to a point prior to the data insertion. Finally it flashbacks to the time after the data insertion.
CREATE TABLE flashback_table_test ( id NUMBER(10) ); ALTER TABLE flashback_table_test ENABLE ROW MOVEMENT; SELECT current_scn FROM v$database; CURRENT_SCN ----------- 715315 INSERT INTO flashback_table_test (id) VALUES (1); COMMIT; SELECT current_scn FROM v$database; CURRENT_SCN ----------- 715340 FLASHBACK TABLE flashback_table_test TO SCN 715315; SELECT COUNT(*) FROM flashback_table_test; COUNT(*) ---------- 0 FLASHBACK TABLE flashback_table_test TO SCN 715340; SELECT COUNT(*) FROM flashback_table_test; COUNT(*) ---------- 1
Flashback of tables can also be performed using timestamps.
FLASHBACK TABLE flashback_table_test TO TIMESTAMP TO_TIMESTAMP('2004-03-03 10:00:00', 'YYYY-MM-DD HH:MI:SS');
Flashback Drop (Recycle Bin)
You can read more about this feature here.
In Oracle 10g the default action of a DROP TABLE
command is to move the table to the recycle bin (or rename it), rather than actually dropping it. The DROP TABLE ... PURGE
option can be used to permanently drop a table.
DROP TABLE my_table PURGE;
The recycle bin is a logical collection of previously dropped objects, with access tied to the DROP
privilege. This feature does not use flashback logs or undo, so it is independent of the other flashback technologies. The contents of the recycle bin can be shown using the SHOW RECYCLEBIN
command and purged using the PURGE TABLE
command. As a result, a previously dropped table can be recovered from the recycle bin.
CREATE TABLE flashback_drop_test ( id NUMBER(10) ); INSERT INTO flashback_drop_test (id) VALUES (1); COMMIT; DROP TABLE flashback_drop_test; SHOW RECYCLEBIN ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- FLASHBACK_DROP_T BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE 2004-03-29:11:09:07 EST FLASHBACK TABLE flashback_drop_test TO BEFORE DROP; SELECT * FROM flashback_drop_test; ID ---------- 1
Tables in the recycle bin can be queried like any other table.
DROP TABLE flashback_drop_test; SHOW RECYCLEBIN ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- FLASHBACK_DROP_T BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0 TABLE 2004-03-29:11:18:39 EST SELECT * FROM "BIN$TDGqmJZKR8u+Hrc6PGD8kw==$0"; ID ---------- 1
If an object is dropped and recreated multiple times all dropped versions will be kept in the recycle bin, subject to space. Where multiple versions are present it's best to reference the tables via the RECYCLEBIN_NAME
. For any references to the ORIGINAL_NAME
it is assumed the most recent object is drop version in the referenced question. During the flashback operation the table can be renamed.
FLASHBACK TABLE flashback_drop_test TO BEFORE DROP RENAME TO flashback_drop_test_old;
Several purge options exist.
PURGE TABLE tablename; -- Specific table. PURGE INDEX indexname; -- Specific index. PURGE TABLESPACE ts_name; -- All tables in a specific tablespace. PURGE TABLESPACE ts_name USER username; -- All tables in a specific tablespace for a specific user. PURGE RECYCLEBIN; -- The current users entire recycle bin. PURGE DBA_RECYCLEBIN; -- The whole recycle bin.
Several restrictions apply relating to the recycle bin.
- Only available for non-system, locally managed tablespaces.
- There is no fixed size for the recycle bin. The time an object remains in the recycle bin can vary.
- The objects in the recycle bin are restricted to query operations only (no DDL or DML).
- Flashback query operations must reference the recycle bin name.
- Tables and all dependent objects are placed into, recovered and purged from the recycle bin at the same time.
- Tables with Fine Grained Access policies aer not protected by the recycle bin.
- Partitioned index-organized tables are not protected by the recycle bin.
- The recycle bin does not preserve referential integrity.
This feature can be disabled/enabled at the session or system level.
-- Session level. ALTER SESSION SET recyclebin = OFF; ALTER SESSION SET recyclebin = ON; -- System level. ALTER SYSTEM SET recyclebin = OFF; ALTER SYSTEM SET recyclebin = ON;
The contents of the recyclebin can also be displayed using the DBA_RECYCLEBIN
view.
Flashback Database
You can read more about this feature here.
The FLASHBACK DATABASE
command is a fast alternative to performing an incomplete recovery. In order to flashback the database you must have SYSDBA
privilege and the flash recovery area must have been prepared in advance.
The FLASHBACK_ON
column of the V$DATABASE
view shows the current status of flashback database.
If the database is in NOARCHIVELOG
it must be switched to ARCHIVELOG
mode.
CONN / AS SYSDBA ALTER SYSTEM SET log_archive_dest_1='location=d:\oracle\oradata\DB10G\archive\' SCOPE=SPFILE; ALTER SYSTEM SET log_archive_format='ARC%S_%R.%T' SCOPE=SPFILE; SHUTDOWN IMMEDIATE STARTUP MOUNT ALTER DATABASE ARCHIVELOG; ALTER DATABASE OPEN;
Flashback must be enabled before any flashback operations are performed.
CONN / AS SYSDBA SHUTDOWN IMMEDIATE STARTUP MOUNT EXCLUSIVE ALTER DATABASE FLASHBACK ON; ALTER DATABASE OPEN;
In Oracle 11gR2, this process was simplified, allowing flashback database to be enabled/disabled without the need for a database restart. As a result, you could just issue the following.
CONN / AS SYSDBA ALTER DATABASE FLASHBACK ON; --ALTER DATABASE FLASHBACK OFF;
With flashback enabled the database can be switched back to a previous point in time or SCN without the need for a manual incomplete recovery. In the following example a table is created, the database is then flashbacked to a time before the table was created.
-- Create a dummy table. CONN scott/tiger CREATE TABLE flashback_database_test ( id NUMBER(10) ); -- Flashback 5 minutes. CONN / AS SYSDBA SHUTDOWN IMMEDIATE STARTUP MOUNT EXCLUSIVE FLASHBACK DATABASE TO TIMESTAMP SYSDATE-(1/24/12); ALTER DATABASE OPEN RESETLOGS; -- Check that the table is gone. CONN scott/tiger DESC flashback_database_test
Some other variations of the flashback database command include.
FLASHBACK DATABASE TO TIMESTAMP my_date; FLASHBACK DATABASE TO BEFORE TIMESTAMP my_date; FLASHBACK DATABASE TO SCN my_scn; FLASHBACK DATABASE TO BEFORE SCN my_scn;
The window of time that is available for flashback is determined by the DB_FLASHBACK_RETENTION_TARGET
parameter. The maximum flashback can be determined by querying the V$FLASHBACK_DATABASE_LOG
view. It is only possible to flashback to a point in time after flashback was enabled on the database and since the last RESETLOGS
command.
In addition to using SCNs and timestamps, we can create restore points and guaranteed restore points. A restore point is just a text alias representing a SCN. A guaranteed restore point means the database doesn't delete any flashback logs between that point and the current time. You should delete all unnecessary guaranteed restore points.
CREATE RESTORE POINT before_changes; CREATE RESTORE POINT before_changes GUARANTEE FLASHBACK DATABASE;
Existing restore points can be displayed using the V$RESTORE_POINT
view. To flashback to this restore point, we would issue the following command.
FLASHBACK DATABASE TO RESTORE POINT before_changes;
Flashback Query Functions
The TIMESTAMP_TO_SCN
and SCN_TO_TIMESTAMP
functions have been added to SQL and PL/SQL to simplify flashback operations.
SELECT * FROM emp AS OF SCN TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24); SELECT * FROM emp AS OF TIMESTAMP SCN_TO_TIMESTAMP(993240); DECLARE l_scn NUMBER; l_timestamp TIMESTAMP; BEGIN l_scn := TIMESTAMP_TO_SCN(SYSTIMESTAMP - 1/24); l_timestamp := SCN_TO_TIMESTAMP(l_scn); END; /
In addition to querying the V$DATABASE
view, the current SCN can be retrieved using the DBMS_FLASHBACK
package.
SELECT current_scn FROM v$database; SELECT DBMS_FLASHBACK.get_system_change_number FROM dual;
For more information see:
- Flashback Query
- Flashback Version Query
- Flashback Table
- Using Flashback Query (SELECT ... AS OF)
- Using Flashback Version Query
- Using Flashback Transaction Query
- Oracle Flashback Table: Returning Individual Tables to Past States
- Oracle Flashback Drop: Undo a DROP TABLE Operation
- Enabling Flashback Database
- Flashback Query
- Flashback and LogMiner Enhancements in Oracle Database 11g Release 1
- Flashback Data Archive (FDA) Enhancements in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...