SQL*Plus Copy Command实现不同表之间数据复制

 

以下内容引自：http://blog.csdn.net/leuphis/archive/2007/07/25/1706775.aspx

 

 

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> set arraysize 5000
SQL> copy from scott/tiger@lsj to scott/tiger@lsj create dept1 using select * from dept;
数组提取/绑定大小为 5000。(数组大小为 5000)
将在完成时提交。(提交的副本为 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

 

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.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