Oracle数据库bycopy的用法,了解sqlplus中的copy命令

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

以INSERT ..SELECT的方式将目标数据由源表插入到源表中,以此种方法复制表上的数据时可以灵活运行SELECT语句剔除那些无用的数据；同时也可以利用append nologging和并行parallel等特性加速数据复制速度.通过使用DBLINK数据库远程连接,INSERT...SELECT方式可以在数据库间复制表数据,当然要比在本库中复制表数据效率下降一些.

使用Oracle标准的导入导出工具exp/imp或者在10g以后使用DataPump数据泵技术,此种方法可以在数据库之间复制表数据,也可以用QUERY选项指定Where条件以筛选导出数据,10g中server端模式的数据泵极大地提高了导入导出的效率.

此外我们也可以使用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 TO

: database string, e.g., hr/your_password@d:chicago-mktg

: ONE of the keywords: APPEND, CREATE, INSERT or REPLACE

: a comma-separated list of destination column aliases

: any valid SQL SELECT statement

A missing FROM or TO clause uses the current SQL*Plus connection.

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

SQL>copy from maclean/maclean@clinica.rh2.oracle.com 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 maclean@clinica.rh2.oracle.com.

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/maclean@clinica.rh2.oracle.com 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 maclean@clinica.rh2.oracle.com.

51041 rows inserted into COPY_TABLE.

51041 rows committed into COPY_TABLE at DEFAULT HOST connection.

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

SQL>copy from maclean/maclean@clinica.rh2.oracle.com to maclean/maclean@clinicb.rh3.oracle.com 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 maclean@clinica.rh2.oracle.com.

51041 rows inserted into COPY_TABLE_FROM_ANOTHER_DB.

51041 rows committed into COPY_TABLE_FROM_ANOTHER_DB at maclean@clinicb.rh3.oracle.com.

SQL> conn maclean/maclean@clinicb.rh3.oracle.com

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掉再重建并插入数据