How to Use SQL Developer's Database Cart for Schema and Data Export

The Problem

When migrating schemas or refreshing environments, manually scripting DDL and data exports is error-prone and time-consuming. SQL Developer's Database Cart provides a GUI-driven way to assemble and export schema objects with their data into deployable bundles.

Prerequisites

• Oracle SQL Developer 4.0 or later (tested on 22.x)
• Database connection with SELECT on DBA_METADATA and SELECT privileges on source objects
• Write access to local filesystem for export destination

Assemble Objects in the Cart

1. Open the Cart: Navigate to View > Cart. The cart panel appears at the bottom of the SQL Developer window.
2. Drag objects: From the Connections navigator, drag tables, views, indexes, or other schema objects directly into the cart. Each object shows as a row with checkboxes for DDL and data options.
3. Name the cart: Click the cart's Actions menu and choose Save Cart to give it a descriptive name. This persists the cart for reuse across sessions.

Configure Export Options

Click Export in the cart toolbar. The export wizard offers critical choices:

• Format: Choose SQL File for DDL, Insert Statements for small tables, or SQL*Loader for large data volumes.
• Include: Check Storage Clause, Grant, and Constraints to reproduce objects fully. Uncheck for development copies where storage details differ.
• Single File: Consolidate all objects into one script, or export separate files per object for modular deployment.

Export and Verify

1. Select destination folder: Choose a local directory. SQL Developer creates DDL files and, for data exports, either .sql insert files or .ctl/.dat loader files.
2. Run the export: Click Next through the wizard. Monitor the log window for any metadata extraction errors.
3. Check output: Navigate to the export folder. Confirm each dragged object has a corresponding .sql file. For data, verify .ctl files exist alongside .dat files when using loader format.

Deploy to Target Environment

Use the generated scripts with SQLcl or SQL*Plus:

sql -s username/password@target_db @ddl_export.sql

For SQL*Loader data:

sqlldr userid=username/password control=table_name.ctl log=table_name.log

Validation Steps

• Object counts: After deployment, run SELECT COUNT(*) FROM USER_TABLES; and compare with source.
• Data validation: For critical tables, compare row counts and key column sums between source and target.
• Compilation check: Query SELECT OBJECT_TYPE, COUNT(*) FROM USER_ERRORS GROUP BY OBJECT_TYPE; to find invalid objects. Recompile with ALTER ... COMPILE; as needed.

Recovery Options

If deployment fails:

• Restore the target schema from backup before retrying.
• Edit the exported .sql files to comment out problematic objects and re-run.
• Use Tools > Database Diff to compare source and target, identifying missing or mismatched objects.

Important Considerations

• Character sets: Ensure source and target database character sets are compatible. Mismatches can corrupt Unicode data in loader files.
• Large data: Avoid INSERT statements for tables with millions of rows. SQL*Loader is faster and produces smaller files.
• Privileges: The Cart may not export all grant statements. Manually verify and reapply critical grants.
• Editioned objects: Objects in editions may export with incomplete metadata. Review generated DDL carefully.

Limitations

Older SQL Developer versions lack some export options. Queue tables, some materialized view logs, and fine-grained auditing rules may not export completely. Always validate against the source schema after deployment.