SQL * Plus copy command to process Mass Data Replication

For data replication at the database TABLE level, the most common method is create table as (CTAS. In fact, the copy command under SQL * Plus can do the same job, and it is better, and the performance is also better. What's more, data replication across platforms and heterogeneous databases is supported. The copy command can accomplish some stream functions similarly, although the copy command and stream method are not heavyweight. The following describes the main usage of the copy command.

1. copy Command help information

Scott @ SYBO2SZ> help copy

COPY
----

Copies data from a query to a table in the same or another
Database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.

COPY {FROM database | TO database | FROM database TO database}
{APPEND | CREATE | INSERT | REPLACE} destination_table
[(Column,...)] USING query

Where database has the following syntax:
Username [/password] @ connect_identifier

The data types supported by copy and usage of the copy command are listed above.
The from database clause specifies the connected source database. If omitted, it indicates the currently connected database.
The to database clause specifies the target database to be connected to. If omitted, it is the current database.
From database TO database both specify the connected source database and target database
Supports data replication between tables: APPEND | CREATE | INSERT | REPLACE
Supports data replication across Oracle versions, between different schemas, and between the same schema
Supports data replication between heterogeneous databases, such as from Oracle to non-Oracle databases.
Supports Oracle cross-platform database replication, for example, from windows to linux.
Supports data replication from a local database to a remote database, from a remote database to a local database, and from a remote database to another remote database.
When copying data, use Oracle net to transmit data

2. Data replication between the same database and the same schema

-- Create method. Only the from clause is specified.
-- Note: In the following example, the symbol "-" indicates a Connection Symbol, used for line feed writing.
Scott @ SYBO2SZ> copy from scott/tiger @ sybo2sz-
> Create tb_emp-
> Using select * from emp;

Array fetch/bind size is 2000. (arraysize is 2000)
Will commit when done. (copycommit is 0)
Maximum long size is 5000. (long is 5000)
Table TB_EMP created.

14 rows selected from scott @ sybo2sz.
14 rows inserted into TB_EMP.
14 rows committed into TB_EMP at default host connection.

-- Append method. Only the to clause is specified.
Scott @ SYBO2SZ> copy to scott/tiger @ sybo2sz-
> Append tb_emp-
> Using select * from emp;

Array fetch/bind size is 2000. (arraysize is 2000)
Will commit when done. (copycommit is 0)
Maximum long size is 5000. (long is 5000)
14 rows selected from default host connection.
14 rows inserted into TB_EMP.
14 rows committed into TB_EMP at scott @ sybo2sz.

Scott @ SYBO2SZ> select count (*) from tb_emp;

COUNT (*)
----------
28

-- Insert
Scott @ SYBO2SZ> copy from scott/tiger @ sybo2sz-
> Insert tb_emp2 using select * from emp where deptno = 20;

Array fetch/bind size is 2000. (arraysize is 2000)
Will commit when done. (copycommit is 0)
Maximum long size is 5000. (long is 5000)
5 rows selected from scott @ sybo2sz.
5 rows inserted into TB_EMP2.
5 rows committed into TB_EMP2 at default host connection.

-- Replace: the number of table records obtained in the previous test is 5, and the number of records after replace is 14, as shown below,
Scott @ SYBO2SZ> copy from scott/tiger @ sybo2sz-
> Replace tb_emp2 using select * from emp;

Array fetch/bind size is 2000. (arraysize is 2000)
Will commit when done. (copycommit is 0)
Maximum long size is 5000. (long is 5000)
Table TB_EMP2 dropped.

Table TB_EMP2 created.

14 rows selected from scott @ sybo2sz.
14 rows inserted into TB_EMP2.
14 rows committed into TB_EMP2 at default host connection.

-- Use column alias
-- The column alias is used below, and only a few columns of data are copied.
Scott @ SYBO2SZ> copy from scott/tiger @ sybo2sz-
> Replace tb_emp2 (eno, name, job_name) using select empno, ename, job from emp;

Array fetch/bind size is 2000. (arraysize is 2000)
Will commit when done. (copycommit is 0)
Maximum long size is 5000. (long is 5000)
Table TB_EMP2 dropped.

Table TB_EMP2 created.

14 rows selected from scott @ sybo2sz.
14 rows inserted into TB_EMP2.
14 rows committed into TB_EMP2 at default host connection.

1 2 3 4 Next Page