Run a Consistent Export with expdp FLASHBACK_TIME

calendar Jul 10, 2026 / · 11 min read · Oracle DBA Data Pump expdp FLASHBACK_TIME Consistent Export Oracle Administration Undo Retention Oracle Backup  ·
Share on: linkedin copy

Run a Consistent Export with expdp FLASHBACK_TIME

Purpose

A Data Pump export that reads one table at 02:00 and the next at 02:15 is not a consistent snapshot — it is a photograph taken in pieces over time. For schemas where rows in one table reference rows in another, that time spread means the export can capture parent rows whose child records were not yet committed, or child records whose parents were deleted during the export window. The dump file looks intact until the import runs and foreign key constraints reveal the mismatch, or an ETL pipeline reports orphaned surrogate keys.

FLASHBACK_TIME solves this by pinning every table read to a single point in time. Data Pump converts the specified timestamp to the closest available System Change Number (SCN), then issues flashback queries against every object — reading each row as it existed at that SCN regardless of when during the export run that particular table is reached. A schema export that runs for two hours will still see a consistent state because undo data preserves the rows as they were at the flashback point.

FLASHBACK_TIME=SYSTIMESTAMP is the most compact form: Oracle evaluates SYSTIMESTAMP at the moment the master control process starts the job, captures the corresponding SCN, and uses that SCN for every subsequent flashback query. The alternative is an explicit timestamp string passed to TO_TIMESTAMP, or for finer control, the FLASHBACK_SCN parameter that bypasses timestamp-to-SCN conversion entirely and delivers an exact, unambiguous consistency point.

Code

 1# Form 1: pin the export to the moment the job starts (most common form)
 2expdp userid=system/password \
 3  DIRECTORY=dpump_dir \
 4  DUMPFILE=myschema_%U.dmp \
 5  LOGFILE=myschema_export.log \
 6  SCHEMAS=myschema \
 7  FLASHBACK_TIME=SYSTIMESTAMP
 8
 9# Form 2: explicit timestamp — quotes and parentheses must be escaped on the OS shell
10expdp userid=system/password \
11  DIRECTORY=dpump_dir \
12  DUMPFILE=myschema_%U.dmp \
13  LOGFILE=myschema_export.log \
14  SCHEMAS=myschema \
15  FLASHBACK_TIME=\"TO_TIMESTAMP\(\'2026-07-10 02:00:00\',\'YYYY-MM-DD HH24:MI:SS\'\)\"
16
17# Form 3: parameter file avoids shell-quoting issues entirely
18# Contents of export.par:
19#   DIRECTORY=dpump_dir
20#   DUMPFILE=myschema_%U.dmp
21#   LOGFILE=myschema_export.log
22#   SCHEMAS=myschema
23#   FLASHBACK_TIME="TO_TIMESTAMP('2026-07-10 02:00:00','YYYY-MM-DD HH24:MI:SS')"
24expdp userid=system/password PARFILE=export.par

 1-- Capture the current SCN before the job if you want to use FLASHBACK_SCN instead
 2SELECT CURRENT_SCN, SYSTIMESTAMP FROM v$database;
 3
 4-- Check the configured undo retention period
 5SHOW PARAMETER undo_retention
 6
 7-- Free space in the active undo tablespace
 8SELECT tablespace_name,
 9       ROUND(SUM(bytes) / 1024 / 1024, 0) AS free_mb
10FROM   dba_free_space
11WHERE  tablespace_name IN (SELECT value
12                           FROM   v$parameter
13                           WHERE  name = 'undo_tablespace')
14GROUP  BY tablespace_name;
15
16-- Recent undo behaviour: look for ORA-01555 incidents and query length
17SELECT TO_CHAR(begin_time, 'YYYY-MM-DD HH24:MI') AS period_start,
18       undoblks,
19       txncount,
20       maxquerylen,
21       ssolderrcnt
22FROM   v$undostat
23ORDER  BY begin_time DESC
24FETCH  FIRST 12 ROWS ONLY;

Code Breakdown

Form 1: FLASHBACK_TIME=SYSTIMESTAMP

This is the standard form for scheduled overnight exports. Oracle evaluates SYSTIMESTAMP the moment the job starts, converts it to the nearest available SCN, and uses that SCN for every flashback read across all worker processes. A table reached two hours into the run is still seen as it was at job-start — the consistency point does not drift with the clock.

