当前位置： 首页 > MIGRATE > 正文

迁移含有LONG字段的表

huangtingzhong 发布于 2013-05-24 12:03MIGRATE抢沙发

在上周5遇到一个客户truncate了一张800G的表，表中含有LONG字段，异机还原+迁移表，折腾啊，总共还原了2天2夜才还原成功，中途遇到了空间不够等问题。

下面是测试表含有LONG字段的迁移的几种方法。

测试环境是:OS RHEL 4.8   DB:10.2.0.4

建议在带有long字段的表的迁移的时候，使用exp/imp的方式。

1，配置NLS_LANG参数

[oracle10g@rhel4 sql]$ export NLS_LANG='AMERICAN_AMERICA.ZHS16GBK';
[oracle10g@rhel4 sql]$ echo $NLS_LANG
AMERICAN_AMERICA.ZHS16GBK

2，创建测试表

 create table scott.test_long(htz varchar2(20),huangtingzhong long);
  insert into scott.test_long values('htz1','黄廷忠1');
  insert into scott.test_long values('htz2','黄廷忠2');
  insert into scott.test_long values('htz3','黄廷忠3');
  insert into scott.test_long values('htz4','黄廷忠4');
  insert into scott.test_long values('htz5','黄廷忠5');
  insert into scott.test_long values('htz6','黄廷忠6');
  commit;
www.htz.pw >set lines 200
www.htz.pw >select * from scott.test_long;
HTZ                  HUANGTINGZHONG
-------------------- --------------------------------------------------------------------------------
htz1                 黄廷忠1
htz2                 黄廷忠2
htz3                 黄廷忠3
htz4                 黄廷忠4
htz5                 黄廷忠5
htz6                 黄廷忠6
6 rows selected.

 

3，使用常规的CTAS方法来迁移报错，MOVE也报错

www.htz.pw >create table scott.test1_long as select * from scott.test_long;
create table scott.test1_long as select * from scott.test_long
                                       *
ERROR at line 1:
ORA-00997: illegal use of LONG datatype
www.htz.pw >alter table scott.test_long move;
alter table scott.test_long move
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

 

4，使用expdp的network_link方式

[oracle10g@rhel4 sql]$ impdp scott/oracle directory=exp_test logfile=long.log tables=test2_long remap_schema=scott:htz network_link=expdp_long
Import: Release 10.2.0.4.0 - 64bit Production on Saturday, 18 May, 2013 17:26:46
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=exp_test logfile=long.log tables=test2_long remap_schema=scott:htz network_link=expdp_long 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
ORA-31679: Table data object "HTZ"."TEST2_LONG" has long columns, and longs can not be loaded/unloaded using a network link
Job "SCOTT"."SYS_IMPORT_TABLE_01" completed with 1 error(s) at 17:26:50

5，通过COPY的方式

在生产中COPY命令使用时我们需要注意几个方面
1，多少条记录后提交一次(copycommit(非0)*arraysize)，如果UNDO表空间足够大的时候，我们不需要考虑这个。
2，配置一下long的值，默认是80，弄个最大值2000000000

在群中的E总，说带有LONG字段的表使用COPY的方式会出问题，但是我不知道会出什么问题。

www.htz.pw >show copycommit;
copycommit 0
www.htz.pw >show arraysize
arraysize 15
www.htz.pw >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.
www.htz.pw >copy from scott/oracle@orcl10g create scott.test1_long using select * from scott.test_long;
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 SCOTT.TEST1_LONG created.
   6 rows selected from scott@orcl10g.
   6 rows inserted into SCOTT.TEST1_LONG.
   6 rows committed into SCOTT.TEST1_LONG at DEFAULT HOST connection.

通过COPY迁移出来的记录是正常的，也没有出现乱码现象。

www.htz.pw >select * from scott.test1_long;

HTZ                  HUANGTINGZHONG
-------------------- --------------------------------------------------------------------------------
htz1                 黄廷忠1
htz2                 黄廷忠2
htz3                 黄廷忠3
htz4                 黄廷忠4
htz5                 黄廷忠5
htz6                 黄廷忠6

