COPY command in oracle with examples- copy data between two remote databases

The COPY command in Oracle is a feature of SQL*Plus that allows you to copy data between tables in different databases. This can be particularly useful for moving data from one database to another or duplicating tables within the same database.

Syntax of the COPY Command

The basic syntax of the COPY command is:

COPY FROM username1/password1@database1
     TO username2/password2@database2
     {APPEND|CREATE|INSERT|REPLACE} destination_table (column1, column2, ...)
     USING query;

• FROM username1/password1@database1: Specifies the source database connection.
• TO username2/password2@database2: Specifies the target database connection.
• {APPEND|CREATE|INSERT|REPLACE}: Determines how the data should be copied to the destination table.
  • APPEND: Adds the copied data to the existing data in the destination table.
  • CREATE: Creates a new table and inserts the copied data.
  • INSERT: Inserts the copied data into the destination table.
  • REPLACE: Replaces the existing data in the destination table.
• destination_table: The name of the table in the target database.
• (column1, column2, ...): List of columns to be copied.
• USING query: The SQL query that retrieves the data to be copied.

Examples

1. Copying Data Between Two Databases

Assume you have a table employees in a source database and you want to copy it to a target database.

COPY FROM scott/tiger@source_db
     TO scott/tiger@target_db
     CREATE employees_copy
     USING SELECT * FROM employees;

This command connects to source_db using the scott/tiger credentials, copies all data from the employees table, and creates a new table employees_copy in the target_db.

2. Appending Data to an Existing Table

If you want to append data from the source table to an existing table in the target database:

COPY FROM scott/tiger@source_db
     TO scott/tiger@target_db
     APPEND employees_copy
     USING SELECT * FROM employees

Assume you have already created employees_copy in the target database, and now you want to append more data from the employees table in the source database:

COPY FROM scott/tiger@source_db
     TO scott/tiger@target_db
     APPEND employees_copy
     USING SELECT * FROM employees;

This command appends data from the employees table in the source_db to the existing employees_copy table in the target_db.

3. Inserting Data into an Existing Table

If you want to insert data into an existing table in the target database:

COPY FROM scott/tiger@source_db
     TO scott/tiger@target_db
     INSERT INTO employees_copy (employee_id, first_name, last_name, department_id, salary)
     USING SELECT employee_id, first_name, last_name, department_id, salary FROM employees;

This command inserts the selected columns from the employees table in the source_db into the employees_copy table in the target_db.

4. Replacing Data in an Existing Table

If you want to replace the data in an existing table in the target database:

COPY FROM scott/tiger@source_db
     TO scott/tiger@target_db
     REPLACE employees_copy
     USING SELECT * FROM employees;

This command replaces the existing data in the employees_copy table in the target_db with the data from the employees table in the source_db.

Considerations

• Performance: The COPY command can be slow for large datasets because it moves data row by row. For large data migrations, consider using Oracle Data Pump or other ETL tools.
• Security: Ensure that credentials are handled securely, especially when using the COPY command in scripts.
• SQL*Plus Environment: The COPY command is a feature of SQL*Plus and is not available directly in PL/SQL or other Oracle clients.
• Data Types: Ensure that the data types in the source and destination tables are compatible to avoid errors during the copy operation.

Using SQL*Plus to Execute the COPY Command

Here is an example of how you might use the COPY command in a SQL*Plus session:

1. Start SQL*Plus:

   sqlplus

2. Execute the COPY Command:

   COPY FROM scott/tiger@source_db
        TO scott/tiger@target_db
        CREATE employees_copy
        USING SELECT * FROM employees;
   

This command will prompt SQL*Plus to connect to both the source and target databases, execute the specified query on the source database, and then copy the data to the target database according to the specified method (CREATE in this case).

Conclusion

The COPY command in Oracle SQL*Plus is a convenient way to move data between tables in different databases. It's useful for small to medium data migration tasks or for duplicating tables across different environments. For larger datasets or more complex migrations, other tools like Oracle Data Pump or custom ETL processes may be more efficient and suitable.