Because HR knows she
deleted the rows less than an hour ago, we can use a relative timestamp value with Flashback
Query to retrieve the rows:
SQL> insert into hr.employees_archive
2 select * from hr.employees
3 as of timestamp systimestamp - interval '60' minute
4 where hr.employees.employee_id not in
5 (select employee_id from hr.employees);
2 rows created.
SQL> commit;
Commit complete.
Because we know that EMPLOYEE_ID is the primary key of the table, we can use it to retrieve
the employee records that existed an hour ago but do not exist now. Note also that we didn??™t have
to know which records were deleted; we essentially compared the table as it existed now versus
an hour ago and inserted the records that no longer exist into the archive table.
TIP
It is preferable to use the SCN for Flashback over a timestamp;
SCNs are exact, whereas the timestamp values are only stored every
three seconds to support Flashback operations. As a result, enabling
Flashback using timestamps may be off by as much as 1.5 seconds.
Although we could use Flashback Table to get the entire table back, and then archive and
delete the affected rows, in this case it is much simpler to merely retrieve the deleted rows and
insert them directly into the archive table.
Another variation of Flashback Table is to use Create Table As Select (CTAS) with the subquery
being a Flashback Query:
SQL> delete from employees where employee_id in (195,196);
2 rows deleted.
Pages:
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385