使用sqlplus Copy 命令从远程数据库读取数据.
需要从9个地市的计费数据库中读取一些表,存储到运营分析系统中.
有几个表数据量超过1亿条.
对于这么大的数据量,如果用create table as select 或者使用cursor的话,对回滚段的压力肯定非常大.
经过同事提示,想到了sqlplus的copy命令.
connect ods/ods
set time on
set timing on
set head on
set echo on
set copycommit 10
set arraysize 5000
-- 长春
copy from yyfx/******@ccbill1 to changchun/******@ora1 create serv using select * from lbas.serv;
这个命令不用太多解释.
需要注意的是: create处有4中选择: create,append,insert,replace.
具体命令下面有一个itput的帖子,解释得很详细.
补充点: 在sqlplus执行这个命令的时候,有3个选项需要注意:
set long 1000 -----------long型字符的长度
set copycommit 10 -----------读多少次提交
set arraysize 5000 -----------每次读取数据的行数
根据以上的设置,copy命令每次读取5000行数据,没读取10次(50000行)commit一次.
下面是itpub的文档:
Copy Command的初步研究
SQL*Plus Copy Command
前言
这段时间论坛里有好几个贴子讨论到了在表之间复制数据的问题，也讨论到了SQL*Plus Copy Command。
在数据表间复制数据是Oracle DBA经常面对的任务之一，Oracle为这一任务提供了多种解决方案，SQL*Plus Copy Command便是其中之一。SQL*Plus Copy Command通过SQL*Net在不同的表(同一服务器或是不同服务器)之间复制数据或移动数据。
在实际运行环境中若能恰当地选择使用SQL*Plus Copy Command可以有效地提高数据复制的性能。
下面将简要介绍SQL*Plus Copy Command使用，并在性能方面与其他两种方案进行对比，力求能提供一个使用Copy Command的方案参考。
1.语法及使用说明
1.1 语法
下面我们来看一下SQL*Copy Command的语法及使用说明。
在解释SQL*Plus Copy Command的语法之前，我们必须要明确SQL*Plus Copy Command不是一个方法或是函数，也不是一个SQL语句，它是一个命令(command)，当然这个命令必须在SQL*Plus里运行。
SQL*Plus Copy Command的语法：
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 Command的使用：
1.2.1 在同一个服务器的同一个Schema中复制数据：
Copy操作前Schema中的表
SQL> conn scott/tiger
已连接。
SQL> select tname from tab;
TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
同时指定From database和To database
SQL> copy from scott/tiger@lsj to scott/tiger@lsj create dept1 using select * from dept;
数组提取/绑定大小为 15。(数组大小为 15)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
表 DEPT1 已创建。
4 行选自 scott@lsj。
4 行已插入 DEPT1。
4 行已提交至 DEPT1 (位于 scott@lsj)。
只指定From Database
SQL> copy from scott/tiger@lsj create dept2 using select * from dept;
数组提取/绑定大小为 15。(数组大小为 15)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
表 DEPT2 已创建。
4 行选自 scott@lsj。
4 行已插入 DEPT2。
4 行已提交至 DEPT2 (位于 DEFAULT HOST 连接)。
只指定To Database
SQL> copy to scott/tiger@lsj create dept3 using select * from dept;
数组提取/绑定大小为 15。(数组大小为 15)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
表 DEPT3 已创建。
4 行选自 DEFAULT HOST 连接。
4 行已插入 DEPT3。
4 行已提交至 DEPT3 (位于 scott@lsj)。
Copy操作后Schema中的表
SQL> select tname from tab;
TNAME
------------------------------
DEPT
EMP
BONUS
SALGRADE
DEPT1
DEPT2
DEPT3
已选择7行。
1.2.2 在同一个服务器的不同Schema中复制数据：
这种情况下必须同时指定From Database和To Database
SQL> copy from scott/tiger@lsj to lsjdemo/lsjdemo@lsj create dept using select * from dept;
数组提取/绑定大小为 15。(数组大小为 15)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
表 DEPT 已创建。
4 行选自 scott@lsj。
4 行已插入 DEPT。
4 行已提交至 DEPT (位于 lsjdemo@lsj)。
注意这种情况下，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;
数组提取/绑定大小为 15。(数组大小为 15)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
表 DEPT 已创建。
4 行选自 scott@lsj。
4 行已插入 DEPT。
4 行已提交至 DEPT (位于 lsj@sunserve)。
2.SQL*Plus Copy Command的性能
对于SQL*Plus Copy Command的性能问题可谓是众说纷纭，有人说SQL*Plus Copy Command复制数据时不经过Buffer，不写回滚段，所以速度是非常快的；而有人经过实验发现SQL*Plus Copy Command并不像想象中的那么快，甚至慢得让人难以接受。
我们说SQL*Plus Copy Command快还是慢主要是与其他两种复制数据的方案进行对比：
Create table … as select …
Insert into … select … from …
这三种方案在性能上到底有什么差别呢，我们通过实验来验证一下。
实验环境：
我的工作用机，lsj
CPU：2.6G
MEM：1G
Oracle：10g
实验方案描述：
构造一个500余万条记录的实验表，分别用三种方案复制到新的表统计所花的时间，Create…as..和Insert …. select…直接使用Sql*Plus的时间统计，Copy命令不能使用Sql*Plus的时间统计，采用秒表计时，可精确到百分之一秒。
实验前后监控UNDOTBS来查看写回滚段的数据量，数据库运行在Archivelog状态下，查看Archivelog来监控写Redo的数据量(这个可能不太精确，谁有更好的方法请指教一下)。
2.1 准备实验数据
SQL> conn lsjdemo/lsjdemo@lsj
已连接。
SQL> select count(*) from all_objects;
COUNT(*)
----------
10164
SQL> create table a as select * from all_objects where 1<0;
表已创建。
SQL> declare n number;
2 begin
3 for n in 1..500 loop
4 insert into a select * from all_objects;
5 end loop;
6 end;
7 /
PL/SQL 过程已成功完成。
SQL> select count(*) from a;
COUNT(*)
----------
5082500
看一下500多万条记录占了多大空间
SQL> select SUM(BYTES)/(1024*1024) Total_Space from DBA_extents
2 where owner='LSJDEMO' and segment_type='TABLE' and segment_name='A'
3 GROUP BY TABLESPACE_NAME,SEGMENT_NAME;
TOTAL_SPACE
-----------
504
2.2 实验过程
2.2.1 Copy Command
首先来看一下SQL*Plus Copy Command的表现。
操作前的状态：
SQL> column 表空间名 format a15
SQL> select a.file_id "文件号",a.tablespace_name "表空间名",
a.bytes/(1024*1024) "空间(M)",
(a.bytes-sum(nvl(b.bytes,0)))/(1024*1024) "已用(M)",
(sum(nvl(b.bytes,0)))/(1024*1024) "剩余空间(M)",
sum(nvl(b.bytes,0))/a.bytes*100 "可用比率"
from dba_data_files a, dba_free_space b
where a.file_id=b.file_id(+) and a.tablespace_name='UNDOTBS1'
group by a.tablespace_name ,a.file_id,a.bytes;
文件号 表空间名 空间(M) 已用(M) 剩余空间(M) 可用比率
---------- --------------- ---------- ---------- ----------- ----------
2 UNDOTBS1 3000 1032.625 1967.375 65.5791667
执行过程：
SQL> copy from lsjdemo/lsjdemo@lsj create b using select * from a;
数组提取/绑定大小为 15。(数组大小为 15)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
表 B 已创建。
5083000 行选自 lsjdemo@lsj。
5083000 行已插入 B。
5083000 行已提交至 B (位于 DEFAULT HOST 连接)。
再次查看回滚段表空间状态：
SQL> /
文件号 表空间名 空间(M) 已用(M) 剩余空间(M) 可用比率
---------- --------------- ---------- ---------- ----------- ----------
2 UNDOTBS1 3000 1032.625 1967.375 65.5791667
上面的结果说明copy command没有生成undo。
经查看发现生成了592M的归档日志，说明copy comm.and生成了大量的redo数据。
执行时间：8分40.51秒 = 520.51秒
2.2.2 Insert into …. select ….
接下来我们看一下Insert 的性能，在执行Insert前将归档日志清空：
SQL> set timing on
SQL> create table c as select * from a where 1<0;
表已创建。
已用时间: 00: 00: 00.45
SQL> insert into c select * from a;
已创建5083000行。
已用时间: 00: 10: 31.64
再次查看回滚段表空间状态：
SQL> /
文件号 表空间名 空间(M) 已用(M) 剩余空间(M) 可用比率
---------- --------------- ---------- ---------- ----------- ----------
2 UNDOTBS1 3000 1032.625 1967.375 65.5791667
咦，回滚段表空间依然是1032.625M，难道Insert不生成undo信息？不可能啊。
偶再次执行insert into c select * from a; 并启动一个监控程序，每5秒报告UNDOTBS1的空间信息，这才发现了问题所在.
原来在开始执行Insert的时候回滚段表空间被释放了一部分，释放到687.625M，然后在Insert的过程中逐渐增长，最后又增长到1032.625M，说明Insert生成了1032.625-687.625=345M回滚信息。
回滚段表空间为什么会先释放后增长呢，它里面明明有足够的Free空间呀，这个偶也想不明白了。
同样，偶也想到，Copy Command会不会也是这种情况呢
所以，偶再次执行Copy，也同样地监控回滚段表空间，发现在执行过程中回滚段表空间始终是1032.625M，最终证明了Copy command没有生成Undo信息。
接下来查看归档日志，发现Insert生成了1.72G的归档日志，归档日志的数据量是Copy Command的三倍之多。
执行时间从上面的结果已经可以看到了：
已用时间: 00: 10: 31.64
10分31.64秒 = 631.64秒。
2.2.3 Create … as select…
最后来看看Create的性能。
SQL> create table d as select * from a;
表已创建。
已用时间: 00: 04: 04.79
在执行过程中监控Undo表空间，发现也没有生成回滚信息。其实Create不生成回滚是很好理解的，因为Create Table 是DDL语句，根本无法回滚，自然也就不会生成回滚信息了。
经查看发现生成了515M归档日志，比Insert少得多，比Copy Command也少一些。
执行时间上快得有些出乎意料：
4分4.79秒 = 244.79秒，明显快于前两种方案。
在这里偶对Create的执行机制产生了一个猜测，认为它并不是把数据先Query出来，再用Query的结果写入目标表中，而是直接读Block数据并写入目标表中，所以才会有这么快的执行速度。
下面来验证一下：
SQL> create view v_a as select * from a;
视图已创建。
已用时间: 00: 00: 00.14
SQL> create table e as select * from v_a;
表已创建。
已用时间: 00: 04: 03.62
从上面的结果看出还是很快，难道是这个视图太特殊了，再试一个：
SQL> drop view v_a;
视图已删除。
SQL> desc a;
名称 是否为空? 类型
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(30)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> create view v_a as select owner,object_name,subobject_name,
rownum rno,data_object_id,object_type,created,
last_ddl_time,timestamp,status,temporary,generated,secondary
from a;
视图已创建。
已用时间: 00: 00: 00.03
SQL> create table e as select * from v_a;
表已创建。
已用时间: 00: 04: 12.65
这个结果依然是很快，看来偶的猜测是完全错误的，那Create table ….as select 为什么会这么快呢？偶是想不明白了，敬请诸位老大指教一下。
2.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.2.5 实验总结
Create Table…as select…是最快的，而且生成的Undo和Redo信息最少，所以只要可能，请尽量使用这种方案。但这种方案有一定的限制，即目标表必须是不存在的，不能用它向已有的目标表中追加记录。
Insert into … select … 是最慢的，而且生成最多的Undo和Redo信息，对I/O的压力最大，优势在于大家对它比较熟悉，使用起来比较简单，适合于处理少量的数据，若要处理大量的数据，不推荐使用这种方案。
Copy Command可以处理Create Table不能处理的情况，即向已有的数据表中追加记录，相对于insert来说，效率更高一些，生成更少的Redo信息，不生成Undo信息，所以在执行大量的数据追加时，推荐使用Copy Command命令。
2.3 对Copy Command的进一步实验
我们在观察Copy Command的结果的时候发现输出中有几个引人注意的信息：
SQL> copy from lsjdemo/lsjdemo@lsj create b using select * from a;
数组提取/绑定大小为 15。(数组大小为 15)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
表 B 已创建。
5083000 行选自 lsjdemo@lsj。
5083000 行已插入 B。
5083000 行已提交至 B (位于 DEFAULT HOST 连接)。
其中：
数组提取/绑定大小为 15。(数组大小为 15)
这个和SQL Plus的一个选项有关 -- Arraysize
SQL> show arraysize
arraysize 15
这个选项决定在Copy操作时以15条记录为一个批处理，在这儿偶又猜测，如果把这个值设大一些，Copy 是否会快一些呢？
实验一把：
SQL> set arraysize 2048
SQL> show arraysize
arraysize 2048
SQL> copy from lsjdemo/lsjdemo@lsj create f using select * from a;
数组提取/绑定大小为 2048。(数组大小为 2048)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
表 F 已创建。
5083000 行选自 lsjdemo@lsj。
5083000 行已插入 F。
5083000 行已提交至 F (位于 DEFAULT HOST 连接)。
哈哈，果然不出偶所料(终于料准了一把 )：
执行时间：4分35.37秒 = 275.37秒，这个结果已经和Create table … 相差不多了
最新实验结果
方案---------------------执行时间(秒)---------------Undo(M)-------------Redo(M)
Copy command------------275.37---------------------0-------------------------592
Insert into…----------------631.64------------------345---------------------1720
Create Table… -----------244.79---------------------0----------------------515
偶继续猜想，偶把Arraysize从15改成2048，结果快了很多，那改得再大一些，应该会更快的，偶把它改成最大值5000，继续实验：
SQL> set arraysize 5000
SQL> show arraysize
arraysize 5000
SQL> copy from lsjdemo/lsjdemo@lsj create g using select * from a;
数组提取/绑定大小为 5000。(数组大小为 5000)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
表 G 已创建。
5083000 行选自 lsjdemo@lsj。
5083000 行已插入 G。
5083000 行已提交至 G (位于 DEFAULT HOST 连接)。
呵呵，偶又料准了一把：
执行时间：4分22.97秒 = 262.97秒
最新实验结果
方案---------------------执行时间(秒)---------------Undo(M)-------------Redo(M)
Copy command------------262.97---------------------0-------------------------592
Insert into…----------------631.64------------------345---------------------1720
Create Table… -----------244.79---------------------0----------------------515
进一步实验后的结论：
Copy comm.and的执行速度受Arryasize的影响，一般来说，把Arraysize设置得大一些Copy command会执行得更快，Arraysize的最大值是5000。
3.Copy Command的一些其他说明
3.1 数据类型方面的限制
Copy Command只支持如下几种类型的字段：
CHAR
DATE
LONG
NUMBER
VARCHAR2
如果Query中包含这些字段之外的字段，则Copy Command无法执行，返回错误：
SQL> create table m(a number,b blob default empty_blob());
表已创建。
已用时间: 00: 00: 00.07
SQL> insert into m values(1,empty_blob());
已创建 1 行。
已用时间: 00: 00: 00.00
SQL> commit;
提交完成。
已用时间: 00: 00: 00.00
SQL> copy from lsjdemo/lsjdemo@lsj create n using select * from m;
数组提取/绑定大小为 5000。(数组大小为 5000)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
CPY-0012: 无法复制数据类型
3.2 对LONG系列类型字段的支持
与2.4.1中所论述的相反，Copy Command支持Long 及Long Raw类型的字段，但Insert into…select …及Create Table …. as select …则不支持Long及Long Raw 类型的字段。这种情况下，Copy Command反而可以很好地完成任务。
SQL> create table o(a number,b long raw);
表已创建。
已用时间: 00: 00: 00.04
SQL> insert into o values(1,NULL);
已创建 1 行。
已用时间: 00: 00: 00.00
SQL> create table p as select * from o;
create table p as select * from o
*
第 1 行出现错误:
ORA-00997: 非法使用 LONG 数据类型
已用时间: 00: 00: 00.00
SQL> create table p(a number, b long raw);
表已创建。
已用时间: 00: 00: 00.00
SQL> insert into p select * from o;
insert into p select * from o
*
第 1 行出现错误:
ORA-00997: 非法使用 LONG 数据类型
已用时间: 00: 00: 00.01
SQL> copy from lsjdemo/lsjdemo@lsj append p using select * from o;
数组提取/绑定大小为 5000。(数组大小为 5000)
将在完成时提交。(提交的副本为 0)
最大 long 大小为 80。(long 为 80)
1 行选自 lsjdemo@lsj。
1 行已插入 P。
1 行已提交至 P (位于 DEFAULT HOST 连接)。
3.3 选择执行Host
选择执行Copy command的Host时需要十分注意，如果目标Schema和源Schema在同一台服务器上，由执行的Host最好也放在这台服务器上，也即在这台服务器上执行Copy Command，如果目标Schema和源Schema不在同一台服务器上，则可以在目标服务器上执行Copy Command，也可以在源服务器上执行Copy Command，但不要在第三台机器上执行Copy Command。
总之，不要在和Copy Command操作的数据无关的客户端上执行Copy Command。
原因比较好理解，Copy Command的执行机制是通过SQL*Net来周转数据，如果是在第三台机器上执行的话，相当于数据绕了一大段弯路才进入目标服务器，自然会影响效率。
4.总结
最终实验结果
方案---------------------执行时间(秒)---------------Undo(M)-------------Redo(M)
Copy command------------262.97--------------------0-------------------------592
Insert into…----------------631.64------------------345---------------------1720
Create Table… -----------244.79---------------------0----------------------515
相信您可以参考这个结果得出什么情况下可以使用Copy Command的结论。
总体来说，SQL*Plus Copy Command是一个很不错的东东，其实Oracle为我们的每一项可能面临的工作任务都提供了多种可以选择的实现方案，关键在于我们要了解这些方案，并且知道在什么情况下选择哪种方案更加合适。
偶研究Oracle的时间不长，经验很不足的说，上面的研究过程肯定有不少不足和错误，敬请各位拍砖指正。
阅读(2074) | 评论(0) | 转发(0) |
