SQL> commit;
Commit complete.
SQL> create table employees_deleted as
2 select * from employees
3 as of timestamp systimestamp - interval '60' minute
4 where employees.employee_id not in
5 (select employee_id from employees);
Table created.
SQL> select employee_id, last_name from employees_deleted;
Chapter 7: Managing Transactions with Undo Tablespaces 225
EMPLOYEE_ID LAST_NAME
----------- -------------------------
195 Jones
196 Walsh
2 rows selected.
This is known as an out-of-place restore (in other words, restoring the table or a subset of the
table to a different location than the original). This has the advantage of being able to further
manipulate the missing rows, if necessary, before placing them back in the table; for example,
after reviewing the out-of-place restore, an existing referential integrity constraint may require that
you insert a row into a parent table before the restored row can be placed back in the child table.
One of the disadvantages of an out-of-place restore using CTAS is that neither constraints nor
indexes are rebuilt automatically.
DBMS_FLASHBACK
An alternative to Flashback Query is the package DBMS_FLASHBACK. One of the key differences
between the DBMS_FLASHBACK package and Flashback Query is that DBMS_FLASHBACK operates
at the session level, whereas Flashback Query operates at the object level.
Within a PL/SQL procedure or a user session, DBMS_FLASHBACK can be enabled and all
subsequent operations, including existing applications, can be carried out without the as of clause
being added to select statements.
Pages:
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386