6，通过expdp方式来迁移

在使用expdp方式来迁移的时候，MACLEAN中提到下面这句话“
例如10g中推出的数据泵功能data pump expdp/impdp，在2个字符集完全一致(ZHS16GBK AL16UTF16)的数据库间导入、导出LONG  字段也可能存在乱码，这个现象在10g中比较常见。”由于expdp/impdp导long会出现乱码，建议使用exp/imp工具来导包含有long字段的表，在他的测试中11G以上乱码现象得到修复。

www.htz.pw >create directory dump_test as '/tmp';
Directory created.
www.htz.pw >grant write,read on directory dump_test to scott;
Grant succeeded.
www.htz.pw >!expdp scott/oracle directory=dump_test dumpfile=test1_long_expdp.dmp logfile=test1_long_expdp.log tables=test_long;
Export: Release 10.2.0.4.0 - 64bit Production on Friday, 24 May, 2013 10:11:09
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dump_test dumpfile=test1_long_expdp.dmp logfile=test1_long_expdp.log tables=test_long 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 64 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."TEST_LONG"                         5.335 KB       6 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /tmp/test1_long_expdp.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at 10:11:12
导出成功，没有报任何的错误
drop掉相当的表
www.htz.pw >drop table scott.test_long purge;
Table dropped.
导入数据
www.htz.pw >!impdp scott/oracle directory=dump_test dumpfile=test1_long_expdp.dmp logfile=test1_long_expdp.log tables=test_long;
Import: Release 10.2.0.4.0 - 64bit Production on Friday, 24 May, 2013 10:12:44
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=dump_test dumpfile=test1_long_expdp.dmp logfile=test1_long_expdp.log tables=test_long 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."TEST_LONG"                         5.335 KB       6 rows
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at 10:12:46

查看导入的数据，一切正常没有出现乱码的现象

www.htz.pw >select * from scott.test_long;
HTZ                  HUANGTINGZHONG
-------------------- --------------------------------------------------------------------------------
htz1                 黄廷忠1
htz2                 黄廷忠2
htz3                 黄廷忠3
htz4                 黄廷忠4
htz5                 黄廷忠5
htz6                 黄廷忠6
6 rows selected.

7，使用exp的方式来导数据

         在使用exp方式来导数据库的使用，建议配置direct=y与recordlength两个参数来提高性能，在imp导入的时候，如果在UNDO表空间不够的情况下，建议加上commit=y，在有long字段的imp时，每插入一条记录都会commit一次，而是达到buffer值时才commit，这点需要注意，commit次数的增加会导致性能的下降。

www.htz.pw >!exp scott/oracle file=/tmp/test1_long_exp.dmp log=test1_long_exp.log tables=test_long
Export: Release 10.2.0.4.0 - Production on Fri May 24 10:11:42 2013
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Conventional Path ...
. . exporting table                      TEST_LONG          6 rows exported
Export terminated successfully without warnings.
www.htz.pw >drop table scott.test_long purge;
Table dropped.

www.htz.pw >!imp scott/oracle file=/tmp/test1_long_exp.dmp log=test1_long_exp.log tables=test_long
Import: Release 10.2.0.4.0 - Production on Fri May 24 10:13:38 2013
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table                    "TEST_LONG"          6 rows imported
Import terminated successfully without warnings.

www.htz.pw >select * from scott.test_long;

HTZ                  HUANGTINGZHONG
-------------------- --------------------------------------------------------------------------------
htz1                 黄廷忠1
htz2                 黄廷忠2
htz3                 黄廷忠3
htz4                 黄廷忠4
htz5                 黄廷忠5
htz6                 黄廷忠6

6 rows selected.
一切正常。

 

赏

您可能还会对这些文章感兴趣！

迁移含有LONG字段的表：等您坐沙发呢！

发表评论

gravatar

? razz sad evil ! smile oops grin eek shock ??? cool lol mad twisted roll wink idea arrow neutral cry mrgreen

快捷键：Ctrl+Enter