Migrating Schema Data with SQL Developer's Export Wizard: Insert Scripts That Actually Work

The problem: moving data without a database link

You need to copy a handful of tables from a production schema into a test environment, but the two databases cannot see each other. Creating a database link is blocked by network policy, and Data Pump is overkill for a few megabytes of reference data. The built‑in Export wizard in Oracle SQL Developer can turn table rows into a plain‑text .sql file full of INSERT statements—something you can run anywhere you have a SQL Worksheet.

Thesis: the wizard is a viable migration path when you respect its limits

If you filter the data, choose the right insert flavour, and handle constraints before you run the script, the generated file is a portable, version‑controlled artifact. The wizard is not a silver bullet for terabyte‑scale loads, but for reference tables, lookup data, and small transactional snapshots it saves hours of manual scripting.

1. Preparing the export

1. Open the Connections navigator, expand the source schema, and right‑click the target table (e.g., ORDERS). Choose Export.
2. In the Export Wizard dialog set:
   • Format: insert (or insert ignore if the target may already contain the same primary keys).
   • Where clause: add a filter such as ORDER_DATE >= DATE '2025-01-01' to keep the file small.
   • Output: Single file (easier to version) or Separate files per table if you have many tables and want parallel execution.
   • Encoding: UTF‑8 (default) – matches most modern editors.
3. Click Next → Finish. SQL Developer writes export_ORDERS.sql to the folder you selected.

Permissions: you need SELECT on the source table and write access to the chosen filesystem location. No DBA role is required.

2. Handling constraints and large objects

Before you run the generated script in the target schema:

• Disable foreign‑key constraints that reference the imported tables:

  ALTER TABLE target_schema.ORDER_ITEMS DISABLE CONSTRAINT fk_order_items_orders;

  Run this in a SQL Worksheet connected as the target schema owner (or a user with ALTER ANY TABLE).
• CLOB/BLOB columns are emitted as literal hex strings in the INSERT. For anything larger than a few kilobytes the statement exceeds the 32 KB SQL*Plus limit and will error. Export those columns separately (e.g., via Data Pump or a PL/SQL loader) and skip them in the wizard by unchecking the column in the Columns tab.
• File size: a 200 MB .sql file will choke most editors and the SQL Worksheet parser. If the export exceeds ~50 MB, split by table or add a tighter WHERE clause.

3. Worked example: moving recent orders to a test schema

Assume the source schema PROD_APP and target schema TEST_APP both contain an ORDERS table with identical DDL.

1. In SQL Developer connected to PROD_APP, right‑click ORDERS → Export.
2. Set Format = insert, Where clause = ORDER_DATE >= DATE '2025-06-01', Output = Single file, file name orders_recent.sql.
3. Finish the wizard. Open orders_recent.sql – you should see a header comment, a SET DEFINE OFF, and a series of statements like:

   INSERT INTO ORDERS (ORDER_ID, CUST_ID, ORDER_DATE, TOTAL) VALUES (1001, 42, DATE '2025-06-15', 123.45);

4. Connect to the target database as TEST_APP. In a new Worksheet run:

   -- 1. Disable dependent FKs
   ALTER TABLE ORDER_ITEMS DISABLE CONSTRAINT fk_order_items_orders;
   
   -- 2. Run the export script
   @/path/to/orders_recent.sql
   
   -- 3. Re‑enable FKs
   ALTER TABLE ORDER_ITEMS ENABLE CONSTRAINT fk_order_items_orders;

5. Verify row count:

   SELECT COUNT(*) FROM ORDERS WHERE ORDER_DATE >= DATE '2025-06-01';

   The result should match the source count.

Risk note: If the target already holds rows with the same ORDER_ID, the plain INSERT will raise ORA-00001. Switch the wizard to insert ignore (adds /*+ IGNORE_ROW_ON_DUPKEY_INDEX */ hint) or delete/merge beforehand.

Trade‑offs and limitations

Aspect	Benefit	Limitation
Portability	Plain SQL runs on any Oracle version ≥ 11g	No parallelism; single‑threaded execution
Version control	Script can be checked into Git	Diff noise for large data sets
LOB handling	Works for tiny CLOB/BLOB	Fails silently for >32 KB values
Constraint safety	Explicit disable/enable gives control	Manual step – easy to forget

Actionable closing checklist

• ✅ Filter with a WHERE clause to keep the script < 50 MB.
• ✅ Choose insert ignore when primary‑key collisions are possible.
• ✅ Disable all inbound foreign keys on the target before running the script.
• ✅ Exclude LOB columns from the wizard; load them separately.
• ✅ After execution, run a row‑count comparison and a quick SELECT * FROM table WHERE rownum <= 5 sanity check.
• ✅ Re‑enable constraints and gather statistics (DBMS_STATS.GATHER_TABLE_STATS) so the optimizer sees the new data.

Follow the list and you’ll have a repeatable, auditable migration that works even when the two databases cannot talk to each other.