Flashback technology has a flashback table, a flashback delete, a flashback query, a flashback transaction query, a flashback transaction, a flashback database, and a flashback data archive. Among them, the flashback query, Flashback transaction query is used to "observe" the past; the Flashback data archive is not a standalone function, its function is to extend the time window of the flashback query; The flashback table, the Flashback delete table can "go back" to the past in a table, and the flashback transaction can "go back" to the past in the transaction unit. The flashback database is able to "go back" to the past in a database.
I. Flash back table (Flashback tables)
The flashback table is used to undo the undo table space, so the table can be flashed back how long before the data file Undo_retention,undo table space to start the auto-growth function, whether set guarantee, such as the impact of three factors.
1. Flash back to the exact time
Sql> Flashback table Scott.emp to timestamp to_timestamp (' 2014-09-16 04:32:00 ', ' yyyy-mm-dd hh24:mi:ss ');
2. Flash back 10 minutes ago
Sql> Flashback table Scott.emp to timestamp (systimestamp-interval ' minute);
3. Flash the scott.emp back to the SCN at 1086000.
Sql> Flashback table Scott.emp to SCN 1086000;
--about how the SCN and timestamps are converted see Oracle shredding 31st
4. Flash the scott.emp and scott.dept two tables back to the SCN 1086000 (primarily for tables with foreign KEY constraints)
Sql> Flashback table Scott.emp,scott.dept to SCN 1086000;
Use the Flash back table to note the following:
(1) The returned table must have the Line mobility feature enabled
Sql> ALTER TABLE dept enable row Movement;
(2) The performer of the "FLASHBACK table" command must have the "FLASHBACK any table" system permission or have the "FLASHBACK" object permission on the table being flashed back.
(3) "FLASHBACK TABLE" belongs to the DDL command and is implicitly committed.
(4) Any table of SYS users cannot use this feature.
Ii. Flash-back deletion table (Flashback Drop)
The Flashback deletion list refers to the effect of withdrawing "DROP table".
1. Flashback of deleted scott.emp form
Sql> Flashback table Scott.emp to before drop;
2. When the table is deleted, a new table with the same name is created, and if an attempt is made to flash back to the original table using the above command, it will be reported ora-38312:original name is used by an existing object error and can be renamed.
Sql> Flashback table Test to before drop rename to Test1;
3. If the table name is duplicated, the flashback is followed by a post-in-first-out principle.
4. The recovered Recycle Bin object can be indicated on flashback
Sql> Flashback table "Bin$ayid7zbbjwngukjadqiiua==$0" to before drop;
The Flashback Delete Table works by: When the "drop table" command executes, the table and its indexes are not actually deleted, and the space occupied is simply assigned to another database object: The Recycle Bin object, essentially the equivalent of renaming. Note: The tablespace allocates the space of the Recycle Bin object to the segment that requires space under the pressure of autogrow, and the data file does not grow automatically until the Recycle Bin object is exhausted.
5. Can disable Recycle Bin function
Sql> alter system set recyclebin= ' OFF ' scope=spfile;
6. Delete all objects from the current user Recycle Bin
Sql> purge RecycleBin;
Third, flash back query (Flashback)
Querying past data in a table is called a flashback query, and there are two main ways: 1. Flash back to point in time query. Use the "as of" clause of the SELECT command with the PL/SQL package to dbms_flashback the query over a point in time. 2. Flash back to the version query. Queries using the "versions between" clause of the SELECT command over a period of time.
Flashback Point-in-time query
Using the "as of" clause
1. Check the salary of employee number No. 7788 at specific time
Sql> Select Sal from emp as of timestamp to_timestamp (' 2014-09-16 10:02:30 ', ' Yyyy-mm-dd,hh24:mi:ss ') where empno=7788;
2. Check the wages of Employee No. No. 7788 at five minutes ago
Sql> Select Sal from emp as of timestamp (systimestamp-interval ' 5 ' minute) where empno=7788;
3. Query specific SCN
Sql> SELECT * from emp as of SCN 1095000;
4. Change the salary of employee number No. 7788 to a value up to 15 minutes ago
sql> Update emp Set sal= (select Sal from emp as of timestamp (Systimestamp-interval ' minute) where empno=7888) whe Re empno=7788;
Using the Dbms_flashback Package
Using the Enable_at_time or ENABLE_AT_SCN stored procedure of the Dbms_flashback package locks a session-level flashback time target into the flashback mode, and subsequent query commands can omit "as of" until the call is Dbms_flashback _disable the stored procedure until it is closed.
For example, the flashback mode session is fixed at 15 minutes ago:
sql> exec dbms_flashback.enable_at_time (systimestamp-interval ' minute);
Now for the query, note that the table that was queried at this point was 15 minutes ago.
Sql> Select Sal from emp where empno=7788; --Ignoring the "as of" clause
When you access date functions such as Sysdate, Systimestamp, and so on, their return value is still the current value, not the value before 15 minutes.
DML and DDL execution error when in Flash-back session mode
sql> Update emp set sal=4000 where empno=7788;
Update emp set sal=4000 where empno=7788
*
ERROR at line 1:
Ora-08182:operation not supported while in Flashback mode
If the query is complete, call the disable stored procedure to turn off Flashback session mode.
sql> exec dbms_flashback.disable;
Flash back version Query
The Flashback version query can run through a certain length of time window, and can return data at different points in the time window by using only one query command.
For example, the salary of employee number NO. 7788 will be modified first through 3 transactions. Its value is 4000, then 5000, then 10000, and finally 3000.
Sql> SelectSal fromEmpwhereEmpno=7788; SAL---------- 4000SQL> UpdateEmpSetSal= the whereEmpno=7788;1row updated. SQL> Commit;CommitComplete . SQL> UpdateEmpSetSal=10000 whereEmpno=7788;1row updated. SQL> Commit;CommitComplete . SQL> UpdateEmpSetSal= the whereEmpno=7788;1row updated. SQL> Commit;CommitComplete.
Perform a flashback version query
Sql> SelectEmpno,sal fromEMP2Versionsbetween timestamp(Systimestamp-Interval' the'Minute andMaxValue3 whereEmpno=7788; EMPNO SAL----- ---------- 7788 the 7788 10000 7788 the 7788 4000
With "versions between", we can see that in 15 minutes, the salary of employee number No. 7788 is 4 values, indicating that there are 3 transactions that have been modified. To be able to see the order of these transactions, you can use pseudo-fields in the query list. As shown below:
Sql> Select 2VERSIONS_XID,VERSIONS_STARTSCN,VERSIONS_ENDSCN,3Empno,sal4 fromEMP5Versionsbetween timestamp(Systimestamp-Interval' the'Minute andMaxValue6 whereEmpno=7788 7 Order by 2nulls first; Versions_xid versions_startscn versions_endscn EMPNO SAL---------------- ----------------- --------------- ----- ----------060002000f0300001097139 7788 400002001100fb0200001097139 1097148 7788 the03001d001e0300001097148 1097153 7788 100000900170000030000 1097153 7788 the
Where Versions_xid for the transaction number VERSIONS_STARTSCN and VERSIONS_ENDSCN are the SCN at the start of the transaction, and the SCN at the beginning of the next transaction that modifies the line. The SCN numbers of the two fields are easily derived from the actual order of modification: 4000,5000,10000, and finally 3000.
Reference: "Courage: Oracle 11g Database Recovery Technology"
Oracle Flash Back Technology (Flashback)