long

LONG作为一种数据类型，在LOB大对象出现之后就不再推荐使用了。Oracle对LONG的态度是废弃，以至于一些新的工具对LONG类型的支持也并不好。

例如10g中推出的数据泵功能data pump expdp/impdp，在2个字符集完全一致(ZHS16GBK AL16UTF16)的数据库间导入、导出LONG 字段也可能存在乱码，这个现象在10g中比较常见。

对于该expdp/impdp long字段产生乱码的问题， Workaroud 的是用 exp/imp 传统导入导出工具来替代expdp /impdp，虽然export/import工具的速度不如data pump，但是因为是传统工具所以对于LONG这种近乎废弃的数据类型支持较好。

在11g上测试了一下，该乱码问题似乎已经得到修复：


SQL*Plus: Release 11.2.0.3.0 Production on 星期五 2月 22 06:13:53 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options


SQL> conn maclean/oracle
Connected.
SQL> 
SQL> 
SQL> 
SQL> 
SQL> create table tlong (t1 int, t2 long);

Table created.


SQL> insert into tlong values(1,'Maclean的测试');

1 row created.

SQL> commit;

Commit complete.

SQL> select t2 from tlong;

T2
--------------------------------------------------------------------------------
Maclean的测试



  1* select name,value$ from sys.props$ where name like '%CHARACTERSET%'
SQL> /

NAME                           VALUE$
------------------------------ --------------------------------------------------
NLS_CHARACTERSET               AL32UTF8
NLS_NCHAR_CHARACTERSET         UTF8


[oracle@vmac1 ~]$ expdp maclean/oracle dumpfile=tmp:tlong.dmp tables=maclean.tlong 

Export: Release 11.2.0.3.0 - Production on 星期五 2月 22 06:18:55 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
启动 "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** dumpfile=tmp:tlong.dmp tables=maclean.tlong 
正在使用 BLOCKS 方法进行估计...
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
使用 BLOCKS 方法的总估计: 64 KB
处理对象类型 TABLE_EXPORT/TABLE/TABLE
. . 导出了 "MACLEAN"."TLONG"                           5.421 KB       1 行
已成功加载/卸载了主表 "MACLEAN"."SYS_EXPORT_TABLE_01" 
******************************************************************************
MACLEAN.SYS_EXPORT_TABLE_01 的转储文件集为:
  /tmp/tlong.dmp
作业 "MACLEAN"."SYS_EXPORT_TABLE_01" 已于 06:19:14 成功完成



[oracle@vmac1 ~]$ impdp dumpfile=tmp:tlong.dmp remap_table=maclean.tlong:tlong1         

Import: Release 11.2.0.3.0 - Production on 星期五 2月 22 06:21:34 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Username: maclean
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
已成功加载/卸载了主表 "MACLEAN"."SYS_IMPORT_FULL_01" 
启动 "MACLEAN"."SYS_IMPORT_FULL_01":  maclean/******** dumpfile=tmp:tlong.dmp remap_table=maclean.tlong:tlong1 
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
. . 导入了 "MACLEAN"."TLONG1"                          5.421 KB       1 行
作业 "MACLEAN"."SYS_IMPORT_FULL_01" 已于 06:21:39 成功完成


SQL> select * from tlong1;




        T1
----------
T2
--------------------------------------------------------------------------------
         1
Maclean的测试

expdp/impdp导入导出Long字段类型产生乱码问题