Oracle exp: Export Database with 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 — FLASHBACK_SCN names the SCN directly; FLASHBACK_TIME lets Export find the SCN that most closely matches a timestamp.
  • 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.
  • Timestamp format — The value can be any format that DBMS_FLASHBACK.ENABLE_AT_TIME accepts; the plain quoted string 'YYYY-MM-DD HH24:MI:SS' is also accepted for backward compatibility.
  • Legacy tool note — The exp utility is the original export tool (pre-Data Pump). Original Export is desupported for general use as of Oracle Database 11g; its only supported use in 11g and later is backward migration of XMLType data to 10.2 or earlier. Use expdp/impdp instead.

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

Both forms are valid on the command line and in a parfile: FLASHBACK_TIME accepts any format that DBMS_FLASHBACK.ENABLE_AT_TIME accepts, including TO_TIMESTAMP(...), and the plain quoted string is kept for backward compatibility. Shell quoting is often easier inside a parfile.

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