Oracle Flashback Query ： SELECT AS OF
Oracle Flashback Version Query ：
DBMS_FLASHBACK Package
Oracle Flashback Table:
Oracle Flashback Drop:
Oracle Flashback Database:
http://docs.oracle.com/cd/B28359_01/backup.111/b28270/rcmflash.htm#i1018669
Oracle Flashback Query ： SELECT AS OF
Oracle 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';
Flashback Transaction Query (与Flashback Version Query联合查询)
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')
);
DBMS_FLASHBACK Package
Flashback Transaction :Flashback Transaction is part of DBMS_FLASHBACK package
Flashback Data Archive (Oracle Total Recall):
Create table employee and store the historical data in the default Flashback Data Archive:
CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10),
JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE;
Create table employee and store the historical data in the Flashback Data Archive fla1:
CREATE TABLE employee (EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10),
JOB VARCHAR2(9), MGR NUMBER(4)) FLASHBACK ARCHIVE fla1;
Enable flashback archiving for the table employee and store the historical data in the default Flashback Data Archive:
ALTER TABLE employee FLASHBACK ARCHIVE;
Enable flashback archiving for the table employee and store the historical data in the Flashback Data Archive fla1:
ALTER TABLE employee FLASHBACK ARCHIVE fla1;
Disable flashback archiving for the table employee:
ALTER TABLE employee NO FLASHBACK ARCHIVE;
Oracle Flashback Table:
Connect SQL*Plus to the target database and identify the current SCN.
You cannot roll back a FLASHBACK TABLE statement, but you can issue another FLASHBACK TABLE statement and specify a time just prior to the current time. Therefore, it is advisable to record the current SCN. You can obtain it by querying V$DATABASE as follows:
SELECT CURRENT_SCN
FROM V$DATABASE;
Identify the time, SCN, or restore point to which you want to return the table.
If you have created restore points, then you can list available restore points by executing the following query:
SELECT NAME, SCN, TIME
FROM V$RESTORE_POINT;
Ensure that enough undo data exists to rewind the table to the specified target.
If the UNDO_RETENTION intialization parameter is set, and the undo retention guarantee is on, then you can use the following query to determine how long undo data is being retained:
SELECT NAME, VALUE/60 MINUTES_RETAINED
FROM V$PARAMETER
WHERE NAME = 'undo_retention';
Ensure that row movement is enabled for all objects that you are rewinding with Flashback Table.
You can enable row movement for a table with the following SQL statement, where table is the name of the table that you are rewinding:
ALTER TABLE table ENABLE ROW MOVEMENT;
Determine whether the table that you intend to flash back has dependencies on other tables. If dependencies exist, then decide whether to flash back these tables as well.
You can issue the following SQL query to determine the dependencies, where schema_name is the schema for the table to be flashed back and table_name is the name of the table:
SELECT other.owner, other.table_name
FROM sys.all_constraints this, sys.all_constraints other
WHERE this.owner = schema_name
AND this.table_name = table_name
AND this.r_owner = other.owner
AND this.r_constraint_name = other.constraint_name
AND this.constraint_type='R';
Execute a FLASHBACK TABLE statement for the objects that you want to flash back.
The following SQL statement returns the hr.temp_employees table to the restore point named temp_employees_update:
FLASHBACK TABLE hr.temp_employees
TO RESTORE POINT temp_employees_update;
The following SQL statement rewinds the hr.temp_employees table to its state when the database was at the time specified by the SCN:
FLASHBACK TABLE hr.temp_employees
TO SCN 123456;
As shown in the following example, you can also specify the target point in time with TO_TIMESTAMP:
FLASHBACK TABLE hr.temp_employees
TO TIMESTAMP TO_TIMESTAMP('2007-10-17 09:30:00', 'YYYY-MM-DD HH:MI:SS');
Oracle Flashback Drop:
Use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover objects from the recycle bin. You can specify either the name of the table in the recycle bin or the original table name.
This section assumes a scenario in which you drop the wrong table. Many times you have been asked to drop tables in the test databases, but in this case you accidentally connect to the production database instead and drop hr.employee_demo. You decide to use FLASHBACK TABLE to retrieve the dropped object.
To retrieve a dropped table:
Connect SQL*Plus to the target database and obtain the name of the dropped table in the recycle bin.
You can use the SQL*Plus command SHOW RECYCLEBIN as follows:
SHOW RECYCLEBIN;
ORIGINAL NAME RECYCLEBIN NAME TYPE DROP TIME
---------------- --------------------------------- ------------ -------------
EMPLOYEE_DEMO BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 TABLE 2005-04-11:17:08:54
The ORIGINAL NAME column shows the original name of the object, while the RECYCLEBIN NAME column shows the name of the object as it exists in the bin.
Alternatively, you can query USER_RECYCLEBIN or DBA_RECYCLEBIN to obtain the table name. The following example queries the views to determine the original names of dropped objects:
SELECT object_name AS recycle_name, original_name, type
FROM recyclebin;
RECYCLE_NAME ORIGINAL_NAME TYPE
-------------------------------- --------------------- ----------
BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 EMPLOYEE_DEMO TABLE
BIN$JKS983293M1dsab4gsz/I249==$0 I_EMP_DEMO INDEX
If you plan to manually restore original names for dependent objects, then ensure that you make note of each dependent object's system-generated recycle bin name before you restore the table.
Note:
Object views such as DBA_TABLES do not display the recycle bin objects.
Optionally, query the table in the recycle bin.
You must use the recycle bin name of the object in your query rather than the object's original name. The following example queries the table with the recycle bin name of BIN$KSD8DB9L345KLA==$0:
SELECT *
FROM "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0";
Quotes are required because of the special characters in the recycle bin name.
Note:
If you have the necessary privileges, then you can also use Oracle Flashback Query on tables in the recycle bin, but only by using the recycle bin name rather than the original table name. You cannot use DML or DDL statements on objects in the recycle bin.
Retrieve the dropped table.
Use the FLASHBACK TABLE ... TO BEFORE DROP statement. The following example restores the BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0 table, changes its name back to hr.employee_demo, and purges its entry from the recycle bin:
FLASHBACK TABLE "BIN$gk3lsj/3akk5hg3j2lkl5j3d==$0" TO BEFORE DROP;
Note that the table name is enclosed in quotes because of the possibility of special characters appearing in the recycle bin object names.
Alternatively, you can use the original name of the table:
FLASHBACK TABLE HR.EMPLOYEE_DEMO TO BEFORE DROP;
You can also assign a new name to the restored table by specifying the RENAME TO clause. For example:
FLASHBACK TABLE "BIN$KSD8DB9L345KLA==$0" TO BEFORE DROP
RENAME TO hr.emp_demo;
Optionally, verify that all dependent objects retained their system-generated recycle bin names.
The following query determines the names of the indexes of the retrieved hr.employee_demo table:
SELECT INDEX_NAME
FROM USER_INDEXES
WHERE TABLE_NAME = 'EMPLOYEE_DEMO';
INDEX_NAME
------------------------------
BIN$JKS983293M1dsab4gsz/I249==$0
Optionally, rename the retrieved indexes to their original names.
The following statement renames the index to its original name of i_emp_demo:
ALTER INDEX "BIN$JKS983293M1dsab4gsz/I249==$0" RENAME TO I_EMP_DEMO;
If the retrieved table had referential constraints before it was placed in the recycle bin, then re-create them.
This step must be performed manually because the recycle bin does not preserve referential constraints on a table.
Retrieving Objects When Multiple Objects Share the Same Original Name
You can create, and then drop, several objects with the same original name. All the dropped objects will be stored in the recycle bin. For example, consider the SQL statements in the following example.
Example 16-1 Dropping Multiple Objects with the Same Name
CREATE TABLE temp_employees ( ...columns ); # temp_employees version 1
DROP TABLE temp_employees;
CREATE TABLE temp_employees ( ...columns ); # temp_employees version 2
DROP TABLE temp_employees;
CREATE TABLE temp_employees ( ...columns ); # temp_employees version 3
DROP TABLE temp_employees;
In Example 16-1, each table temp_employees is assigned a unique name in the recycle bin when it is dropped. You can use a FLASHBACK TABLE ... TO BEFORE DROP statement with the original name of the table, as shown in this example:
FLASHBACK TABLE temp_employees TO BEFORE DROP;
The most recently dropped table with this original name is retrieved from the recycle bin, with its original name. Example 16-2 shows the retrieval from the recycle bin of all three dropped temp_employees tables from the previous example, with each assigned a new name.
Example 16-2 Renaming Dropped Tables
FLASHBACK TABLE temp_employees TO BEFORE DROP
RENAME TO temp_employees_VERSION_3;
FLASHBACK TABLE temp_employees TO BEFORE DROP
RENAME TO temp_employees_VERSION_2;
FLASHBACK TABLE temp_employees TO BEFORE DROP
RENAME TO temp_employees_VERSION_1;
Because the original name in FLASHBACK TABLE refers to the most recently dropped table with this name, the last table dropped is the first retrieved.
You can also retrieve any table from the recycle bin, regardless of any collisions among original names, by using the unique recycle bin name of the table. For example, assume that you query the recycle bin as follows (sample output included):
SELECT object_name, original_name, createtime
FROM recyclebin;
OBJECT_NAME ORIGINAL_NAME CREATETIME
------------------------------ --------------- -------------------
BIN$yrMKlZaLMhfgNAgAIMenRA==$0 TEMP_EMPLOYEES 2007-02-05:21:05:52
BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TEMP_EMPLOYEES 2007-02-05:21:25:13
BIN$yrMKlZaQMhfgNAgAIMenRA==$0 TEMP_EMPLOYEES 2007-02-05:22:05:53
You can use the following command to retrieve the middle table:
FLASHBACK TABLE BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TO BEFORE DROP;
Oracle Flashback Database: