8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Flashback Query
Oracle Flashback Query allows users to see a consistent view of the database as it was at a point in the past. This functionality allows comparative reporting over time and recovery from logical corruptions.
Related articles.
- Flashback New Features and Enhancements in Oracle Database 10g
- Flashback and LogMiner Enhancements in Oracle Database 11g Release 1
Prerequisites
Oracle Flashback Query can only be used if the server is configured to use Automatic Undo Management, rather than traditional rollback segments. The maximum time period that can be flashbacked to is defined using the UNDO_RETENTION
parameter in the init.ora file. Alternatively, this parameter can be set using the following command.
ALTER SYSTEM SET UNDO_RETENTION = <seconds>;
Using Flashback Query
Flashback Query is enabled and disabled using the DBMS_FLASHBACK
package. The point in time of the flashback can be
specified using the SCN or the actual time.
EXEC DBMS_FLASHBACK.enable_at_system_change_number(123); EXEC DBMS_FLASHBACK.enable_at_time('28-AUG-01 11:00:00');
Once you've finished performing all your read-only operations you can turn off flashback query.
EXEC DBMS_FLASHBACK.disable;
The flashback query is turned off when the session ends if there is no explicit call to the disable procedure. The current
system change number can be returned using the DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER
function.
Example Data Recovery
Assuming all data was accidentally deleted from the EMPLOYEES
table at 9:05AM we could recover it using the following.
DECLARE CURSOR c_emp IS SELECT * FROM employees; v_row c_emp%ROWTYPE; BEGIN DBMS_FLASHBACK.enable_at_time('28-AUG-01 09:00:00'); OPEN c_emp; DBMS_FLASHBACK.disable; LOOP FETCH c_emp INTO v_row; EXIT WHEN c_emp%NOTFOUND; INSERT INTO employees VALUES (v_row.employee_id, v_row.first_name, v_row.last_name, v_row.email, v_row.phone_number, v_row.hire_date, v_row.job_id, v_row.salary, v_row.commission_pct, v_row.manager_id, v_row.department_id, v_row.dn); END LOOP; CLOSE c_emp; COMMIT; END; /
Notice that the Flashback Query session is disabled after the cursor is created so that the DML operations can be performed to recover the data.
Restrictions
- The server must be configured to use Automatic Undo Management.
- No DDL or DML can be issued when using Flashback Query.
- Flashback Query does not reverse DDL operations such as
DROP
, only DML alterations to the data. - Flashback Query does apply to code objects (Packages, Procedures, Function or Triggers). If invoked, the current definition will be executed against the flashback data.
For more information see:
- Flashback New Features and Enhancements in Oracle Database 10g
- Flashback and LogMiner Enhancements in Oracle Database 11g Release 1
Hope this helps. Regards Tim...