Mostly Making oracle easy, mostly:
Search for:
In SQL Developer

New Feature: Drag and Drop Object To Copy Them to Another Oracle Database

2 Mins Read

In both v4.1.5 and v4.2, you can now select objects from your database tree, and drag them to another connection to have them copied.

The source can be:

  • a MySQL, DB2, SQL Server, Sybase, Terradata, or other 3rd party supported RDBMS
  • an Oracle Database

The destination can be:

  • an on-premise Oracle Databaes
  • an Oracle Database running in the Oracle Cloud
You can drag the entire node up instead of selecting all the tables if you want everything.
You can drag the entire node up instead of selecting all the tables if you want everything.

You have a few options:

Keep the data, don't keep the data, just the data, just the object, etc.
Keep the data, don’t keep the data, just the data, just the object, etc.

For tables, we’ll also grab it’s indexes, triggers, and constraints.

Make sure you check this list to know exactly what you're getting.
Make sure you check this list to know exactly what you’re getting.

Click OK, and we’ll go do the work.

When it’s done, you’ll get a log, see the end of the post for an example.

Why, When, Where…

Ad-hoc, quick and dirty…these are the phrases you should associate with this feature. The Tools > Export & Copy features are much more robust in terms of options and control. But if you are working on your app in our Exadata Express Database Cloud Service and you just want to quickly copy a few tables up, this will be faster than the click-click-click-click-click process the wizards introduce.

Sample Log

Dragged Objects:
hr.HR.TABLE.DAVE
hr.HR.TABLE.IPSUM
hr.HR.TABLE.TABLE_BLOG_QUESTION
hr.HR.TABLE.UMLAUT
DROP Target: demo
Copy DDL: Yes
Do NOT REPLACE Existing Objects
Copy DATA: Yes
Append Existing Objects
--- START --------------------------------------------------------------------
SET define off;
 
--------------------------------------------------------
--  DDL for Table DAVE
--------------------------------------------------------
 
  CREATE TABLE "DAVE" ("SAK_AID_ELIG" NUMBER, "CDE_AID_ELIG_REASON" VARCHAR2(3 BYTE), "DTE_ADDED" NUMBER) ;
 
   COMMENT ON TABLE "DAVE"  IS 'Test case for some guy named Dave';
 
TABLE "DAVE" created.
 
 
Comment created.
 
 
--- END --------------------------------------------------------------------
 
--- START --------------------------------------------------------------------
SET define off;
 
--------------------------------------------------------
--  DDL for Table IPSUM
--------------------------------------------------------
 
  CREATE TABLE "IPSUM" ("STUFF" VARCHAR2(4000 BYTE)) ;
 
TABLE "IPSUM" created.
 
 
--- END --------------------------------------------------------------------
 
--- START --------------------------------------------------------------------
SET define off;
 
--------------------------------------------------------
--  DDL for Table TABLE_BLOG_QUESTION
--------------------------------------------------------
 
  CREATE TABLE "TABLE_BLOG_QUESTION" ("COLUMN_ONE" VARCHAR2(250 CHAR)) ;
 
TABLE "TABLE_BLOG_QUESTION" created.
 
 
--- END --------------------------------------------------------------------
 
--- START --------------------------------------------------------------------
SET define off;
 
--------------------------------------------------------
--  DDL for Table UMLAUT
--------------------------------------------------------
 
  CREATE TABLE "UMLAUT" ("CHARACTERS" VARCHAR2(2 BYTE)) ;
 
TABLE "UMLAUT" created.
 
 
--- END --------------------------------------------------------------------
 
--- START --------------------------------------------------------------------
Moving DATA FOR object DAVE
 
INSERT 2 ROWS INTO DAVE IN 43 milliseconds
--- END --------------------------------------------------------------------
 
--- START --------------------------------------------------------------------
Moving DATA FOR object IPSUM
 
INSERT 1 ROWS INTO IPSUM IN 14 milliseconds
--- END --------------------------------------------------------------------
 
--- START --------------------------------------------------------------------
Moving DATA FOR object TABLE_BLOG_QUESTION
 
INSERT 0 ROWS INTO TABLE_BLOG_QUESTION IN 6 milliseconds
--- END --------------------------------------------------------------------
 
--- START --------------------------------------------------------------------
Moving DATA FOR object UMLAUT
 
INSERT 4 ROWS INTO UMLAUT IN 14 milliseconds
--- END --------------------------------------------------------------------
 
--- START --------------------------------------------------------------------
SET define off;
 
--------------------------------------------------------
--  DDL for Index COLUMN_ONE_UK
--------------------------------------------------------
 
  CREATE UNIQUE INDEX "COLUMN_ONE_UK" ON "TABLE_BLOG_QUESTION" ("COLUMN_ONE") ;
 
INDEX "COLUMN_ONE_UK" created.
 
 
--- END --------------------------------------------------------------------
 