The practical advantage is simplicity. No timestamp calculation is required in the calling script, and the consistency point always reflects the actual job-start moment rather than a hardcoded offset that can slip if the cron fires late.

Form 2: Explicit TO_TIMESTAMP

Specifying a precise timestamp is appropriate when the export must align with an external event — a known application quiesce window, a prior log switch, or a timestamp recorded by an application-level procedure. The TO_TIMESTAMP call converts the string to an Oracle timestamp before Data Pump converts it to a SCN.

Shell quoting is the recurring difficulty. On Bash, the parentheses and embedded single quotes inside the argument must be escaped with backslashes, as shown. Ksh and other shells may differ. Passing the wrong quoting causes expdp to silently accept the argument in a malformed state and start an inconsistent export with no error message — which is why Form 3 is the safer production choice whenever a literal timestamp is needed.

Form 3: Parameter file

A parameter file (PARFILE=) passes arguments to expdp without shell interpolation. Inside the file, FLASHBACK_TIME is written exactly as Oracle expects it — no backslash escaping needed. This is the recommended form for production cron jobs. Keep a canonical export.par in the dump directory and review it alongside the cron entry during any audit.

SQL: capturing CURRENT_SCN

SELECT CURRENT_SCN FROM V$DATABASE returns the SCN at the exact instant the query runs. Recording this number before calling expdp and then passing it as FLASHBACK_SCN=<number> is the most precise and auditable consistency approach. A SCN-based export requires no timestamp-to-SCN conversion, avoids the five-minute rounding described in Key Points below, and produces an exact marker you can log for compliance or comparison purposes.

SQL: undo health checks

The undo queries serve two distinct purposes. DBA_FREE_SPACE filtered to the undo tablespace shows available space — the practical ceiling on how much additional undo the export can generate before space pressure begins expiring committed undo prematurely. V$UNDOSTAT records actual undo behaviour over recent 10-minute intervals: maxquerylen is the longest query in seconds during each period, and ssolderrcnt counts ORA-01555 "snapshot too old" errors. Any nonzero ssolderrcnt in the hours around the planned export window is a warning that the current undo configuration may not sustain the flashback queries for the full export duration.

Key Points

  • The timestamp is converted to an SCN internally. Oracle uses TIMESTAMP_TO_SCN to find the SCN corresponding to the flashback time. The mapping is stored at roughly five-minute intervals in an internal table; a specified timestamp may resolve to an SCN up to five minutes earlier than the exact instant. FLASHBACK_SCN avoids this rounding entirely.
  • Undo must cover both the lookback depth and the full export duration. If the flashback point is 30 minutes in the past and the export takes two hours, undo blocks from those 30 pre-job minutes must remain available for the entire two-hour run. UNDO_RETENTION sets the advisory threshold; auto-extend on the undo tablespace prevents hard failures when retention is underestimated.
  • SYSTIMESTAMP is evaluated once, at job start. There is no re-evaluation mid-export. All parallel worker processes share the same SCN from the opening moment.
  • FLASHBACK_TIME and FLASHBACK_SCN are mutually exclusive. Specifying both causes expdp to error before the job begins. Choose one form and stay with it.
  • Parallel exports maintain a single SCN across all workers. Adding PARALLEL=4 does not split or shift the flashback point — every worker reads from the same job-start SCN, which is the point of the consistency guarantee.
  • Transportable tablespace exports are excluded. The TRANSPORTABLE=ALWAYS mode cannot be combined with flashback consistency parameters. The parameter is rejected or silently ignored depending on the Oracle release.

Insights and Best Practices

Prefer FLASHBACK_SCN for programmatic and auditable exports

A scheduled export that needs an auditable consistency record should capture the SCN with SELECT CURRENT_SCN FROM V$DATABASE before calling expdp, write that SCN to the job log, and then pass it as FLASHBACK_SCN. This gives an exact, unambiguous marker. If a question arises later about the precise contents of an export — for compliance, for a data-set comparison, or for an import into a test environment — a recorded SCN answers it exactly. A timestamp answer is approximate by up to five minutes; a SCN answer is exact.

Size UNDO_RETENTION against export duration plus lookback depth

