employees where employee_id in (195,196);
2 rows deleted.
SQL> commit;
Commit complete.
About ten minutes later, HR decides to get those rows back using DBMS_FLASHBACK, and
enables Flashback for her session:
SQL> execute dbms_flashback.enable_at_time(
2 to_timestamp(sysdate - interval '45' minute));
PL/SQL procedure successfully completed.
Next, HR verifies that the two deleted rows existed as of 45 minutes ago:
SQL> select employee_id, last_name from hr.employees
2 where employee_id in (195,196);
EMPLOYEE_ID LAST_NAME
----------- -------------------------
195 Jones
196 Walsh
SQL>
To put the rows back into the HR.EMPLOYEES table, HR writes an anonymous PL/SQL procedure
to create a cursor to hold the deleted rows, disable Flashback Query, then reinsert the rows:
declare
-- cursor to hold deleted rows before closing
cursor del_emp is
select * from employees where employee_id in (195,196);
del_emp_rec del_emp%rowtype; -- all columns of the employee row
begin
-- open the cursor while still in Flashback mode
open del_emp;
-- turn off Flashback so we can use DML to put the rows
-- back into the EMPLOYEES table
dbms_flashback.disable;
loop
fetch del_emp into del_emp_rec;
exit when del_emp%notfound;
insert into employees values del_emp_rec;
end loop;
Chapter 7: Managing Transactions with Undo Tablespaces 227
commit;
close del_emp;
end; -- anonymous PL/SQL procedure
Note that HR could have enabled Flashback within the procedure; in this case, HR enabled
it outside of the procedure to run some ad hoc queries, and then used the procedure to create the
cursor, turn off Flashback, and reinsert the rows.
Pages:
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388