Tuesday, June 02, 2009

Flashback table in Oracle 9i/10g

To flashback a table, we can create a copy of the table at a certain point in time, using syntax such as:

CREATE TABLE daves_restore AS (SELECT * FROM isfts.lrmp_download_today AS OF TIMESTAMP TO_TIMESTAMP('14-NOV-08 13:30:00', 'DD-MON-YY HH24:MI:SS') MINUS SELECT * FROM lrmp_download_today)

which creates the table with the delta changes in it. Or to create the entire table:

create table agr55.AHSRELVALUE_BAK as (SELECT * FROM agr55.AHSRELVALUE as of timestamp TO_TIMESTAMP('02-JUN-09 10:45:00', 'DD-MON-YY HH24:MI:SS')

No comments: