Introduction to oracle flash back table

Oracle flashback table description flashback table purpose: 1. Use the flashback table statement to restore the TABLE to the status before a human error or application error occurs. 2. The amount of undo data in the system depends on how long it takes to flash back. 3. In addition, the oracle database cannot restore tables that have modified the structure through DDL statements. Note: oracle strongly recommends setting UNDO_MANAGEMENT to AUTO (automatic data rollback management ). In addition, set UNDO_RETENTION (the retention time of data rollback) to an appropriate interval. For more information, see the documentation on the UNDO_MANAGEMENT http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams221.htm#REFRN10224 AndUNDO_RETENTION http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams222.htm#REFRN10225 Initialization parameters. You cannot roll back the flash back statement. However, you can use another flash back statement and specify a time point before the current time. Therefore, it is best to record the current SCN number before declaring a flash back statement! Prerequisites: 1. we need to have the flashback permission on the specified table or the flashback anytable permission to flash back a table to the previous time point; 2. you must have a series of permissions on the SELECT, INSERT, DELETE, and andALTER object privileges tables. 3. except for the use of to before drop, Row movement (alter table test_move enable row movement;) should be enabled for the flashback of the partition table, which is generally used for the partition table. When a Row is updated, if the updated partition column is updated and the updated column value does not belong to the original partition, if this option is enabled, the row will be deleted from the partition, and add it to the updated partition. It is equivalent to an implicit delete + insert, but does not trigger the insert/delete trigger. If this option is not enabled, an error will be reported during the update. 4. To restore a TABLE to a restore point, you must have select any dictionary orFLASHBACKANY TABLE system privilege or theSELECT_CATALOG_ROLE role. Syntax:

Syntax description: flashback table [schema.] table [, [schema.] table]... TO {SCN | TIMESTAMP} expr | restore point restore_point} [{ENABLE | DISABLE} TRIGGERS] | before drop [rename to table]}; flashback table [solution name.] table name [, [solution name.] table name] TO {System Change number | timestamp} expression | recovery point recovery name} [{enable | disable} trigger] | before deletion [rename table]}; example: 1. restore the TABLE to the previous state: [SQL] create table employees_demo as select * FROM employees; [SQL] SELEC T salary FROM employees_test WHERE salary <2500; SALARY ---------- 2400 2200 2100 2400 Note: As a newly created table, before using the following flash back statement, you need to wait for at least five minutes for the SCN to be sent to the flashed back image table. [SQL] alter table employees_test enable row movement; [SQL] UPDATE employees_test SET salary = salary * 1.1 WHERE salary <2500; 5 rows updated. COMMIT; [SQL] SELECT salary FROM employees_test WHERE salary <2500; SALARY ---------- 2420 2310 2420 flash back the table to the status before updating data: [SQL] SQL> flashback table employees_test 2 to timestamp to_timestamp ('2017-08-02 19:12:20 ', 'yyyy-mm-dd hh24: mi: ss'); flash back complete. [SQL] SQL> select salary from employees_test where salary <2500; SALARY ---------- 2400 2200 2100 2400 2. restore the deleted table. Use the tablespace Recyclebin (recycle bin) to retrieve the deleted data: [SQL] SQL> drop table employees_test; the table has been deleted. SQL> select object_name, original_name, operation, type, droptime from recyclebin; OBJECT_NAME ORIGINAL_NAME OPERATION TYPE DROPTIME when running --------- when running BIN $ cost = $0 EMPLOYEES_TEST DROP TABLE 2012-08-02: 19: 26: 54 [SQL] SQL> flashback table employees_test to before drop; flash back complete. SQL> select object_name, original_name, operation, type, droptime from recyclebin; unselected row [SQL] SQL> drop table employees_test; the table has been deleted. SQL> flashback table employees_test to before drop rename to employees_demo; flash back complete. SQL> select * from employees_test; select * from employees_test * row 1st error: ORA-00942: The table or view does not have the above content for simple flashback table operations, content from the oracle official documentation.
 
Author IndexMan