SQL Developer: Moving Schema Objects Between Databases with Database Cart

Move a few tables without writing DDL by hand

You need to promote a handful of tables from an Oracle development database to a test database. The change is too small for a full Data Pump export, but writing CREATE TABLE and INSERT statements by hand is error-prone. SQL Developer's Database Cart is built for this situation: collect schema objects from one connection, generate a deployment script, review it, and run it against another connection.

The cart is a client-side design and review aid, not a substitute for server-side bulk tools such as Oracle Data Pump. It works well for small, hand-picked object moves, but you must review dependencies and execution order yourself.

How the cart works across connections

SQL Developer can keep multiple database connections open at once. In the Connections navigator, you expand a source schema, drag objects such as tables, views, or procedures into the Database Cart panel, then ask SQL Developer to export them. The export can emit DDL for the object definitions and, optionally, INSERT statements for table data. The generated script can be saved to a file or opened directly in a SQL Worksheet for peer review before execution.

Worked example: development to test

1. Open SQL Developer and create two connections: source_conn (development) and target_conn (test).
2. In the Connections navigator, expand the source schema, locate a table such as customer_address, and drag it into the Database Cart.
3. Open the cart, choose Export or Generate Deployment Script (menu names vary by release), and select whether to include table data as INSERT statements.
4. Save the script to a file or open it in a SQL Worksheet. Review the output for CREATE TABLE, constraints, and INSERT blocks.
5. Open a second SQL Worksheet connected to target_conn, paste the script, and execute it.
6. Verify the result with queries such as:

DESCRIBE customer_address;
SELECT COUNT(*) FROM customer_address;

Compare the row count and column list against the source database. If the counts differ, inspect the script for failed INSERT statements or missing data filters.

Required permissions on the target typically include CREATE TABLE and INSERT into the target schema; running as the schema owner is the simplest safe option. Be aware that Oracle DDL statements issue an implicit commit, so a partially executed script can leave objects behind. Running an INSERT script twice can duplicate rows unless the script or table constraints prevent it.

Limitations to check before you rely on the cart

• Dependent objects may be omitted. Indexes, grants, referenced sequences, and other dependent objects are not always included unless explicitly added to the cart. A missing reference can cause target deployment to fail.
• Load order is not automatic. The cart does not resolve foreign-key ordering. Parent tables must be scripted and loaded before child tables, which may require manual reordering.
• Large volumes are slow. Exporting large tables as INSERT statements is client-intensive and slow. Use Oracle Data Pump for high-volume migrations.
• Version coverage varies. Newer Oracle data types or Autonomous Database-specific objects may not export correctly from older SQL Developer releases.

Rollback and final check

Because executing generated DDL changes the target database and Oracle DDL auto-commits, there is no single transaction-level undo. To roll back manually, drop the objects the script created and remove any inserted rows, or restore the target schema from a backup taken before the run.

Use the Database Cart for small, reviewed schema moves between connections. For bulk data, scheduled refreshes, or complex dependency graphs, use Data Pump or DBMS_METADATA instead. Always open the generated script in a worksheet and review it before executing it against the target.