oracle copy与postgresql copy

先介绍oracle copy，其实这个命令我到现在都没用过，也是做数据迁移的命令，注意这个命令是SQL*PLUS的命令，不是oracle的，通过这个命令可以在不同的表之间移动数据

1.基本语法：

COPY {FROM database | TO database | FROM database TO database} {APPEND|CREATE|INSERT|REPLACE} destination_table [(column, column, column, ...)] USING query

a) database：username/password@dbname

from database|to database可以任选一，缺少的一方默认使用当前sqlplus登录的数据库代替。

b) query：普通的select语句，多行时行尾使用连字符 ’-‘ 换行标记

支持的数据类型：CHAR,DATE,LONG,NUMBER,VARCHAR2

copy data把sqlplus作为数据流的管道，不需要中间文件，数据库名使用tns中的配置。query子句在数据源库上执行，结果直接发到目标库。

2.四种模式：

c) append：目标表不存在时先建表再插入数据，若存在直接加载数据。

d) create：直接建表，若目标表存在，报错退出。

e) insert：直接插入数据，若目标表不存在，报错退出。

f) replace：目标表存在时会先删除表，再重建及加载数据，一般很少用这个模式

注意：create、replace 后面没有table关键字，insert后面没有into和values关键字

l 参数：使用sqlplus参数，跟数据库无关。这些参数可以在sqlplus里，show 参数名来查看具体值。

a) arraysize：一个批次传输的数据量,默认值是100，可以在sqlplus里，使用show arraysize查看。

b) copycommit：定义多少批次自动commit，copycommit*arraysize=一次commit的数据量，这个乘积对事务大小有影响。默认是0，就是所有数据加载完毕才做一次commit，建议使用非0数字。

c) long：可以传输long数据的长度，默认值是20000

destination_table [(column, column, column, ...)] 目标表的列名，不指定的话，那么使用后面查询语句的列名

举例：

SQL> copy from sf/sf@skyread to sf/sf@skyread create tb1 using select * from am_role;

Array fetch/bind size is 15. (arraysize is 15)

Will commit when done. (copycommit is 0)

Maximum long size is 80. (long is 80)

CPY-0012: Datatype cannot be copied

SQL> desc am_role

Name Null? Type

----------------------------------------- -------- ----------------------------

ROLEID NOT NULL VARCHAR2(32 CHAR)

ROLENAME VARCHAR2(255 CHAR)

TAG VARCHAR2(255 CHAR)

MEMO VARCHAR2(2000 CHAR)

UPDATEUSER VARCHAR2(50 CHAR)

UPDATEDATE TIMESTAMP(6)

FAST VARCHAR2(1 CHAR)

看到数据类型不能匹配，这里有TIMESTAMP

SQL> select count(*) from A_USER_CONSUME;

COUNT(*)

----------

8632

SQL> desc A_USER_CONSUME

Name Null? Type

----------------------------------------- -------- ----------------------------

IMSI NOT NULL VARCHAR2(30)

DAYS NUMBER(38)

DIRECT_CONSUME NUMBER

MO_CONSUME NUMBER

SQL> copy from sf/sf@skyread to sf/sf@skyread create tb1 using select * from A_USER_CONSUME;

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 TB1 created.

8632 rows selected from sf@skyread.

8632 rows inserted into TB1.

8632 rows committed into TB1 at sf@skyread.

SQL> select count(*) from tb1;

COUNT(*)

----------

8632

注意这里from 和to 最少要指定一个，如果不写，那么默认在当前schema下，一定注意这里create后面没有table关键字，是直接跟表名的

oracle这里用copy的好处是比较灵活，当然这里和create table ... as select ...这种效果是一样的，但是如果是追加新的记录insert into ... select ...如果量很大的话，那么会产生大量的redo和undo，对IO有很大影响，这里copy是不生成undo信息，当然效果会好一些，实际情况用copy还是比较少的，这里只是了解一下oracle有这个功能

另外postgresql 也有copy这个命令，用起来也很灵活，是迁移数据的一个好工具

Syntax:

COPY table_name [ ( column [, ...] ) ]

FROM { 'filename' | STDIN }

[ [ WITH ] ( option [, ...] ) ]

COPY { table_name [ ( column [, ...] ) ] | ( query ) }

TO { 'filename' | STDOUT }

[ [ WITH ] ( option [, ...] ) ]

where option can be one of:

FORMAT format_name

OIDS [ boolean ]

DELIMITER 'delimiter_character'