SQL*Plus copy 命令处理大批量数据复制

    对于数据库表级上的数据复制,我们最常用的是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;
/

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

使用awk来解析dump文件 (73天)

dump文件是平时工作中经常碰见的,有时候得到一个dump,但是没有提供一些更多的信息,导入的时候就很可能会有问题。 如果某个用户默认表空间是user,但是du...

45880
来自专栏乐沙弥的世界

dbms_xplan之display函数的使用

DBMS_XPLAN包包括一系列函数,主要是用于显示SQL语句的执行计划,且不同的情形下使用不同的函数来显示,如预估的执行计划则使用 displ...

20020
来自专栏乐沙弥的世界

SQLplus 下行预取特性

   通常情况下数据库引擎每访问一个数据块将产生至少一个逻辑读。而行预取与逻辑读息息相关。行预取是指当客户端从数据库获取数据时 可以采用单行也可以采用多行方式返...

7320
来自专栏恰童鞋骚年

《T-SQL查询》读书笔记Part 1.逻辑查询处理知多少

  T-SQL是ANSI和ISO SQL标准的MS SQL扩展,其正式名称为Transact-SQL,但一般程序员都称其为T-SQL。

9740
来自专栏Hadoop数据仓库

Oracle 索引的分类

看到 itpub 论坛上的一个帖子,对 Oracle 的索引分类总结得言简意赅,于是收藏过来。又补充了一点RAC用到的反向索引的东西。 逻辑上:  Single...

20290
来自专栏乐沙弥的世界

基于 dbms_redefinition 在线重定义表

      Oracle 支持在线重定义表,也就是说我们可以在修改表结构(DDL)的同时进行相关的DQL、DML操作,使得前端的DML根本感觉不到表结构实际上已...

13120
来自专栏恰童鞋骚年

《T-SQL查询》读书笔记Part 2.执行计划

  在SSMS(SQL Server Management Studio)中,有两种图形化的执行计划可供选择。一种是估计执行计划(Ctrl+L键),另一种是实际...

7630
来自专栏乐沙弥的世界

SQLPlus 常用命令

3->LIST [m][*] [n](简写L)显示缓冲区的所有内容。* 当前行,m 第m行,n 第n行,m n 同时出现,m到n行

8720
来自专栏分布式系统和大数据处理

Hive中分区和分桶的概念和操作

在使用传统的RDBMS数据库(关系数据库),例如MySql时,对于一些大表,我们通常会进行分表操作,以提升查询效率。在Hive中也提供了类似的概念和操作,本文将...

21010
来自专栏安恒网络空间安全讲武堂

Sqli_labs65关通关详解(上)

Less-1 这个题目是基于错误,单引号,字符型注入, http://127.0.0.1/sqli/Less-1/?id=1' //报错 http://...

73960

扫码关注云+社区

领取腾讯云代金券