oracle copy long

COPY是SQLPLUS下的命令，功能很强，语法如下：

COPY FROM user/password@dbname TO user/password@dbname CREATE/APPEND/INSERT/REPLACE TABLE_NAME (COLUMN_NAME, ……) USING SELECT ……

简单描述一下语法：

FROM和TO语句分别描述从哪个数据库COPY到哪个数据库。这两个语句中连接数据库的格式和CONNECT语句后面的连接格式是一致的。FROM或TO指向当前数据库时，可以忽略，但不能同时忽略FROM和TO。

CREATE/APPEND/INSERT/REPLACE：COPY命令的四个选项，功能分别为创建表并插入数据、在已经存在的表的记录后面插入新数据、在空表中插入数据、删除表然后重建新表并插入新数据。

后面是表名和列名，如果列名列表省略的话，则会根据查询语句来确定列的名称。

USING查询，将查询的结果按照指定方法写入表中。

 

COPY命令的优点很多，这里列举我认为最重要的三条：

支持LONG类型：由于CREATE TABLE AS和INSERT INTO SELECT都不支持LONG类型。这就导致了对LONG类型数据的处理十分麻烦，而使用存储过程的方法相对比较麻烦，利用EXP和IMP则有很多的限制条件。相对来说使用COPY是最简单快速的方法。

不需要建立数据库链，只要本地的TNSNAMES.ORA中配置了远端数据库就可以直接访问。操作方便快捷。而且也支持本数据库到本数据库的COPY。

语法灵活，支持目标表名列名与查询语句中不相符，而且还有多种数据处理方式。

 

总的来说COPY命令是一个相当好用的命令，下面是一个简单的例子。

SQL> CREATE TABLE T AS SELECT * FROM DBA_TAB_COLUMNS;
CREATE TABLE T AS SELECT * FROM DBA_TAB_COLUMNS
*
ERROR 位于第 1 行:
ORA-00997: 非法使用 LONG 数据类型

SQL> CREATE TABLE T (ID NUMBER, DEFAULT_VALUE LONG);

表已创建。

SQL> INSERT INTO T SELECT ROWNUM, DATA_DEFAULT FROM DBA_TAB_COLUMNS;
INSERT INTO T SELECT ROWNUM, DATA_DEFAULT FROM DBA_TAB_COLUMNS
*
ERROR 位于第 1 行:
ORA-00997: 非法使用 LONG 数据类型

SQL> DROP TABLE T;

表已丢弃。

SQL> COPY FROM YANGTK/YANGTK@TEST TO YANGTK/YANGTK@TEST CREATE T USING SELECT * FROM DBA_TAB_COLUMNS

数组读取/结合的大小为15。（数组大小为15）
将在完成时提交。（提交的复本为 0）
最长为80。（长度为80）
表T已创建。

27128行选自YANGTK@TEST。
27128行被插入T。
27128行已提交至T（位于YANGTK@TEST）。

其中每次读取数据数组大小有SET的ARRAYSIZE参数决定。每次提交的数据量由SET的COPYCOMMIT参数控制。COPY是否进行类型检查由SET的COPYTYPECHECK参数控制。COPY执行时LONG类型截取长度由SET的LONG参数控制。如果不希望LONG类型被截断，则保证LONG的值超过表中LONG类型的最长值。

SQL> SET ARRAYSIZE 1000
SQL> SET COPYCOMMIT 1000
SQL> SET COPYTYPECHECK OFF
SQL> SET LONG 100000
SQL> COPY FROM YANGTK/YANGTK@TEST TO YANGTK/YANGTK@TEST APPEND T USING SELECT * FROM DBA_TAB_COLUMNS

数组读取/结合的大小为1000。（数组大小为1000）
将在每1000个数组结合之后提交。（提交的复本为1000）
最长为100000。（长度为100000）
27128行选自YANGTK@TEST。
27128行被插入T。
27128行已提交至T（位于YANGTK@TEST）。

在使用COPY命令时，尽量只对用户自己的表进行操作。如果需要对其他用户下的表执行REPLACE操作时，仅仅拥有这个表的INSERT、DELETE权限是没有任何作用的，必须拥有CREATE ANY TABLE、DROP ANY TABLE和INSERT ANY TABLE系统权限。

SQL*Plus的Copy命令

