Welcome to the Oracle community forum and interact with 2 million technical staff to access the flash query function introduced by Oraclehrefsearch.e800.com. cnoracletarget_balkaltoracle9i to display the query results of the previous time point. However, the table itself has not changed. Oracle10g enhances the flash back function.
Welcome to the Oracle Community Forum, interact with 2 million technicians> go to Oracle href = "http://search.e800.com.cn/Oracle" target = _ blank alt = "Oracle"> the flashback query feature introduced by Oracle 9i displays the query results for the previous time point. However, the table itself has not changed. Oracle 10g enhanced the flash back function
Welcome to the Oracle community forum and interact with 2 million technical staff> enter
Href = "http://search.e800.com.cn/Oracle" target = _ blank alt = "Oracle">Oracle9i introduces the flash back query function to display the query results of the previous time point. However, the table itself has not changed.Oracle 10gEnhanced the flashback function to permanently flash back a table. Now Oracle 10 Gb Second Edition adds the specifiedRestorePoint to make this process more convenient.
Storage
You do not need to know the exact SCN for the application Recovery point. After the restoration point is set up, it restores the current SCN. Therefore, the restoration point name is equivalent to the corresponding SCN.
The syntax of the create restore point command is simple:
Create restore point restore_point_name;
The flashback any table or select any dictionary permission requires a normal (unguaranteed) Recovery Point. Once the recovery POINT is no longer needed, the corresponding drop restore point command will delete it.
List A shows the correct permissions provided to OE mode, creates A recovery point, and creates a flashback table after an error UPDATE that misses the correct WHERE clause.
SQL> CONNECT / AS SYSDBA
Connected.
SQL> GRANT SELECT ANY DICTIONARY TO oe;
Grant succeeded.
SQL> CONNECT OE/OE
Connected.
SQL> -- Tables must have row movement enabled to flash back
SQL> ALTER TABLE warehouses 2 ENABLE ROW MOVEMENT;
Table altered.
SQL> CREATE RESTORE POINT my_update;
Restore point created.
SQL> COLUMN name FORMAT a25
SQL> COLUMN time FORMAT a32
SQL>
SQL> SELECT name, scn, time,
guarantee_flashback_database 2 FROMv$restore_point;
NAME SCN TIME GUA
------------------------- ---------- ------------------------------
MY_UPDATE 734934 10-AUG-06 03.32.29.000000000 PM NO
SQL> UPDATE warehouses 2 SET warehouse_name = ''Bellevue, WA'';
9 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT warehouse_id, warehouse_name 2 FROM warehouses;
WAREHOUSE_ID WAREHOUSE_NAME
------------ -----------------------------------
1 Bellevue, WA 2 Bellevue, WA 3 Bellevue, WA 4 Bellevue,
WA 5 Bellevue, WA 6 Bellevue, WA 7 Bellevue, WA 8 Bellevue,
WA 9 Bellevue, WA 9 rows selected.SQL> CONNECT / AS SYSDBA Connected.
SQL> GRANT SELECT ANY DICTIONARY TO oe;
Grant succeeded.
SQL> CONNECT OE/OE
Connected.
SQL> -- Tables must have row movement enabled to flash back
SQL> ALTER TABLE warehouses 2 ENABLE ROW MOVEMENT;
Table altered.
SQL> CREATE RESTORE POINT my_update;
Restore point created.
SQL> COLUMN name FORMAT a25
SQL> COLUMN time FORMAT a32
SQL>
SQL> SELECT name, scn, time,
guarantee_flashback_database 2 FROMv$restore_point;
NAME SCN TIME GUA
------------------------- ---------- -------------------------------
MY_UPDATE 734934 10-AUG-06 03.32.29.000000000 PM NO
SQL> UPDATE warehouses 2 SET warehouse_name = ''Bellevue, WA'';
9 rows updated.
SQL> COMMIT;
Commit complete.
SQL> SELECT warehouse_id, warehouse_name 2 FROM warehouses;
WAREHOUSE_ID WAREHOUSE_NAME
------------ -----------------------------------
1 Bellevue, WA 2 Bellevue, WA 3 Bellevue,
WA 4 Bellevue, WA 5 Bellevue, WA 6 Bellevue, WA 7 Bellevue,
WA 8 Bellevue, WA 9 Bellevue, WA 9 rows selected.
SQL> FLASHBACK TABLE warehouses 2 TO RESTORE POINT my_update;
Flashback complete.
SQL> SELECT warehouse_id, warehouse_name 2 FROM warehouses;
WAREHOUSE_ID WAREHOUSE_NAME
------------ -----------------------------------
1 Southlake, Texas 2 San Francisco 3 New Jersey 4 Seattle,
Washington 5 Toronto 6 Sydney 7 Mexico City 8 Beijing 9 Bombay
9 rows selected.
SQL> FLASHBACK TABLE warehouses 2 TO RESTORE POINT my_update;
Flashback complete.
SQL> SELECT warehouse_id, warehouse_name 2 FROM warehouses;
WAREHOUSE_ID WAREHOUSE_NAME
------------ -----------------------------------
1 Southlake, Texas 2 San Francisco 3 New Jersey 4 Seattle,
Washington 5 Toronto 6 Sydney 7 Mexico City 8 Beijing 9 Bombay
9 rows selected.
|
List
Recovery points are stored in the control file, so they are still valid even if the database is disabled (installed but not enabled. This means you can also use them in the flashback database command.
Author: Bob Watkins (OCP, mcba, MCSE, MCT) is a computer professional with 25 years of experience and has worked as a technical trainer, consultant, and database administrator.
