SQL*Plus copy命令处理大批量数据复制(二)
(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之间数据复制
[sql]
--下面使用了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、不同数据库之间的数据复制
[sql]
--不同数据库之间的复制一定要指定目的数据库连接字符串
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版本之间的数据复制
[sql]
--下面是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命令的性能参数
[sql]
与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;
--设置新的a