Data replication between Oracle databases-COPY command in SQLPlus

Data replication between Oracle databases-the COPY command in SQLPlus can replicate data between different Oracle databases and replicate data in the same database, its performance is the same as that of import/export. Schematic diagram: According to 9i, the Copy command will not be supported in the future, but in fact Oracle 11G still supports the Copy command, but it is not written into the 11g document. In the future, 12C does not know that it is not supported yet. Copy also has obvious disadvantages. Copy only supports five data types: Char, Date, Long, Number, Varchar2, and 8i. New Data Types of Oracle databases are not supported. Usage: 1. first, ensure that the connection information of the source and target instances is recorded in TNS; 2. run SQL * Plus (no need to connect to a specific Instance) sqlplus/nolog 3. run the Copy command syntax:

COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]USING querywhere database has the following syntax:username[/password]@connect_identifierCopies data from a query to a table in a local or remote database. COPY supports the following datatypes:CHARDATELONGNUMBERVARCHAR2Example:copy from user1/password1@source_instance to user2/password2@dest_instance create emp2 using select * from emp;  

 

The preceding Command copies the emp data from source_instance to the emp2 table in dest_instance. For database replication, you can also use the Copy command, Example:

Copy from scott/password @ ptian to scott/password @ ptian create emp2 using select * from emp; this is a bit similar to create table empx AS (SELECT * FROM emp );

 

According to Tom, the efficiency of Copy may be higher than that of create table... AS... (you can also use Create table for inter-Database Replication... as, through database Link), because Copy Copies data from an Instance to sqlplus, and then inserts another Instance from sqlplus. The create table... AS... directly inserts data from a database into the second database, so the efficiency is higher. Four modes of the Copy command * 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 into 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. Other Instructions: if the copy command is long, you can add a hyphen (-) at the end of each line at the time of the branch without adding the last line.

copy from scott/welcome@ptian -  to scott/welcome@ptian-  create empy-  using select * from emp-  where rownum = 1