了解sqlplus中的copy命令

复制表数据是Oracle dba和应用开发人员的日常工作之一,常见的三种复制表数据的方式是:

1. 以INSERT ..SELECT的方式将目标数据由源表插入到源表中,以此种方法复制表上的数据时可以灵活运行SELECT语句剔除那些无用的数据；同时也可以利用append nologging和并行parallel等特性加速数据复制速度.通过使用DBLINK数据库远程连接,INSERT…SELECT方式可以在数据库间复制表数据,当然要比在本库中复制表数据效率下降一些.
2. 使用Oracle标准的导入导出工具exp/imp或者在10g以后使用DataPump数据泵技术,此种方法可以在数据库之间复制表数据,也可以用QUERY选项指定Where条件以筛选导出数据,10g中server端模式的数据泵极大地提高了导入导出的效率.
3. 此外我们也可以使用SQLPLUS的copy命令在本库中或者在数据库间复制表上的数据.

使用copy命令复制表数据的这种方式用的较少,我们来着重介绍一下:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> conn maclean/maclean
Connected.

SQL> create table copy_sample as select * from dba_objects;
Table created.

SQL> copy
usage: COPY FROM <db> TO <db> <opt> <table> { (<cols>) } USING <sel>
 <db>   : database string, e.g., hr/your_password@d:chicago-mktg
 <opt>  : ONE of the keywords: APPEND, CREATE, INSERT or REPLACE
 <table>: name of the destination table
 <cols> : a comma-separated list of destination column aliases
 <sel>  : any valid SQL SELECT statement
A missing FROM or TO clause uses the current SQL*Plus connection.

1.使用copy命令在本地同一数据库复制表上的数据

SQL>copy from maclean/[email protected] create copy_table using select * from copy_sample;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table COPY_TABLE created.

   51041 rows selected from [email protected].
   51041 rows inserted into COPY_TABLE.
   51041 rows committed into COPY_TABLE at DEFAULT HOST connection.

/*  以上命令中from指定了源表所在的数据库连接字符,另可以用to关键字指定目标对象所在数据库连接字符串；
    using字句使用SELECT语句指定了所需复制的数据,因为是查询语句所以数据可以来源于多个表的复杂查询结果
*/

/*  需要注意的是from或to必须指定其一,
    否则出现："SP2-0495: FROM and TO clauses both missing; specify at least one"的提示,
    若不指定from或者to,则默认其为SQLPLUS当前连接的数据库及模式
*/

/* 可以通过加大sqlplus的arraysize加速copy表数据  */

SQL>drop table copy_table;
Table dropped.

SQL>set arraysize 5000;

SQL>copy from maclean/[email protected] create copy_table using select * from copy_sample;

Array fetch/bind size is 5000. (arraysize is 5000)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table COPY_TABLE created.

   51041 rows selected from [email protected].
   51041 rows inserted into COPY_TABLE.
   51041 rows committed into COPY_TABLE at DEFAULT HOST connection.

2.使用copy命令在数据库间复制表数据

SQL>copy from maclean/[email protected] to maclean/[email protected] create
copy_table_from_another_db using select * from copy_sample;

Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table COPY_TABLE_FROM_ANOTHER_DB created.
   51041 rows selected from [email protected].
   51041 rows inserted into COPY_TABLE_FROM_ANOTHER_DB.
   51041 rows committed into COPY_TABLE_FROM_ANOTHER_DB at [email protected].

SQL> conn maclean/[email protected]
Connected.

SQL> select count(*) from copy_table_from_another_db;
  COUNT(*)
----------
     51041

3.使用不同的复制copy形式:

copy命令存在多种数据复制的模式,分别为:
1.append模式,将查询到数据插入到目标表中；若目标表不存在,则会创建目标表:
2.create模式,首先创建目标表,之后将数据插入到目标表中；若目标表已存在,copy命令将报错:
ERROR:
ORA-00955: name is already used by an existing object

3.insert模式,只负责将数据插入到目标表.若目标表不存在则copy命令直接报错；当使用insert模式时,需要保证using字句指定的列和目标表上的列一一对应

4.replace模式,负责替换目标表中的数据.若目标表不存在,使用replace模式时copy命令会创建目标表并插入数据；若目标表存在,则copy会将该表先drop掉再重建并插入数据