Copy command to copy data between a remote database, a local database, or an Oracle database and a non-Oracle Database

Question 2 SQL * Plus Copy command
Problem description
In SQL * Plus, how does one implement data replication between different tables, whether local or remote?
Problem Analysis
The Copy command in SQL * Plus allows you to copy data between a remote database, a local database, or an Oracle database and a non-Oracle database. Its performance is the same as that of import/export.
Basic command format of copy:

Copy {from source_database | to destination_database} {Append | create | insert | replace} destination_table [(Column,...)] Using <source_select_statement>

The database connection format is as follows:
Username/password \] @ connect_identifier
During data replication, the supported data types are char, date, long, number, and varchar2. Principles 3-9 are shown.

Figure 3-9 SQL * Plus copy schematic

The SQL * Plus Copy command can replicate data between different databases and between tables in different modes in the same database.
• Copy data from a remote database to a local database.
• Copy data from a local database (default) to a remote database.
• Copy data from one remote database to another.
Generally, the copy command is used to copy data between an Oracle database and a non-Oracle database. If you copy data between Oracle databases, use the create table as and insert SQL commands.
• There are four types of control methods for the target table: replace, create, insert, and append.
• The replace clause specifies the name of the table to be created. If the target table already exists, delete it and replace it with a table containing the copied data. If not, the target table is created.
• Use the create clause to avoid overwriting existing tables. If the target table already exists, copy reports an error. If not, create the target table.
• Insert data to an existing table.
Insert the queried rows to the target table. If the target table does not exist, copy returns an error. When insert is used, the using clause must select the corresponding columns for each column in the target table.
• Append inserts the queried rows into the target table. If not, create the target table and insert it.
Answer
Note the following:

1) copy is an SQL * Plus command, not an SQL command. No extra points are required at the end of the statement;

2) because most copy commands are relatively long, the end of each line must have a hyphen (-) at the end of each line at the time of the branch, and the last line is not added.
The procedure is as follows.
Step 1: Use the using clause to specify a query, and copy the query result data to the employee table in the current mode of the local database. For example:

Copy from HR/HRD @ rensh- Replace employee- Using select last_name, salary- From emp_details_view- Where department_id = 30

Step 2: Use create to copy data from a remote database to a local database.

Copy from HR/<your_password> @ bostondb- Create empcopy- Using select * from HR

Step 3: Copy data for other users.

Copy from HR/hr @ dbora- Create job- Using select * From renbs.jobs

Connect the user HR to the database dbora and copy the jobs table in renbs mode to the current local database.
Step 4: Copy tables in one mode to another mode in the same database. The user password and Database Service name must also be specified.

Copy from HR/password @ mydatabase- Insert employee_copy2- Using select * From employee_copy

Step 5: retabulation in the same remote database. The keywords from and to must contain the same user name and Database Service name.

copy from HR/password @ daliandb- to HR/password @ daliandb- insert employee_copy2 - using select * From employee_copy