Oraclecopy命令详解（原创）
概述
在数据表间复制数据是Oracle DBA经常⾯对的任务之⼀，Oracle为这⼀任务提供了多种解决⽅案，SQL*Plus Copy 命令便是其中之⼀。SQL*Plus Copy 命令通过SQL*Net在不同的表(同⼀服务器或是不同服务器)之间复制数据或移动数据。在实际运⾏环境中若能恰当地选择使⽤SQL*Plus Copy 命令可以有效地提⾼数据复制的性能。
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。
需要注意的是from或to必须指定其⼀,
否则出现："SP2-0495: FROM and TO clauses both missing; specify at least one"的提⽰,
若不指定from或者to，则默认其为SQLPLUS当前连接的数据库及模式
using⼦句表⽰使⽤SELECT语句指定了所需复制的数据，因为是查询语句所以数据可以来源于多个表的复杂查询结果
{APPEND|CREATE|INSERT|REPLACE} – 声明操作数据的⽅式，下⾯分别解释⼀下：
Append – 向已有的⽬标表中追加记录，如果⽬标表不存在，⾃动创建，这种情况下和Create等效。
Create – 创建⽬标表并且向其中追加记录，如果⽬标表已经存在，则会返回错误。
Insert – 向已有的⽬标表中插⼊记录，与Append不同的是，如果⽬标表不存在，不⾃动创建⽽是返回错误。
Replace – ⽤查询出来的数据覆盖已有的⽬标表中的数据，如果⽬标表不存在，⾃动创建。
见下⾯⼏个例⼦
同⼀数据库相同schema之间数据复制
create ⽅式，仅指定from⼦句
注，下⾯的⽰例中，符号"-"表⽰是连接符号，⽤于换⾏书写
scott@SYBO2SZ> copy from scott/tiger@sybo2sz -
> create tb_emp -
> using select * from emp;
同⼀数据库不同schema之间数据复制
下⾯使⽤了append⽅式，同时指定from及to⼦句
scott@SYBO2SZ> copy from scott/tiger@sybo2sz to goex_admin/xxx@sybo2sz -
> append tb_emp using select * from emp;
不同数据库之间的数据复制
不同数据库之间的复制⼀定要指定⽬的数据库连接字符串
scott@SYBO2SZ> copy from scott/tiger@sybo2sz to goex_admin/xxx@cnmmbo -
> append tb_emp using select * from emp;
不同oracle版本之间的数据复制
下⾯是oracle 10g到oracle 11g之间的数据复制
cott@SYBO2SZ> copy from scott/tiger@sybo2sz to scott/tiger@ora11g -
> create tb_emp using select * from emp where deptno=30;
COPY supports the following datatypes:
CHAR
DATE
LONG
NUMBER
VARCHAR2
Tips: if you execute copy command by any java connection client, you can copy more data type beyond above, such as timestamp,blob etc.
To enable the copying of data between Oracle and non-Oracle databases, NUMBER columns are changed to DECIMAL columns in the destination table. Hence, if you are copying between Oracle databases, a NUMBER column with no precision will be changed to a DECIMAL(38) column. When copying between Oracle databases, you should use SQL commands (CREATE TABLE AS and INSERT) or you should ensure that your columns have a precision specified.
The SQL*Plus SET LONG variable limits the length of LONG columns that you copy. If any LONG columns contain data longer than the value of LONG, COPY truncates the data.
Warning:
Including your password in plain text is a security risk. You can avoid this risk by omitting the password, and entering it only when the system prompts for it.
性能优化
arraysize
arraysize参数⽤于SQL*Plus 每⼀次fetch数据的⾏数，缺省值为15，有效值是1到5000。当扫描了arraysize ⾏后，停⽌扫描，返回数据到sqlplus客户端，然后继续扫描。
这个过程就是统计信息中的SQL*Net roundtrips to/from client。因为arraysize 默认是15⾏，那么就有⼀个问题，因为我们⼀个block 中的记录数⼀般都会超过15⾏，所以如果按照15⾏扫描⼀次，那么每次扫描要多扫描⼀个数据块，⼀个数据块也可能就会重复扫描多次。重复的扫描会增加consistent gets和 physical reads。 增加physical reads，这个很好理解，扫描的越多，物理的可能性就越⼤。consistent gets，这个是从undo⾥读的数量，Oracle 为了保证数据的⼀致性，当⼀个查询很长，在查询之后，数据块被修改，还未提交，再次查询时候，Oracle根据Undo 来构建CR块，这个CR块，可以理解成数据块在之前某个时间的状态。 这样通过查询出来的数据就是⼀致的。那么如果重复扫描的块越多，需要构建的CR块就会越多，这样读Undo 的机会就会越多，consistent gets 就会越多。如果数据每次传到客户端有中断，那么这些数据会重新扫描，这样也就增加逻辑读，所以调整arraysize可以减少传的次数，减少逻辑读。
所以通过上⾯的说明，arraysize 参数如果过低，会影响如physical reads，consistent gets 还有SQL*Net roundtrips to/from client 次数。
oracle decimal类型查看默认值
SYS@anqing2(rac2)> show arraysize
arraysize 15
⼿⼯修改arraysize
SYS@anqing2(rac2)> set arraysize 100
SYS@anqing2(rac2)> show arraysize
arraysize 100
永久保存arraysize 参数：
可以该参数保存到glogin.sql 或者login.sql ⽂件⾥，这样可以永久⽣效，不必每次都去set 指定。
修改glogin.sql
[oracle@rac2 admin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/sqlplus/admin
[oracle@rac2 admin]$ ls
glogin.sql help iplus libisqlplus.def libsqlplus.def plustrce.sql pupbld.sql
在glogin.sql⾥添加：
set arraysize 5000
--重新登陆，查询
SYS@anqing2(rac2)> show arraysize
arraysize 5000
相关测试
查看table占⽤blocks 数量
DAVE@anqing2(rac2)> select owner,extents,segment_name,blocks from dba_segments where segment_name='DAVE' and
DAVE@anqing2(rac2)> select owner,extents,segment_name,blocks from dba_segments where segment_name='DAVE' and owner='DAVE';
OWNER EXTENTS SEGMENT_NAME BLOCKS
---------- ---------- -------------------- ----------
DAVE 3 DAVE 24
从这个数据算⼀个，1000⾏数据24个数据块。 平均下来每个数据块⾥有417条记录. 但事情情况可能不是这样.
表结构很简单
DAVE@anqing2(rac2)> desc dave;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
NAME VARCHAR2(10)
查看每个数据块中有多少记录：
DAVE@anqing2(rac2)> select prerid,count(rid) rid from (select substr(rowid,1,15) prerid,rowid rid from dave) group by prerid;
PRERID RID
------------------------------ ----------
AAANXzAAHAAAAAa 517
AAANXzAAHAAAAAf 517
AAANXzAAHAAAAAP 517
.......................................
AAANXzAAHAAAAAd 517
20 rows selected.
这⾥只有20⾏，即实际只使⽤了20个数据块，每个数据块的记录如上查询结果，因为表的记录很简单，所以每个块中的记录很多。
但是之前我们查询表占⽤了24个数据块，那么通过以下查询，可以理解为什么是24个blocks：
DAVE@anqing2(rac2)> select extent_id,block_id,blocks from dba_extents where owner='DAVE' and segment_name='DAVE'; EXTENT_ID BLOCK_ID BLOCKS
---------- ---------- ----------
0 9 8
1 17 8
2 25 8
因 为这⾥分配了3个extents，每个extent 由8个blocks组成。如果按照默认的情况，arraysize 为15，那么每个块要查询的次数是：517/15 = 35次。 那么这个就会带来更多的consistents gets 和 physical read。 我们验证⼀下。
DAVE@anqing2(rac2)> set autot traceonly stat
DAVE@anqing2(rac2)> select * from dave where rownum<518;
因为⼀个数据块中有517条记录，所以这⾥只查询⼀个数据块的次数。
517 rows selected.
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
87 consistent gets
0 physical reads
0 redo size
9354 bytes sent via SQL*Net to client
774 bytes received via SQL*Net from client
36 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
517 rows processed
注意这⾥的SQL*Net roundtrips to/from client，在之前，我们估计是按照arraysize 的默认值，读完这个数据块需要roundtrips 35次，这⾥实际⽤了36次。
我们设置下arraysize， 在查询：
arraysize 最⼤5000
DAVE@anqing2(rac2)> set arraysize 5000
DAVE@anqing2(rac2)> select * from dave where rownum<518;
DAVE@anqing2(rac2)> select * from dave where rownum<518;
517 rows selected.
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
5036 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
517 rows processed
⽐较：
consistent gets ： 从87 变成了5.
SQL*Net roundtrips to/from client ：从36 变成了2。如果数据量越⼤，那么这种优化的性能提升就越明显，该参数值copy中命令中可以理解为每次fetch的⾏数，。
copycommit
该参数⽤于copy完多少array之后执⾏commit，如果该值为0，则表⽰所有数据复制完毕后再执⾏commit，如果该参数为⾮零值，则将在copy了arraysize*copycommit⾏后进⾏commit；
性能实验结果
实验数据：
记录数：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
Create Table…as select…是最快的，⽽且⽣成的Undo和Redo信息最少，所以只要可能，请尽量使⽤这种⽅案。但这种⽅案有⼀定的限制，即⽬标表必须是不存在的，不能⽤它向已有的⽬标表中追加记录。
Insert into … select … 是最慢的，⽽且⽣成最多的Undo和Redo信息，对I/O的压⼒最⼤，优势在于⼤家对它⽐较熟悉，使⽤起来⽐较简单，适合于处理少量的数据，若要处理⼤量的数据，不推荐使⽤这种⽅案。
Copy Command可以处理Create Table不能处理的情况，即向已有的数据表中追加记录，相对于insert来说，效率更⾼⼀些，⽣成更少的Redo信息，不⽣成Undo信息，所以在执⾏⼤量的数据追加时，推荐使⽤Copy Command命令。
本⽂原创，转载请注明出处、作者
如有错误，欢迎指正
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论