Oracle Flashback Query: Copy Historical Data Into a New Table

calendar Mar 10, 2026 / · 6 min read · oracle database flashback query sql dba data recovery insert append ctas oracle admin  ·
Share on: linkedin copy

Oracle Flashback Query: Copy Historical Data Into a New Table

Sometimes you need to go back in time and recover data the way it looked at a specific moment — without restoring the full database. Oracle makes this possible with Flashback Query, using the AS OF TIMESTAMP clause to read historical data directly from the undo tablespace. This post shows a clean, two-step pattern to create an empty copy of a table and then fill it with data from a past point in time.

What This Code Does

This script solves a common DBA and developer problem: recovering a point-in-time snapshot of table data and storing it in a separate, safe location. Instead of restoring a backup, Oracle's Flashback Query reads committed data from the past directly using the undo tablespace. The two-step approach — create first, then insert — gives you fine-grained control over the target tablespace, storage, and the exact timestamp of the historical snapshot.

Full Code

 1-- Step 1: Create an empty copy of the source table
 2CREATE TABLE old_test_table
 3TABLESPACE andy
 4AS SELECT *
 5FROM test_table
 6WHERE rownum < 1;
 7
 8-- Step 2: Insert historical data using Flashback Query
 9INSERT /*+ APPEND */ INTO old_test_table
10(SELECT * FROM test_table AS OF TIMESTAMP
11  TO_TIMESTAMP('24-AUG-06 12:00:00','DD-MON-YY HH24:MI:SS'));
12
13COMMIT;

Step-by-Step Code Breakdown

Step 1 — Create an Empty Shell Table (CTAS)

1CREATE TABLE old_test_table
2TABLESPACE andy
3AS SELECT *
4FROM test_table
5WHERE rownum < 1;

This uses CTAS (CREATE TABLE AS SELECT), a single DDL command that creates a new table whose column structure is copied exactly from the source table. The trick here is WHERE rownum < 1 — because ROWNUM starts at 1 for the first row, this condition is never true, so zero rows are selected. The result is a perfectly structured empty table with no data, placed in the andy tablespace. This separates the table definition step from the data loading step, which is useful when you want control over storage, tablespace placement, or indexes before any rows arrive.

Key points for this step:

  • TABLESPACE andy — directs Oracle to store the new table in the named tablespace, not the user's default tablespace
  • AS SELECT \* FROM test_table — clones all columns and data types automatically with no manual column list needed
  • WHERE rownum < 1 — a reliable Oracle idiom to produce zero rows; rownum is never less than 1 so the WHERE clause always evaluates to false
  • Constraints such as NOT NULL, primary keys, and indexes are not copied by CTAS — only column names and data types are transferred

Step 2 — Insert Historical Data with Flashback Query

1INSERT /*+ APPEND */ INTO old_test_table
2(SELECT * FROM test_table AS OF TIMESTAMP
3  TO_TIMESTAMP('24-AUG-06 12:00:00','DD-MON-YY HH24:MI:SS'));
4
5COMMIT;

This line combines three important Oracle features working together: the APPEND hint, the AS OF TIMESTAMP Flashback clause, and the TO_TIMESTAMP conversion function.

Key points for this step:

  • AS OF TIMESTAMP — tells Oracle to read the table as it was at a specific past moment; Oracle retrieves this data from the undo tablespace, not the current data files
  • TO_TIMESTAMP('24-AUG-06 12:00:00','DD-MON-YY HH24:MI:SS') — converts a formatted string into an Oracle TIMESTAMP value; the format model must exactly match the string provided
  • /\*+ APPEND \*/ — this is an Oracle optimizer hint that switches the insert to direct-path mode: data is written directly to the datafiles, bypassing the buffer cache, which makes large inserts significantly faster
  • The COMMIT at the end is required before you can read the table again after a direct-path insert

Key Insights and Warnings

Flashback Query Depends on Undo Retention

Flashback Query works only as far back as your undo data exists. If the undo tablespace has recycled or overwritten the blocks for your target timestamp, Oracle will throw:

1ORA-01555: snapshot too old

The further back in time you query, the larger your undo tablespace and UNDO_RETENTION parameter need to be.

The APPEND Hint Is a Direct-Path Insert

The /\*+ APPEND \*/ hint tells Oracle to skip the buffer cache and write rows directly past the table's high water mark — the boundary of previously used space. This is much faster for large datasets. However, there are important trade-offs:

  • Only one session can perform a direct-path insert on a table at a time
  • The table is locked until you COMMIT or ROLLBACK
  • You cannot query the target table in the same session after the insert until you commit
  • For small datasets, a conventional insert may actually be faster than direct-path
  • If the table has enabled triggers or enabled foreign key constraints, Oracle silently falls back to conventional insert and ignores the hint

AS OF TIMESTAMP vs. AS OF SCN

Oracle Flashback Query supports two modes:

ModeSyntaxNotes
TimestampAS OF TIMESTAMP TO_TIMESTAMP(...)Human-readable; ~3-second granularity internally
SCNAS OF SCN 1234567Exact; uses System Change Number directly

The timestamp method is easier to read but Oracle converts it to an SCN internally, which introduces up to a 3-second margin of imprecision. For critical recoveries, SCN is more precise.

The WHERE rownum < 1 Idiom

This is a well-known Oracle pattern. Using WHERE 1=0 achieves the same result in most databases, but WHERE rownum < 1 is specifically an Oracle idiom that guarantees the optimizer produces zero rows while still giving CTAS the full column structure. Either works in Oracle, but rownum < 1 makes the Oracle-specific intent immediately clear to other DBAs.

Common Use Cases

  • Accidental data deletion recovery — pull a table back to its state before a bad DELETE or UPDATE ran
  • Audit and compliance snapshots — capture what data looked like at a specific business date
  • Before/after comparison — preserve the "before" state of a table when testing a data migration
  • Point-in-time reporting — recreate historical data sets for month-end or year-end reports without a full restore

References

Posts in this series