Data replication between Oracle databases-copy command in Sqlplus

The copy command enables the replication of data between different Oracle databases and enables data replication of the same database with the same performance and import/export.

According to the 9i document, the Copy command will not be supported in the future, but in fact, Oracle 11g still supports the copy command, but the document is not written in 11g, the future 12C do not know that support is not supported.

Copy also has the obvious disadvantage that copy only supports five data types, which are not supported by the new data type of Oracle database after char,date,long,number,varchar2,8i.

How to use:

1. First ensure that the connection information for source instance and target instance is recorded in TNS;

2. Run SQL * Plus (no need to connect to specific instance)

Sqlplus/nolog

3. Run the Copy command

Syntax for the copy command:

COPY {from database | to database | From database to database} {append| create| Insert| REPLACE} destination_table [(Column, Column, column, ...)]
USING Query

where database has the following syntax:

Username[/password] @connect_identifier

Copies data from a query to a table in a local or remote database. Copysupports the following datatypes:

CHAR

DATE

LONG

Number

VARCHAR2

Example:

1. Copy from User1/[email protected]_instance to User2/[email protected]_instance create EMP2 using SELECT * from EMP;

The command above will copy the EMP data from the source_instance to the EMP2 table in the dest_instance.

Replication within the database, or you can use the Copy command, Example:

1. Copy from Scott/[email protected] to Scott/[email protected] Create EMP2 using SELECT * from EMP;

This is somewhat similar to the Create TABLE empx as (SELECT * from EMP);
According to Tom, Copy might be more efficient than the Create TABLE ... As ... (Replication between databases can also use Create table...as, via database Link), because copy is copying data from one instance to sqlplus and then inserting another instance from Sqlplus.

and create TABLE ... As ... is to insert data from one database directly into the second database, so the efficiency is higher.

Copy Four modes of the command

* The REPLACE clause specifies the name of the table being created. If the target table already exists, it is removed and replaced with the table that contains the copied data. If it does not exist, the target table is created.
* Use the CREATE clause to avoid overwriting tables that already exist. If the target table already exists, copy reports an error and, if it does not exist, creates the target table.
* Insert inserts data into a table that already exists. Inserts the queried row into the destination table, and copy returns an error if the target table does not exist. When you use INSERT, the USING clause must select the corresponding column for each column of the target table.
* Append is the row to insert the query into the target table. If it does not exist, the target table is created and inserted.

Other Notes

If the copy command is long, you must have a continuation character (-) at the end of each line at the time of the branch, without the last line.

1. Copy from Scott/[email protected]-
2. to Scott/[email protected]
3. Create empy-
4. Using SELECT * FROM emp-
5. where rownum = 1

Data replication between Oracle databases-copy command in Sqlplus