--- START --------------------------------------------------------------------
SET define off;
 
--------------------------------------------------------
--  Constraints for Table TABLE_BLOG_QUESTION
--------------------------------------------------------
 
  ALTER TABLE "TABLE_BLOG_QUESTION" ADD CONSTRAINT "COLUMN_ONE_UK" UNIQUE ("COLUMN_ONE") USING INDEX  ENABLE;
  ALTER TABLE "TABLE_BLOG_QUESTION" ADD CONSTRAINT "UPPER_ONLY" CHECK ( column_one = UPPER(column_one)) ENABLE;
  ALTER TABLE "TABLE_BLOG_QUESTION" ADD CONSTRAINT "NOT_LOWER" CHECK ( column_one <> LOWER(column_one)) ENABLE;
 
TABLE "TABLE_BLOG_QUESTION" altered.
 
 
TABLE "TABLE_BLOG_QUESTION" altered.
 
 
TABLE "TABLE_BLOG_QUESTION" altered.
 
 
--- END --------------------------------------------------------------------
Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Related Posts

15 Comments

  1. Sameer Reply

    Hi Jeff,

    While this feature is probably meant to copy objects between Oracle and Non-Oracle Databases for quick migration, our team usually has a need to quickly copy objects between two Schemas within the same Oracle database.

    Having established connection to both SOURCE schema and TARGET schema within my Oracle database (11.2.0.4 Ent), when I drag & drop, say TABLES from SOURCE schema into TARGET schema, it would be nice to have an option to un-check the migration of Indexes, Triggers and Constraints. Basically, we just need a way to quickly copy the TABLE and its data or simply copy an empty TABLE.

    • thatjeffsmith Post Author

      Tools > Database Copy.

      Uncheck everything but ‘Tables.’ Pick your tables. It’ll do just what you want.

    • Sameer

      Thanks Jeff,

      I used Tools –> Database Copy and upon selecting Source Connection to SOURCE schema and Destination Connection to TARGET schema, upon clicking on Next button, I get a pop-up window with the following error:-

      Validation Failed
      Source and Destination are same Database nothing to Copy.

      Not sure if this is a bug or not?

    • thatjeffsmith Post Author

      you need a connection for SchemaA and a connection to SchemaB.

      They can be on the same database, I just tried this in 4.2.0.17

    • Sameer

      Yup, I’m on the same version 4.2.0.17.089 and when I select the 2 schemas and click on Next, I get that error.

    • thatjeffsmith Post Author

      don’t know what you mean by ‘select the 2 schemas’…you’re selecting CONNECTIONS, right? And each connection is a different schema in the same database?

    • Sameer

      Well, for simplicity reasons, I have 2 saved connections:-

      1. Schema_A: which I login with my username & pwd.

      2. Schema_B: which I login using PROXY_CONNECT using my username[Schema_B] and pwd.

      Could that be an issue?

    • Sameer

      In that case, the tool should allow us to connect to any 2 connections within the same Oracle Database whether using PROXY_CONNECT privileges or DIRECT connect privileges.

      It would not make sense to create unique ‘N’ usernames and passwords for ‘N’ number of connections/schemas in the same database, hence, the PROXY_CONNECT flexibility it provides.

      How can we resolve this? File a bug? File a Feature Enhancement request?

    • thatjeffsmith Post Author

      It’s an ER, for now if you want to copy objects/data between two schemas in the same DB using the wizard, create a connection for each. Or use the drag and drop…or use a CREATE TABLE AS SELECT…

  2. remduv Reply

    Hello Jeff

    As we are talking about “drag and drop” there is one thing I would like to be able to do in SQL Developer using it.
    Sorry if this is not the appropriate place to talk about it but I’m pretty sure you will read this comment 🙂
    So .. Could this be possible to take a connection (with the mouse) in the “Connections” panel and drop it on the dropdown list used to switch connections, so that SQL Dev switch to this connection.
    The list of connections is pretty huge on my PC so this would be very convenient to me if this is made possible (it’s easier to find a connection in the ad hoc pannel as they could be grouped by folders)

    Thanks to you and your team for the good job done by the way.

  3. Walter Myers Reply

    This is a cool feature. I’ve tested the copy from a Sql Server source to Oracle (XE) and it worked. However, a DB2 (LUW) source does not work, even though the log states it completed successfully.

    Dragged Objects:
    [email protected]
    Drop Target: xe
    Copy DDL: Yes
    Do Not Replace Existing Objects
    Copy Data: Yes
    Append Existing Objects
    Task Succeeded.

    1 tables copied.
    Copy To Oracle: ZIP Finished

    On the Logging Page – Log I get the following error:

    SEVERE 359 419024 oracle.dbtools.migration.copy.BridgeTableDef Issue running BRIDGE command

    I thought I would let you know.

  4. Tony Johnson Reply

    What about tablespace assignments ? Go into whatever is the default tablespace ?

Write A Comment