Copy From

Sqlplus 可以实现不同数据库下的表复制

 

语法如下：

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

APPEND
    --表存在则INSERT，不存在则CREATE

    Inserts the rows from query into destination_table if the table exists. If destination_table does not exist, COPY creates it.

    CREATE
    --新建一个表，如果表存在则出错

    Inserts the rows from query into destination_table after first creating the table. If destination_table already exists, COPY returns an error.

    INSERT
    --向表中插入记录

    Inserts the rows from query into destination_table. If destination_table does not exist, COPY returns an error. When using INSERT, the USING query must select one column for each column in destination_table.

    REPLACE

    --无论表是否存在，均清空然后CREATE
    Replaces destination_table and its contents with the rows from query. If destination_table does not exist, COPY creates it. Otherwise, COPY drops the existing table and replaces it with a table containing the copied data.

COPY 命令注意不要使用回车

数据类型方面的限制

Copy Command只支持如下几种类型的字段：

CHAR
DATE
LONG
NUMBER
VARCHAR2

如果Query中包含这些字段之外的字段，则Copy Command无法执行，返回错误：

CPY-0012: 无法复制数据类型

Copy comm.and的执行速度受Arryasize的影响，一般来说，把Arraysize设置得大一些Copy command会执行得更快，Arraysize的最大值是5000。

查看及设置 Arryasize

SQL> show arraysize

SQL> set arraysize 5000