Oracle案例：遷移含有LONG字段的表詳情 - oracle,數據庫,oracle,SYS,字段,JavaScript,前端開發 mb43f60adeco02a 博客

Oracle案例：遷移含有LONG字段的表

在上週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.

一切正常。

------------------作者介紹-----------------------
姓名：黃廷忠

mb43f60adeco02a 博客

mb43f60adeco02a 博客

博客 / 詳情

Oracle案例：遷移含有LONG字段的表

Oracle案例：遷移含有LONG字段的表

1，配置NLS_LANG參數

2，創建測試表

3，使用常規的CTAS方法來遷移報錯，MOVE也報錯

4，使用expdp的network_link方式

5，通過COPY的方式

6，通過expdp方式來遷移

7，使用exp的方式來導數據

發佈評論

Product

Company

Support

Company

博客 / 詳情

Oracle案例：遷移含有LONG字段的表

Oracle案例：遷移含有LONG字段的表

1，配置NLS_LANG參數

2，創建測試表

3，使用常規的CTAS方法來遷移報錯，MOVE也報錯

4，使用expdp的network_link方式

5，通過COPY的方式

6，通過expdp方式來遷移

7，使用exp的方式來導數據

發佈 評論

發佈評論