周末继续看了看OCI接口的资料, 着重于以前没有搞懂的LONG/LOB类型处理接口, 写了一个DataCopy程序来作为学习的结果. DataCopy可以在两个Oracle库之间以比较快的速度拷贝数据, 这个速度至少要比EXP/IMP来迁移数据来得快, 并且支持最大100MB的LONG/LONG RAW/CLOB/BLOB值, 一般情况下够用了吧.

    没有说明文档, 只有命令行帮助.

DataCopy: Fast Oracle Data Copy (Demo Version), Release 2.0.1
(c) Copyright Lou Fangxin (AnySQL.net) 2010, all rights reserved.

Usage: datacopy keyword=value [,keyword=value,...]

Valid Keywords:
  user1  = username/password@tnsname for source database.
  user2  = username/password@tnsname for target database.
  table  = table name for both source and target.
  tables  = table name list for both source and target.
  table1  = source table name to query data from.
  table2  = target table name to insert data into.
  query1  = select SQL for source database.
  query2  = insert SQL for target database.
* wait    = wait time in microsecond after each array.
  read    = set DB_FILE_MULTIBLOCK_READ_COUNT at session level
  sort    = set SORT_AREA_SIZE at session level (UNIT:MB)
  hash    = set HASH_AREA_SIZE at session level (UNIT:MB)
  serial  = set _serial_direct_read to TRUE at session level
  array  = array fetch size
  rows    = print log information for every given rows.
  long    = maximum size for long, long raw, CLOB, BLOB columns.
* crypt  = encrypt the connection info only, no data copy (YES/NO).
  parfile = read command option from parameter file
* direct  = direct mode (YES/NO).
* nolog  = no archive log for direct mode (YES/NO).
* parallel= allow parallel load for direct mode (YES/NO).
* sync    = sync mode (INSERT,UPDATE,DELETE,UPDINS,INSUPD,DELINS).
* unique  = primary key or unique key columns of target table.
  log    = log file name for screen messages.

Notes:
  datacopy user1=scott/tiger user2=scott/tiger table=emp
  datacopy user1=scott/tiger user2=scott/tiger table=emp direct=yes
  datacopy user1=scott/tiger user2=scott/tiger table1=emp table2=emp_his

    数据拷贝中, 目标端数据库并不一定是INSERT操作, 也可以是UPDATE或DELETE, 或一段PL/SQL代码, 默认情况下是INSERT而已. 并且也只是实现了OCI的Array操作接口, 在真实情况下有什么实用价值, 就让各自去延伸了.