在数据表间复制数据是Oracle DBA经常面对的任务之一，Oracle为这一任务提供了多种解决方案，SQL*Plus Copy 命令便是其中之一。SQL*Plus Copy 命令通过SQL*Net在不同的表(同一服务器或是不同服务器)之间复制数据或移动数据。
在实际运行环境中若能恰当地选择使用SQL*Plus Copy 命令可以有效地提高数据复制的性能。
下面将简要介绍SQL*Plus Copy 命令使用，并在性能方面与其他两种方案进行对比，力求能提供一个使用Copy 命令的方案参考。
1.语法及使用说明
1.1 语法
下面我们来看一下SQL*Copy 命令的语法及使用说明。
在解释SQL*Plus Copy 命令的语法之前，我们必须要明确SQL*Plus Copy 命令不是一个方法或是函数，也不是一个SQL语句，它是一个命令(command)，当然这个命令必须在SQL*Plus里运行。

SQL*Plus Copy 命令的语法：
COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)]
USING query

我们分部分来解释一下：

COPY – 这个不太需要解释，主命令，声明要执行COPY操作

From Database – 源数据库
To Database – 目标数据库
此处注意花括号中有三种可选的写法(以”|”隔开)，如果源数据表和目标数据表在同一个Schema中，则可以只写From Database，也可以只写To Database，当然还可以是第三种写法，把From Database和To Database写全。但如果源数据表和目标数据表不在同一个Schema中，则必须用第三种写法，即把From Database和To Database都写全
From Database和To Database的格式是一样的：USERID/PASSWORD@SID，这个大家都应该很熟悉了。

{APPEND|CREATE|INSERT|REPLACE} – 声明操作数据的方式，下面分别解释一下：
Append – 向已有的目标表中追加记录，如果目标表不存在，自动创建，这种情况下和Create等效。
Create – 创建目标表并且向其中追加记录，如果目标表已经存在，则会返回错误。
Insert – 向已有的目标表中插入记录，与Append不同的是，如果目标表不存在，不自动创建而是返回错误。
Replace – 用查询出来的数据覆盖已有的目标表中的数据，如果目标表不存在，自动创建。

destination_table – 目标表的名字
[(column, column, column, ...)] – 可以指定目标表中列的名字，如果不指定，则自动使用Query中的列名。
USING query – 查询语句，交流的数据来自这儿。

1.2 使用范例
下面我们通过几个例子来看一下SQL*Plus Copy 命令的使用：

1.2.1 在同一个服务器的同一个Schema中复制数据：
同时指定From database和To database
SQL> copy from scott/tiger@lsj to scott/tiger@lsj create dept1 using select * from dept;
只指定From Database
SQL> copy from scott/tiger@lsj create dept2 using select * from dept;
只指定To Database
SQL> copy to scott/tiger@lsj create dept3 using select * from dept;

1.2.2 在同一个服务器的不同Schema中复制数据：
这种情况下必须同时指定From Database和To Database
SQL> copy from scott/tiger@lsj to lsjdemo/lsjdemo@lsj create dept using select * from dept;
注意这种情况下，using select * from dept 中并不需要使用scott.demp的形式。

1.2.3 在不同的服务器间复制数据：
SQL> conn lsj/lsj@sunserve
已连接。
SQL> copy from scott/tiger@lsj to lsj/lsj@sunserve create dept using select * from dept;
2.4 性能实验结果
实验数据：
记录数：5,082,500
数据量：504M
实验结果

方案------------------------执行时间(秒) ---------Undo(M) ------Redo(M)
Copy command -------------520.51----------------------0 ---------------- 592
Insert into…select …---- 631.64 ------------------345 -------------1720
Create Table…------------- 244.79 --------------------0 ----------------515

2.总结

Create Table…as select…是最快的，而且生成的Undo和Redo信息最少，所以只要可能，请尽量使用这种方案。但这种方案有一定的限制，即目标表必须是不存在的，不能用它向已有的目标表中追加记录。
Insert into … select … 是最慢的，而且生成最多的Undo和Redo信息，对I/O的压力最大，优势在于大家对它比较熟悉，使用起来比较简单，适合于处理少量的数据，若要处理大量的数据，不推荐使用这种方案。
Copy Command可以处理Create Table不能处理的情况，即向已有的数据表中追加记录，相对于insert来说，效率更高一些，生成更少的Redo信息，不生成Undo信息，所以在执行大量的数据追加时，推荐使用Copy Command命令