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

最新推荐文章于 2023-01-04 11:54:56 发布

weixin_34138377

最新推荐文章于 2023-01-04 11:54:56 发布

阅读量122

点赞数

文章标签：数据库操作系统

废话就不多说了，开始。。。

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

更多参考

DML Error Logging 特性

PL/SQL --> 标游

PL/SQL --> 隐式标游(SQL%FOUND)

批量SQL之 FORALL 语句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化与赋值

PL/SQL 合联数组与嵌套表
PL/SQL 变长数组
 PL/SQL --> PL/SQL记载

dbms_xplan之display_cursor数函的用使

dbms_xplan之display数函的用使

执行计划中各段字各模块述描

用使 EXPLAIN PLAN 获得SQL语句执行计划

文章结束给大家分享下程序员的一些笑话语录：看到有人回帖“不顶不是中国人”，他的本意是想让帖子沉了。

weixin_34138377

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
数据库数据复制SQL*Plus copy 命令处理大批量数据复制

废话就不多说了，开始。。。对于数据库表级上的数据制复，我们最用常的是CREATE TABLE AS(CTAS)..方法。其实在SQL*Plus面下copy命令可以实现样同的任务，而且更加色出，性能也比拟异优。更出突的是支撑跨平台，构异数据库之间的数据制复。copy命令可以类似地实现一些stream实现的功能，尽管copy命令与stream方法不是一个重量级。面下述描copy命令...
复制链接

扫一扫