The minimum adequate UNDO_RETENTION for a flashback export is the duration of the longest expected export run plus the age of the oldest flashback point the schedule uses. If a full-schema export takes 90 minutes and the flashback point is set 15 minutes before job start, undo blocks must survive at least 105 minutes from the flashback point. Set UNDO_RETENTION to at least that value with a margin of 20–30 percent for workload variability. Auto-extend on the undo tablespace is the backstop when a larger-than-expected transaction volume expands undo faster than estimated; pair auto-extend with a reasonable maximum datafile size so growth does not consume all available disk.

Review V$UNDOSTAT before introducing a new export schedule

Before making flashback exports part of a production cron, run the V$UNDOSTAT query for the planned export window over several recent weekdays. High undoblks counts during those hours mean the instance generates heavy undo at that time — exactly when the flashback export needs the oldest undo blocks to still be available. Schedule the export for the quietest window in V$UNDOSTAT, or increase undo tablespace size before moving the job to peak load hours.

Validate with a table-only trial before the first full-schema run

Before committing a full-schema flashback export to a cron schedule, run a small TABLES= export using the same FLASHBACK_TIME parameter against the two or three largest, most-active tables in the schema. Check the log for ORA-01555 errors and confirm the dump completes cleanly. A full-schema test run that hits ORA-01555 at hour three of a four-hour window costs more recovery time than a five-minute trial against a single hot table before the schedule goes live.

Use a parameter file for every scheduled job

Shell quoting for explicit FLASHBACK_TIME values is a repeated source of silent misconfiguration. When the parentheses or quotes are wrong, expdp may start without applying the parameter and produce an inconsistent export that shows no error in the log. A parameter file eliminates the quoting variable, keeps the argument readable and reviewable in the file system, and is unchanged by any shell version differences between environments. Treat the export.par file as a deployable artefact alongside the cron entry.

Confirm flashback mode in the export log before trusting the dump

The expdp log records whether flashback consistency was applied. Look for an explicit notice that confirms the SCN or the flashback time was resolved at job start. A log that shows only the Master table successfully loaded/unloaded line without a flashback confirmation means the parameter was not accepted. Check the parameter spelling and quoting, then re-run before treating the dump as consistent.

When to Use This

  • Overnight schema exports of multi-table schemas where referential integrity must hold after import.
  • Exports that feed a downstream data warehouse, where a consistent snapshot at a known time is required for correct incremental loads.
  • Pre-migration safety exports before a schema change, where the dump must be importable back to a defined state if the migration fails.
  • Cross-schema exports where two schemas share data through database links or synonyms that reference each other.
  • Any export where the import will run with CONSTRAINTS=y and foreign key violations would indicate an inconsistent source.
  • Environments using PARALLEL=n where, without flashback consistency, worker processes reading different tables at different clock times would produce an incoherent dump.

Troubleshooting Common Issues

ORA-01555: snapshot too old is the primary failure mode. It means the undo blocks needed to reconstruct a row as it existed at the flashback SCN have been overwritten. The immediate fix is to re-run the export with a more recent flashback point. The permanent fix is to increase UNDO_RETENTION, and if the undo tablespace is near its size limit, add space or enable auto-extend. On a tablespace that is also approaching the maximum datafile ceiling, adding a second datafile is the fastest path to relief.

ORA-08180: no snapshot found based on specified time occurs when the timestamp supplied to FLASHBACK_TIME is older than the earliest entry in the internal SCN-to-timestamp mapping table. Oracle can only convert a timestamp to a SCN if that timestamp falls within the mapping retention window. If the requested time is too far in the past, the conversion fails before the export begins. Resolve this by using FLASHBACK_SCN with a known SCN captured at the required moment, or by specifying a less distant flashback time.

Silent loss of the flashback parameter — the export runs but the log does not confirm flashback mode — almost always traces to shell quoting. The symptom is a successful-looking export that was not actually consistent. Re-run using PARFILE=export.par with the FLASHBACK_TIME line inside the parameter file, and confirm the log shows the expected SCN or flashback-time notice before treating the dump as reliable.

Export runs but is slower than expected — flashback queries reconstruct historical row versions by reading undo segments, which adds I/O beyond the normal segment scan. On a system with heavy undo activity, reconstruction cost is proportional to how far back the flashback point sits and how many rows changed in that window. Using FLASHBACK_TIME=SYSTIMESTAMP minimises this overhead because the consistency point is the current moment and almost no undo reconstruction is needed for freshly started scans. Moving an explicit timestamp flashback point closer to the job-start time reduces reconstruction work proportionally.

References

Posts in this series