对于数据库表级上的数据复制,我们最常用的是CREATE TABLE AS(CTAS)..方式。其实在SQL*Plus下面copy命令可以完成同样的工作,而且更加出色,性能也比较优异。更突出的是支持跨平台,异构数据库之间的数据复制。copy命令可以类似地完成一些stream完成的功能,尽管copy命令与stream方式不是一个重量级。下面描述copy命令的主要用法。
1、copy命令的帮助信息
- scott@SYBO2SZ> help copy
- COPY
- ----
- Copies data from a query to a table in the same or another
- database. COPY supports CHAR, DATE, LONG, NUMBER and VARCHAR2.
- COPY {FROM database | TO database | FROM database TO database}
- {APPEND|CREATE|INSERT|REPLACE} destination_table
- [(column, column, column, ...)] USING query
- where database has the following syntax:
- username[/password]@connect_identifier
- 上面列出了copy支持的数据类型以及copy命令的用法
- from database 子句指定连接的源数据库,如果省略则为当前连接的数据库
- to database子句指定连接的目的数据库,如果省略则为当前数据库
- from database TO database 同时指定了连接的原数据库以及目的数据库
- 支持几种不同的表间数据复制方式:APPEND|CREATE|INSERT|REPLACE
- 支持跨Oracle版本,不同schema之间,相同schema之间的数据复制
- 支持异构数据库间的数据复制,如Oracle到非Oracle数据库
- 支持Oracle跨平台间的数据库复制,如windows平到到linux平台
- 支持本地数据库到远程数据库,远程数据库到本地,远程数据库到另一个远程数据库之间数据复制
- 复制数据时,使用Oracle net来传输数据
2、同一数据库相同schema之间数据复制
- --create 方式,仅指定from子句
- --注,下面的示例中,符号"-"表示是连接符号,用于换行书写
- scott@SYBO2SZ> copy from scott/tiger@sybo2sz -
- > create tb_emp -
- > using select * from emp;
- Array fetch/bind size is 2000. (arraysize is 2000)
- Will commit when done. (copycommit is 0)
- Maximum long size is 5000. (long is 5000)
- Table TB_EMP created.
- 14 rows selected from scott@sybo2sz.
- 14 rows inserted into TB_EMP.
- 14 rows committed into TB_EMP at DEFAULT HOST connection.
- --append方式,仅指定to子句
- scott@SYBO2SZ> copy to scott/tiger@sybo2sz -
- > append tb_emp -
- > using select * from emp;
- Array fetch/bind size is 2000. (arraysize is 2000)
- Will commit when done. (copycommit is 0)
- Maximum long size is 5000. (long is 5000)
- 14 rows selected from DEFAULT HOST connection.
- 14 rows inserted into TB_EMP.
- 14 rows committed into TB_EMP at scott@sybo2sz.
- scott@SYBO2SZ> select count(*) from tb_emp;
- COUNT(*)
- ----------
- 28
- --insert 方式
- scott@SYBO2SZ> copy from scott/tiger@sybo2sz -
- > insert tb_emp2 using select * from emp where deptno=20;
- Array fetch/bind size is 2000. (arraysize is 2000)
- Will commit when done. (copycommit is 0)
- Maximum long size is 5000. (long is 5000)
- 5 rows selected from scott@sybo2sz.
- 5 rows inserted into TB_EMP2.
- 5 rows committed into TB_EMP2 at DEFAULT HOST connection.
- --replace方式,上一次试验得到的表记录数为5,使用replace后记录数为14,如下,
- scott@SYBO2SZ> copy from scott/tiger@sybo2sz -
- > replace tb_emp2 using select * from emp;
- Array fetch/bind size is 2000. (arraysize is 2000)
- Will commit when done. (copycommit is 0)
- Maximum long size is 5000. (long is 5000)
- Table TB_EMP2 dropped.
- Table TB_EMP2 created.
- 14 rows selected from scott@sybo2sz.
- 14 rows inserted into TB_EMP2.
- 14 rows committed into TB_EMP2 at DEFAULT HOST connection.
- --使用列别名的方式
- --下面使用了列别名,且只复制其中的几列数据
- scott@SYBO2SZ> copy from scott/tiger@sybo2sz -
- > replace tb_emp2(eno,name,job_name) using select empno,ename,job from emp;
- Array fetch/bind size is 2000. (arraysize is 2000)
- Will commit when done. (copycommit is 0)
- Maximum long size is 5000. (long is 5000)
- Table TB_EMP2 dropped.
- Table TB_EMP2 created.
- 14 rows selected from scott@sybo2sz.
- 14 rows inserted into TB_EMP2.
- 14 rows committed into TB_EMP2 at DEFAULT HOST connection.
3、同一数据库不同schema之间数据复制
- --下面使用了append方式,同时指定from及to子句
- scott@SYBO2SZ> copy from scott/tiger@sybo2sz to goex_admin/xxx@sybo2sz -
- > append tb_emp using select * from emp;
- Array fetch/bind size is 2000. (arraysize is 2000)
- Will commit when done. (copycommit is 0)
- Maximum long size is 5000. (long is 5000)
- Table TB_EMP created.
- 14 rows selected from scott@sybo2sz.
- 14 rows inserted into TB_EMP.
- 14 rows committed into TB_EMP at goex_admin@sybo2sz.
4、不同数据库之间的数据复制
- --不同数据库之间的复制一定要指定目的数据库连接字符串
- scott@SYBO2SZ> copy from scott/tiger@sybo2sz to goex_admin/xxx@cnmmbo -
- > append tb_emp using select * from emp;
- Array fetch/bind size is 2000. (arraysize is 2000)
- Will commit when done. (copycommit is 0)
- Maximum long size is 5000. (long is 5000)
- Table TB_EMP created.
- 14 rows selected from scott@sybo2sz.
- 14 rows inserted into TB_EMP.
- 14 rows committed into TB_EMP at goex_admin@cnmmbo.
5、不同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;
- Array fetch/bind size is 2000. (arraysize is 2000)
- Will commit after every 0 array binds. (copycommit is 0)
- Maximum long size is 5000. (long is 5000)
- Table TB_EMP created.
- 6 rows selected from scott@sybo2sz.
- 6 rows inserted into TB_EMP.
- 6 rows committed into TB_EMP at scott@ora11g.
- --也可以从oracle 11g复制数据到oracle 10g,此处省略
- --跨平台复制数据,没有环境,有待测试
6、copy命令的性能参数
- 与copy性能相关的几个参数
- arraysize 该参数用于SQL*Plus 每一次fetch数据的行数,缺省值为15,有效值是1到5000
- copycommit 该参数用于copy完多少行数据之后执行commit,如果该值为0,则表示所有数据复制完毕后再执行commit
- long 该参数用于设置long字符类型的最大长度,Oracle不建议使用long类型而是使用lob类型来取代
- --首先设置参数arraysize与copycommit
- scott@SYBO2SZ> set arraysize 15
- scott@SYBO2SZ> set copycommit 0
- --清空缓存
- scott@SYBO2SZ> alter system flush buffer_cache;
- scott@SYBO2SZ> alter system flush shared_pool;
- --执行脚本调用copy创建表
- scott@SYBO2SZ> @/users/robin/dba_scripts/custom/temp/cp_cmd.sql
- PL/SQL procedure successfully completed.
- Array fetch/bind size is 15. (arraysize is 15)
- Will commit when done. (copycommit is 0)
- Maximum long size is 5000. (long is 5000)
- Table CP_BIG_TB created.
- 1000000 rows selected from scott@sybo2sz.
- 1000000 rows inserted into CP_BIG_TB.
- 1000000 rows committed into CP_BIG_TB at scott@sybo2sz.
- PL/SQL procedure successfully completed.
- The elapsed time is 41.84 seconds.
- The undo size is 0
- The redo size is 0
- PL/SQL procedure successfully completed.
- --上面得到的结果表明,copy命令被使用时不产生undo 和redo
- --一百万行数据复制的时间是41.84 seconds
- --下面清除刚刚复制的目的表
- scott@SYBO2SZ> drop table CP_BIG_TB purge;
- --清空缓存
- scott@SYBO2SZ> alter system flush buffer_cache;
- scott@SYBO2SZ> alter system flush shared_pool;
- --设置新的arraysize与copycommit
- scott@SYBO2SZ> set arraysize 2000
- scott@SYBO2SZ> set copycommit 5000
- --再次调用脚本
- scott@SYBO2SZ> @/users/robin/dba_scripts/custom/temp/cp_cmd.sql
- PL/SQL procedure successfully completed.
- Array fetch/bind size is 2000. (arraysize is 2000)
- Will commit after every 5000 array binds. (copycommit is 5000)
- Maximum long size is 5000. (long is 5000)
- Table CP_BIG_TB created.
- 1000000 rows selected from scott@sybo2sz.
- 1000000 rows inserted into CP_BIG_TB.
- 1000000 rows committed into CP_BIG_TB at scott@sybo2sz.
- PL/SQL procedure successfully completed.
- The elapsed time is 24.65 seconds.
- The undo size is 0
- The redo size is 0
- PL/SQL procedure successfully completed.
- --从上面的结果可知,后者耗用的时间明显低于前者,节约了近一半的时间
7、测试用到的脚本
- robin@SZDB:~/dba_scripts/custom/temp> more cp_cmd.sql
- SET SERVEROUTPUT ON;
- VARIABLE start_time NUMBER;
- VARIABLE end_time NUMBER;
- VARIABLE v_s_undo NUMBER;
- VARIABLE v_s_redo NUMBER;
- VARIABLE v_e_undo NUMBER;
- VARIABLE v_e_redo NUMBER;
- VARIABLE v_diff_dt NUMBER;
- VARIABLE v_diff_undo NUMBER;
- VARIABLE v_diff_redo NUMBER;
- --Author : Robinson
- --Blog : http://blog.csdn.net/robinson_0612
- BEGIN
- SELECT DBMS_UTILITY.get_time INTO :start_time FROM DUAL;
- SELECT b.VALUE
- INTO :v_s_undo
- FROM v$statname a, v$mystat b
- WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'undo change vector size';
- SELECT b.VALUE
- INTO :v_s_redo
- FROM v$statname a, v$mystat b
- WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'redo size';
- END;
- /
- COPY from scott/tiger@sybo2sz -
- to scott/tiger@sybo2sz -
- create cp_big_tb -
- using -
- select * from big_table;
- BEGIN
- SELECT DBMS_UTILITY.get_time INTO :end_time FROM DUAL;
- SELECT b.VALUE
- INTO :v_e_undo
- FROM v$statname a, v$mystat b
- WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'undo change vector size';
- SELECT b.VALUE
- INTO :v_e_redo
- FROM v$statname a, v$mystat b
- WHERE a.statistic# = b.statistic# AND LOWER (a.name) = 'redo size';
- END;
- /
- BEGIN
- :v_diff_dt := round((:end_time - :start_time)/100,2);
- :v_diff_undo := :v_e_undo - :v_s_undo;
- :v_diff_redo := :v_e_redo - :v_s_redo;
- DBMS_OUTPUT.put_line ('The elapsed time is ' || TO_CHAR (:v_diff_dt)||' seconds.');
- DBMS_OUTPUT.put_line ('The undo size is ' || TO_CHAR (:v_diff_undo));
- DBMS_OUTPUT.put_line ('The redo size is ' || TO_CHAR (:v_diff_redo));
- END;
- /