Direct Answer
There is no fixed row-count or byte threshold at which Data Pump becomes mandatory. The Database Export wizard remains usable for large schemas, but its practical ceiling is set by the client JVM heap and network round-trip latency, not by a hard Oracle limit. If you can create or access a Data Pump directory object, that path is generally faster and more stable for multi-gigabyte recoveries; if server-side configuration is locked down, SQL scripts are still viable provided you manage client memory and foreign key ordering.
Confirmed Behavior
SQL Developer's Database Export wizard generates INSERT statements on the client. Memory consumption therefore depends on the client JVM heap and the volume of rows buffered before the script is written. Data Pump (expdp/impdp) runs inside the database server, uses the direct-path API where possible, and writes binary dump files to an OS directory referenced by a DATABASE DIRECTORY object.
Likely Explanation
Memory overhead does not scale in a simple linear formula. It is influenced by row width, LOB columns, index and constraint metadata, and SQL Developer's internal buffering strategy. The failure mode you are most likely to see is a client-side OutOfMemoryError rather than a database error, which means the threshold is effectively your configured heap size, not a universal table-size limit.
Steps for This Scenario
- Check client heap. In SQL Developer, open About or inspect the
sqldeveloper.conf file for the -Xmx value. If it is at the default, raise it before attempting a large export.
- Check directory access. Run
SELECT owner, directory_name, directory_path FROM all_directories; to see whether a usable directory object exists and whether your user has read/write privileges on it. If yes, Data Pump is the lower-risk choice.
- If restricted to SQL scripts, export in chunks. Use the wizard's row-range or table-filter options to split the output into smaller files rather than one monolithic script.
- Test a small subset first. Export and re-import a single table or a few thousand rows to measure generation time and restore time before committing to the full schema.
Managing Foreign Key Dependencies in SQL Scripts
Binary Data Pump imports handle constraint dependencies automatically. SQL scripts do not. You have two reliable options:
- Disable, load, re-enable. Generate a pre-script that disables all foreign key constraints, run the table inserts, then run a post-script that re-enables them. This avoids ordering problems entirely.
- Topological ordering. Export parent tables before child tables. SQL Developer's dependency ordering helps, but circular references can still break it. In that case, fall back to the disable/re-enable method.
When re-enabling constraints, expect validation time proportional to the child table size. For very large tables, consider leaving constraints in NOVALIDATE state if you are confident in the source data integrity.
One Missing Diagnostic
Do you have CREATE ANY DIRECTORY privilege, or can a DBA create a directory object and grant you access? If the answer is no, the recommendation shifts firmly toward tuned SQL script exports with chunking and constraint deferral. If yes, Data Pump should be preferred for datasets above a few gigabytes.
sqldeveloper