Export an Oracle Database from a Point in Time Using FLASHBACK_TIME

calendar Mar 9, 2026 / · 5 min read · oracle flashback exp utility database backup point in time recovery parfile dba scripts oracle export  ·
Share on: linkedin copy

Export an Oracle Database from a Point in Time Using FLASHBACK_TIME

The Oracle exp utility supports a FLASHBACK_TIME parameter that lets a database administrator export a full database with all data consistent to a single point in time. This is critical for large, active databases where export duration may span minutes or hours and data must not drift between tables.

Purpose

When you run a standard Oracle export on a live database, different tables may be captured at slightly different moments. This can produce an inconsistent dump file where related tables are out of sync. The FLASHBACK_TIME parameter solves this by anchoring all exported data to one specific timestamp — Oracle translates that timestamp into a System Change Number (SCN) and uses Flashback Query internally to read data as it was at that exact moment.

This technique is the modern replacement for the older CONSISTENT=Y parameter of the legacy exp tool.

Breakdown of Code

Method 1 — Command-Line Export with FLASHBACK_TIME

1exp / file=full_scr9.dmp log=full_scr9.log flashback_time=TO_TIMESTAMP('10-09-2006 09:00:00', 'DD-MM-YYYY HH24:MI:SS')
PartWhat It Does
exp /Runs the Oracle export utility using OS authentication (no password required when run as oracle OS user)
file=full_scr9.dmpSets the output dump file name to full_scr9.dmp
log=full_scr9.logWrites all export activity and messages to full_scr9.log
flashback_time=TO_TIMESTAMP(...)Converts the text string '10-09-2006 09:00:00' using format DD-MM-YYYY HH24:MI:SS into an Oracle TIMESTAMP, which exp uses to find the nearest SCN

The TO_TIMESTAMP function is standard Oracle SQL that parses a string into a proper timestamp value using a named format model.

Method 2 — Export Using a Parameter File (parfile)

1exp / parfile=full_scr9.par

Contents of full_scr9.par:

1userid=system/******
2file=full_scr9.dmp
3log=full_scr9.log
4flashback_time='2006-09-13 12:00:00'
PartWhat It Does
exp / parfile=full_scr9.parTells exp to read all parameters from the external file full_scr9.par instead of the command line
userid=system/******Sets the connecting user; the password is masked here for security
file=full_scr9.dmpDestination dump file
log=full_scr9.logDestination log file
flashback_time='2006-09-13 12:00:00'The plain timestamp string used inside a parfile; exp accepts this simplified format directly within a parameter file

Using a parfile keeps credentials out of shell history and makes the command repeatable and scriptable.

Key Points

  • FLASHBACK_TIME vs FLASHBACK_SCN — These two parameters are mutually exclusive; you use one or the other, never both at the same time.
  • SCN resolution — Oracle does not use the timestamp directly. It finds the SCN that most closely matches your given timestamp, then performs a Flashback Query from that SCN.
  • Undo dependency — The flashback export relies on undo data still being available in the UNDO tablespace. If undo data for the target time has been recycled, the export will fail.
  • OS authentication (/) — Using exp / connects via OS-level authentication. This requires the OS user running the command to be in the dba group and is only valid locally on the database server.
  • parfile timestamp format — When using a parfile, the timestamp can be provided as a plain quoted string 'YYYY-MM-DD HH24:MI:SS' without wrapping it in TO_TIMESTAMP().
  • Legacy tool note — The exp utility is the original export tool (pre-Data Pump). Oracle deprecated exp/imp in favor of expdp/impdp starting with Oracle 10g, but exp still works in many versions.

Insights and Explanations

Why Point-in-Time Export Matters

On a busy production database, rows in Table A may be committed while Table B is still mid-transaction during an export run. Without a flashback anchor, the resulting dump represents no single real state of the database — a serious problem for consistent restores, audits, or migrations.

TO_TIMESTAMP Format Model

The format string 'DD-MM-YYYY HH24:MI:SS' is an Oracle NLS format mask:

  • DD — two-digit day
  • MM — two-digit month
  • YYYY — four-digit year
  • HH24 — hour in 24-hour format
  • MI — minutes
  • SS — seconds

This must exactly match the literal string passed as the first argument or Oracle will throw an ORA-01858 or similar date format error.

parfile vs Command-Line Syntax Difference

Notice that on the command line, flashback_time requires the full TO_TIMESTAMP() function call, while inside a parfile it accepts the simpler quoted string form. This is because the command-line parser handles the value differently from the file parser.

Moving to Data Pump (Modern Alternative)

For Oracle 10g and newer, Oracle recommends replacing exp with expdp (Data Pump Export). The equivalent Data Pump command would be:

1expdp system/****** full=y dumpfile=full_scr9.dmp logfile=full_scr9.log \
2  flashback_time=\"to_timestamp\(\'2006-09-13 12:00:00\', \'YYYY-MM-DD HH24:MI:SS\'\)\"

Or more simply using systimestamp for the current moment: flashback_time=systimestamp.

Security Best Practice

The userid=system/****** line in the parfile means the plain-text password sits in a file on disk. Always restrict file permissions on parfiles to the oracle OS user only (chmod 600 full_scr9.par) and avoid placing them in world-readable directories.

References

Posts in this series