逻辑备份/恢复 – 第2页

EXP-00104: datatype (BINARY_DOUBLE) 错误

标题：EXP-00104: datatype (BINARY_DOUBLE) 错误

进行了一次使用exp数据迁移,客户和我说丢失了表,我觉得比较奇怪,分析日志发现EXP-00104: datatype (BINARY_DOUBLE)错误,对其单表进行导出,发现问题重现

[oracle@app73 ~]$ exp system/oracle123 file=/Data/1.dmp tables=CHF.T_XIFENFEI
Export: Release 11.1.0.6.0 - Production on Wed Oct 14 23:25:31 2015
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.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 ...
Current user changed to CHF
. . exporting table                  T_XIFENFEI
EXP-00104: datatype (BINARY_DOUBLE) of column NUMCOMMISSIONRATE in table
 CHF.T_XIFENFEI is not supported, table will not be exported

数据库版本

SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE    11.1.0.6.0      Production
TNS for Linux: Version 11.1.0.6.0 - Production
NLSRTL Version 11.1.0.6.0 - Production

表结构

SQL> DESC CHF.T_XIFENFEI
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
…………
 NUMPRO                                             NUMBER
 NUMCOMMISSIONRATE                                  BINARY_DOUBLE
 NUMSEQ                                             BINARY_DOUBLE
 NUMMARK                                            NUMBER
…………

解决办法,使用expdp/impdp代替exp/imp

[oracle@app73 ~]$ expdp system/manager dumpfile=exp.dmp tables=CHF.T_XIFENFEI  directory=exp_dir
Export: Release 11.1.0.6.0 - 64bit Production on Wednesday, 14 October, 2015 23:30:38
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01":system/******** dumpfile=exp.dmp tables=CHF.T_XIFENFEI directory=exp_dir
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 959 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "CHF"."T_XIFENFEI"                    814.7 MB 1601202 rows
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
  /tmp/exp.dmp
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 23:31:31
[oracle@app101-2 Data]$ impdp system/oracle dumpfile=T_XIFENFEI.dmp tables=CHF.T_XIFENFEI directory=exp_dir
Import: Release 11.2.0.4.0 - Production on Wed Oct 14 23:44:35 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning and Data Mining options
Master table "SYSTEM"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TABLE_01":system/******** dumpfile=T_XIFENFEI.dmp tables=CHF.T_XIFENFEI directory=exp_dir
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "CHF"."T_XIFENFEI"                    814.7 MB 1601202 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_TABLE_01" successfully completed at Wed Oct 14 23:45:37 2015 elapsed 0 00:01:02

出现此类错误,也和自己的粗心有关系,没有仔细检查导出日志,另外该问题从10.1.0.2就有了,直到现在才发现,证明知识点欠缺.同样对于binary_float也有一样问题.
具体可以参考：Export Failed Due To EXP-104 Error, Datatype (Binary_double) Is Not Supported [ID 421591.1]

9.2.0.8 exp导出dmp导入报Segmentation fault/段错误故障解决

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：9.2.0.8 exp导出dmp导入报Segmentation fault/段错误故障解决

在9.2.0.8 rac环境中,使用exp导出来dmp文件任何报错(按单个表，按用户导出,使用tns方式远程exp导出)，包括重启数据库后导出，无法导入到其他数据库中(本库,tns方式远程导入,ftp传输到远程导入,9i/10g/11g版本)报错类似有setillegal instruction(coredump)，段错误，Segmentation fault等，以下列出来几个报错信息

--导入11.2.0.2版本
Import: Release 11.2.0.2.0 - Production on Fri Mar 27 20:09:51 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character setillegal instruction(coredump)
--导入11.2.0.4版本
Import: Release 11.2.0.4.0 - Production on Thu Apr 2 21:12:18 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by UNIONDB, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set段错误
--导入10.2.0.5版本
Import: Release 10.2.0.5.0 - Production on Thu Apr 2 21:44:47 2015
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by UNIONDB, not by you
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses ZHS16GBK character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)段错误

增加trace=y跟踪分析

Trace file /u01/app/oracle/diag/rdbms/qsng/qsng/trace/qsng_ora_16772.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1
System name: Linux
Node name: localhost.localdomain
Release: 2.6.32-300.10.1.el5uek
Version: #1 SMP Wed Feb 22 17:37:40 EST 2012
Machine: x86_64
Instance name: qsng
Redo thread mounted by this instance: 1
Oracle process number: 50
Unix process pid: 16772, image: oracle@localhost.localdomain (TNS V1-V3)
*** 2015-04-02 21:23:11.764
*** SESSION ID:(72.16901) 2015-04-02 21:23:11.764
*** CLIENT ID:() 2015-04-02 21:23:11.764
*** SERVICE NAME:(SYS$USERS) 2015-04-02 21:23:11.764
*** MODULE NAME:(imp@localhost.localdomain (TNS V1-V3)) 2015-04-02 21:23:11.764
*** ACTION NAME:() 2015-04-02 21:23:11.764
=====================
PARSING IN CURSOR #139814426861864 len=34 dep=1 uid=0 oct=42 lid=0 tim=1427980991763775 hv=3913151867 ad='7f29153b02c8' sqlid='14ys3d7nmvxbv'
ALTER SESSION SET SQL_TRACE = TRUE
END OF STMT
EXEC #139814426861864:c=0,e=65,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,plh=0,tim=1427980991756277
CLOSE #139814426861864:c=0,e=4,dep=1,type=0,tim=1427980991764938
=====================
PARSING IN CURSOR #139814426870416 len=67 dep=0 uid=0 oct=47 lid=0 tim=1427980991765036 hv=4244958165 ad='54d651fb0' sqlid='78wmnnryh9uyp'
BEGIN EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE = TRUE'; END;
END OF STMT
EXEC #139814426870416:c=2999,e=10041,p=0,cr=0,cu=0,mis=1,r=1,dep=0,og=1,plh=0,tim=1427980991765033
CLOSE #139814426870416:c=0,e=24,dep=0,type=0,tim=1427980991765547
=====================
PARSING IN CURSOR #139814426870416 len=50 dep=0 uid=0 oct=47 lid=0 tim=1427980991767424 hv=2924529365 ad='5450bc4b0' sqlid='8ppuwaur51jqp'
BEGIN SYS.DBMS_EXPORT_EXTENSION.SET_STATSON; END;
END OF STMT
PARSE #139814426870416:c=1999,e=1828,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1427980991767422
=====================
PARSING IN CURSOR #139814426240744 len=41 dep=1 uid=0 oct=42 lid=0 tim=1427980991768326 hv=2301085140 ad='0' sqlid='g63fjfq4kgjfn'
alter session set TIMED_STATISTICS = TRUE
END OF STMT
PARSE #139814426240744:c=0,e=206,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991768325
EXEC #139814426240744:c=1000,e=5348,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991773803
CLOSE #139814426240744:c=0,e=4,dep=1,type=0,tim=1427980991773879
EXEC #139814426870416:c=2000,e=6363,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1427980991773926
=====================
PARSING IN CURSOR #139814426340472 len=37 dep=1 uid=0 oct=3 lid=0 tim=1427980991776092 hv=1398610540 ad='553f2f718' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #139814426340472:c=1000,e=824,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1427980991776091
=====================
PARSING IN CURSOR #139814426336856 len=210 dep=2 uid=0 oct=3 lid=0 tim=1427980991791474 hv=864012087 ad='547f6c0c0' sqlid='96g93hntrzjtr'
select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, timestamp#, sample_size, minimum, maximum,
 distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj#=:1 and intcol#=:2
END OF STMT
PARSE #139814426336856:c=1000,e=1332,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,plh=0,tim=1427980991791472
EXEC #139814426336856:c=2000,e=21773,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=3,plh=2239883476,tim=1427980991813432
FETCH #139814426336856:c=0,e=78,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=1427980991813580
STAT #139814426336856 id=1 cnt=0 pid=0 pos=1 obj=448 op='TABLE ACCESS BY INDEX ROWID HIST_HEAD$ (cr=2 pr=0 pw=0 time=108 us)'
STAT #139814426336856 id=2 cnt=0 pid=1 pos=1 obj=450 op='INDEX RANGE SCAN I_HH_OBJ#_INTCOL# (cr=2 pr=0 pw=0 time=98 us)'
CLOSE #139814426336856:c=0,e=5,dep=2,type=3,tim=1427980991824838
EXEC #139814426336856:c=0,e=32,p=0,cr=0,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=1427980991825112
FETCH #139814426336856:c=0,e=15,p=0,cr=2,cu=0,mis=0,r=0,dep=2,og=3,plh=2239883476,tim=1427980991825174
CLOSE #139814426336856:c=0,e=4,dep=2,type=3,tim=1427980991825230
EXEC #139814426340472:c=5000,e=49516,p=0,cr=4,cu=0,mis=1,r=0,dep=1,og=4,plh=3684871272,tim=1427980991825784
FETCH #139814426340472:c=0,e=74,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1427980991825935
STAT #139814426340472 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=59 us cost=1 size=15 card=1)'
CLOSE #139814426340472:c=1000,e=79,dep=1,type=0,tim=1427980991826064
=====================
PARSING IN CURSOR #139814426240888 len=50 dep=0 uid=0 oct=3 lid=0 tim=1427980991827030 hv=2950256760 ad='548d195c8' sqlid='gnhvzburxkq3s'
SELECT value from sys.exu8opt where parameter = :1
END OF STMT
PARSE #139814426240888:c=7999,e=52316,p=0,cr=6,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1427980991827029
=====================
PARSING IN CURSOR #139814426339424 len=37 dep=1 uid=0 oct=3 lid=0 tim=1427980991827683 hv=1398610540 ad='553f2f718' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #139814426339424:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991827681
EXEC #139814426339424:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991827862
FETCH #139814426339424:c=0,e=19,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1427980991827938
STAT #139814426339424 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=10 us cost=1 size=15 card=1)'
CLOSE #139814426339424:c=1000,e=79,dep=1,type=0,tim=1427980991828062
EXEC #139814426240888:c=2000,e=2039,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=2941868576,tim=1427980991829442
FETCH #139814426240888:c=0,e=46,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=2941868576,tim=1427980991829653
STAT #139814426240888 id=1 cnt=1 pid=0 pos=1 obj=0 op='FIXED TABLE FULL X$OPTION (cr=0 pr=0 pw=0 time=42 us cost=0 size=81 card=1)'
CLOSE #139814426240888:c=0,e=77,dep=0,type=0,tim=1427980991829862
CLOSE #139814426870416:c=0,e=15,dep=0,type=0,tim=1427980991830042
=====================
PARSING IN CURSOR #139814426870416 len=54 dep=0 uid=0 oct=47 lid=0 tim=1427980991830154 hv=1322764242 ad='54aea3138' sqlid='4jv63yp7dgjyk'
BEGIN SYS.DBMS_EXPORT_EXTENSION.SET_NO_OUTLINES; END;
END OF STMT
PARSE #139814426870416:c=0,e=67,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1427980991830153
=====================
PARSING IN CURSOR #139814426340328 len=48 dep=1 uid=0 oct=42 lid=0 tim=1427980991830368 hv=663246278 ad='0' sqlid='7kgh52cmshpf6'
alter session set CREATE_STORED_OUTLINES = FALSE
END OF STMT
PARSE #139814426340328:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991830367
EXEC #139814426340328:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991830510
CLOSE #139814426340328:c=0,e=3,dep=1,type=0,tim=1427980991830557
EXEC #139814426870416:c=0,e=338,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1427980991830605
CLOSE #139814426870416:c=0,e=19,dep=0,type=1,tim=1427980991830823
=====================
PARSING IN CURSOR #139814426867336 len=59 dep=0 uid=0 oct=47 lid=0 tim=1427980991830946 hv=1151361180 ad='551bd30c0' sqlid='akubp592a0s4w'
BEGIN SYS.DBMS_EXPORT_EXTENSION.SET_NLS_NUMERIC_CHAR; END;
END OF STMT
PARSE #139814426867336:c=0,e=71,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1427980991830945
=====================
PARSING IN CURSOR #139814426339136 len=45 dep=1 uid=0 oct=42 lid=0 tim=1427980991831195 hv=1639245916 ad='0' sqlid='45sy639hv9u2w'
alter session set NLS_NUMERIC_CHARACTERS='.,'
END OF STMT
PARSE #139814426339136:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991831194
EXEC #139814426339136:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991831347
CLOSE #139814426339136:c=0,e=3,dep=1,type=0,tim=1427980991831396
EXEC #139814426867336:c=0,e=347,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1427980991831438
=====================
PARSING IN CURSOR #139814426328016 len=37 dep=1 uid=0 oct=3 lid=0 tim=1427980991832676 hv=1398610540 ad='553f2f718' sqlid='grwydz59pu6mc'
select text from view$ where rowid=:1
END OF STMT
PARSE #139814426328016:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991832675
EXEC #139814426328016:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991832903
FETCH #139814426328016:c=1000,e=45,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1427980991833000
STAT #139814426328016 id=1 cnt=1 pid=0 pos=1 obj=69 op='TABLE ACCESS BY USER ROWID VIEW$ (cr=1 pr=0 pw=0 time=22 us cost=1 size=15 card=1)'
CLOSE #139814426328016:c=0,e=63,dep=1,type=1,tim=1427980991833122
=====================
PARSING IN CURSOR #139814426339280 len=35 dep=0 uid=0 oct=3 lid=0 tim=1427980991834448 hv=994821361 ad='5466a8e58' sqlid='3mwu4pcxnrj7j'
SELECT USERID,NAME FROM SYS.IMP9USR
END OF STMT
PARSE #139814426339280:c=2000,e=2133,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=2709293936,tim=1427980991834446
EXEC #139814426339280:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=2709293936,tim=1427980991834763
FETCH #139814426339280:c=0,e=68,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=2709293936,tim=1427980991835015
PARSE #139814426328016:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991836151
EXEC #139814426328016:c=0,e=43,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991836279
FETCH #139814426328016:c=0,e=20,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1427980991836349
CLOSE #139814426328016:c=0,e=16,dep=1,type=3,tim=1427980991836409
=====================
PARSING IN CURSOR #139814426320856 len=34 dep=0 uid=0 oct=3 lid=0 tim=1427980991837576 hv=3320476556 ad='551865ae8' sqlid='33pm4s32ynwwc'
SELECT NAME,VALUE FROM SYS.EXU9NLS
END OF STMT
PARSE #139814426320856:c=1000,e=1857,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=415205717,tim=1427980991837575
EXEC #139814426320856:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=415205717,tim=1427980991837843
FETCH #139814426320856:c=0,e=83,p=0,cr=2,cu=0,mis=0,r=1,dep=0,og=1,plh=415205717,tim=1427980991838058
FETCH #139814426320856:c=0,e=20,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=415205717,tim=1427980991838214
FETCH #139814426320856:c=0,e=16,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,plh=415205717,tim=1427980991838355
FETCH #139814426320856:c=0,e=16,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,plh=415205717,tim=1427980991838483
STAT #139814426320856 id=1 cnt=3 pid=0 pos=1 obj=98 op='TABLE ACCESS FULL PROPS$ (cr=5 pr=0 pw=0 time=72 us cost=2 size=84 card=3)'
CLOSE #139814426320856:c=0,e=8,dep=0,type=0,tim=1427980991838734
PARSE #139814426328016:c=0,e=20,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991839322
EXEC #139814426328016:c=0,e=23,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=3684871272,tim=1427980991839430
FETCH #139814426328016:c=0,e=17,p=0,cr=2,cu=0,mis=0,r=1,dep=1,og=4,plh=3684871272,tim=1427980991839495
CLOSE #139814426328016:c=0,e=15,dep=1,type=3,tim=1427980991839554
=====================
PARSING IN CURSOR #139814426320856 len=37 dep=0 uid=0 oct=3 lid=0 tim=1427980991842425 hv=2737225759 ad='54cd0a630' sqlid='as799jkjkdh0z'
SELECT COMPATIBLE FROM SYS.IMP9COMPAT
END OF STMT
PARSE #139814426320856:c=3000,e=3443,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=1128103955,tim=1427980991842424
EXEC #139814426320856:c=0,e=39,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1128103955,tim=1427980991842721
FETCH #139814426320856:c=4999,e=4966,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1128103955,tim=1427980991847851
CLOSE #139814426867336:c=0,e=18,dep=0,type=1,tim=1427980991848283
=====================
PARSING IN CURSOR #139814426863200 len=65 dep=0 uid=0 oct=47 lid=0 tim=1427980991848389 hv=1092642237 ad='5573014c0' sqlid='6mk0b8p0k0tdx'
BEGIN SYS.DBMS_EXPORT_EXTENSION.SET_IMP_TIMEZONE('+08:00'); END;
END OF STMT
PARSE #139814426863200:c=0,e=66,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=0,tim=1427980991848387
=====================
PARSING IN CURSOR #139814424939968 len=36 dep=1 uid=0 oct=42 lid=0 tim=1427980991849135 hv=938329428 ad='0' sqlid='gcqwsz0vyvjan'
alter session set TIME_ZONE='+08:00'
END OF STMT
PARSE #139814424939968:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991849133
EXEC #139814424939968:c=0,e=8,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,plh=0,tim=1427980991849270
CLOSE #139814424939968:c=0,e=3,dep=1,type=0,tim=1427980991849317
EXEC #139814426863200:c=0,e=882,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1427980991849392
=====================
PARSING IN CURSOR #139814426317816 len=28 dep=0 uid=0 oct=3 lid=0 tim=1427980991850463 hv=1877579296 ad='550faead8' sqlid='7wf7949rym5j0'
SELECT DBTIMEZONE FROM DUAL
END OF STMT
PARSE #139814426317816:c=0,e=827,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=1388734953,tim=1427980991850462
EXEC #139814426317816:c=0,e=38,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=1388734953,tim=1427980991850766
FETCH #139814426317816:c=0,e=10,p=0,cr=0,cu=0,mis=0,r=1,dep=0,og=1,plh=1388734953,tim=1427980991850897
STAT #139814426317816 id=1 cnt=1 pid=0 pos=1 obj=0 op='FAST DUAL (cr=0 pr=0 pw=0 time=2 us cost=2 size=0 card=1)'
CLOSE #139814426317816:c=0,e=6,dep=0,type=0,tim=1427980991851087
STAT #139814426320856 id=1 cnt=1 pid=0 pos=1 obj=0 op='HASH JOIN (cr=0 pr=0 pw=0 time=4973 us cost=0 size=2115 card=1)'
STAT #139814426320856 id=2 cnt=1 pid=1 pos=1 obj=0 op='FIXED TABLE FULL X$KSPPI (cr=0 pr=0 pw=0 time=3099 us cost=0 size=81 card=1)'
STAT #139814426320856 id=3 cnt=982 pid=1 pos=2 obj=0 op='FIXED TABLE FULL X$KSPPCV (cr=0 pr=0 pw=0 time=1112 us cost=0 size=203400 card=100)'
CLOSE #139814426320856:c=0,e=191,dep=0,type=0,tim=1427980991891702
STAT #139814426339280 id=1 cnt=1 pid=0 pos=1 obj=22 op='TABLE ACCESS CLUSTER USER$ (cr=2 pr=0 pw=0 time=68 us cost=1 size=18 card=1)'
STAT #139814426339280 id=2 cnt=1 pid=1 pos=1 obj=11 op='INDEX UNIQUE SCAN I_USER# (cr=1 pr=0 pw=0 time=34 us cost=0 size=0 card=1)'
CLOSE #139814426339280:c=0,e=123,dep=0,type=0,tim=1427980991891866
CLOSE #139814426863200:c=0,e=17,dep=0,type=0,tim=1427980991891919

这里分析，未发现任何错误，导出程序直接报Segmentation fault终止

原因分析
通过各种方式导入都报类似错误，初步确定是dmp文件异常，而dmp异常有几种可能性：
1.exp程序异常导致dmp文件异常，这里使用tns方式导出，排除该种可能
2.数据库内存出现异常导致dmp文件异常，重启数据库依旧，排除该种可能
3.因为测试过多种平台，版本，本地，远程导入，排除兼容性，远程传输损坏等
4.因为以前正常，突然异常，坏可以exp程序调用包异常，这种故障不能排除

解决方案
鉴于此，通过expcat.sql重新安装相关包，发现执行这个之后，exp导出程序，再imp导入一切正常
执行catexp.sql不影响生产库，只影响exp/imp程序

conn / as sysdba
@?/rdbms/admin/catexp.sql

这里再次提醒我们看到exp导出成功,不能证明imp一定ok,最好通过imp进行导入测试

解决imp导入数据报IMP-00098错误

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：解决imp导入数据报IMP-00098错误

晚上十点多，准备睡觉了，一个朋友在qq上找到我,说他们数据库imp异常,希望我帮忙看看
大概情况
使用exp/imp升级并迁移数据库从win 10.2.0.1 升级到linux 11.2.0.3 由于硬盘空间不够，使用exp把win上面数据导出来，格式化掉win的数据文件所在硬盘格式化并加入到linux系统中(也就是说，故障之时，只有dmp文件，没有了数据文件）,因为原库没有了,dmp又报错,所以担心了起来.
imp报错信息

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 Tes
Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
export client uses US7ASCII character set (possible charset conversion)
. . importing table                "ART_T_ARCTYPE"          0 rows imported
. . importing table                "ART_T_ARTICLE"          0 rows imported
……
. . importing table            "EVT_T_ACCEPT_CODE"          1 rows imported
. . importing table            "EVT_T_ACCEPT_FLOW"
 illegal lob length marker 51166
 bytesread = 00000000000
 TABLE =EVT_T_ACCEPT_FLOW   EVT_T_ACCEPT_FLOW
IMP-00098: INTERNAL ERROR: impgst2
IMP-00028: partial import of previous table rolled back: 680071 rows rolled back
IMP-00008: unrecognized statement in the export file:
  ??????????????????
IMP-00008: unrecognized statement in the export file:
  .:
IMP-00008: unrecognized statement in the export file:
  $;
IMP-00008: unrecognized statement in the export file:
  ||$
…………

这里可以知道数据库exp的客户端是版本是10.2.0.1,使用编码是US7ASCII,但是导入的库编码是ZHS16GBK
exp日志信息

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 US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
EXP-00091: Exporting questionable statistics.
. . exporting table              EVT_T_ACCEPT_FLOW    3470071 rows exported
. . exporting table             EVT_T_ACCEPT_FLOW1    2707606 rows exported
……
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully with warnings.

这里我们可以知道数据库编码是ZHS16GBK,exp客户端编码为US7ASCII,因此到导出过程中发生了一次由ZHS16GBK到US7ASCII的编码转换
检查新库发现,其db编码为ZHS16GBK,新库的NLS设置为:nls_lang=AMERICAN_AMERICA.ZHS16GBK
整个导出流程是:db编码为ZHS16GBK的数据库被客户端编码为US7ASCII的exp导出,然后被编码为ZHS16GBK的imp导入到db编码为ZHS16GBK的数据库中
故障可能性定位
1.可能在从ZHS16GBK到US7ASCII的编码转换中发生数据异常,也就是说dmp文件本身异常(我们最不希望出现的结果)
2.由于数据库导出过场会发生编码转换(ZHS16GBK->US7ASCII),而导入过程未发生编码转换(ZHS16GBK->ZHS16GBK),从而出现异常
分析并解决
1. 使用dul扫描dmp文件发现汉字都能够识别,而且exp成功的记录条数在dul扫描中都完全正常恢复出来,证明dmp文件是正常的
2. 基于dmp文件正常,编码转换的过程,得出结论在该库的imp过程中设置nls_lang=AMERICAN_AMERICA.US7ASCII问题应该就可以正常解决.果不其然,设置了NLS_LANG之后imp导入数据OK
得出结论
1. 做升级,迁移尽快保留多一份数据,这次吓得不轻
2. exp/imp最好前后客户端编码一致,否则可能被转换晕
如果遭遇真的dmp损坏情况,类似:IMP-00098: INTERNAL ERROR: impgst2,可以联系我们,提供专业处理

ORACLE 12C 在datapump方面增强参数

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE 12C 在datapump方面增强参数

在阅读ORACLE 12C datapump相关文档之时,发现有两个比较欣喜的参数LOGTIME和SQLFILE,鉴于他们是12C新增加参数,对他们的使用方法和用途进行简单说明
LOGTIME参数
该参数可以用于expdp/impdp,主要作用是记录执行步骤的开始时间,精确到微秒,使用语法为

LOGTIME=[NONE | STATUS | LOGFILE | ALL]
• NONE--No timestamps on status or log file messages (same as default)
• STATUS--Timestamps on status messages only
• LOGFILE--Timestamps on log file messages only
• ALL--Timestamps on both status and log file messages

该参数主要在我们对于一些数据库迁移升级项目使用datapump的时候,在测试阶段能够通过该参数发现哪一步执行时间较长,然后对其调优减少执行时间;另外一点就是可以通过做减法精确到具体的时间(毫米),我们可以知道我们的datapump主要耗时在哪一步,做到心中有数不慌

LOGTIME测试

SQL> conn chf/xifenfei@pdb
已连接。
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         3 PDB                            READ WRITE NO
SQL> create directory temp as 'e:\';
目录已创建。
SQL> create table t_xifenfei as select * from dba_objects;
表已创建。
SQL> create index ind_t_xifenfei on t_xifenfei(object_id);
索引已创建。
C:\Users\XIFENFEI>expdp chf/xifenfei@pdb dumpfile=t_xifenfei.dmp tables=t_xifenf
ei logfile=t_xifenfei.log directory=temp REUSE_DUMPFILES=yes LOGTIME=all
Export: Release 12.1.0.1.0 - Production on 星期日 7月 14 20:11:24 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
14-7月 -13 20:11:35.961: 启动 "CHF"."SYS_EXPORT_TABLE_01":  chf/********@pdb dumpfile=t_xifenfei.dmp
 tables=t_xifenfei logfile=t_xifenfei.log directory=temp REUSE_DUMPFILES=yes LOGTIME=all
14-7月 -13 20:11:37.703: 正在使用 BLOCKS 方法进行估计...
14-7月 -13 20:11:40.636: 处理对象类型 TABLE_EXPORT/TABLE/TABLE_DATA
14-7月 -13 20:11:40.825: 使用 BLOCKS 方法的总估计: 13 MB
14-7月 -13 20:11:48.802: 处理对象类型 TABLE_EXPORT/TABLE/TABLE
14-7月 -13 20:11:54.543: 处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
14-7月 -13 20:11:57.204: 处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
14-7月 -13 20:11:59.269: 处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
14-7月 -13 20:11:59.306: 处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/MARKER
14-7月 -13 20:12:36.563: . . 导出了 "CHF"."T_XIFENFEI"  10.36 MB   90865 行
14-7月 -13 20:12:37.527: 已成功加载/卸载了主表 "CHF"."SYS_EXPORT_TABLE_01"
14-7月 -13 20:12:37.533: ******************************************************************************
14-7月 -13 20:12:37.537: CHF.SYS_EXPORT_TABLE_01 的转储文件集为:
14-7月 -13 20:12:37.547:   E:\T_XIFENFEI.DMP
14-7月 -13 20:12:37.577: 作业 "CHF"."SYS_EXPORT_TABLE_01" 已于 星期日 7月 14 20:12:37 2013 elapsed 0 00:01:06 成功完成

SQLFILE参数
该参数可以用于impdp,主要作用是未真实在目标端执行导入的情况下,生成sql文件包含该dmp文件的所有ddl语句,使用语法为

SQLFILE=[directory_object:]file_name

注意事项:
1.directory_object可以不和impdp的DIRECTORY参数不一致,如果是一样,directory_object可以省略
2.SQLFILE文件必须写入到磁盘之上,不能写入到ASM中
3.SQLFILE和QUERY参数冲突,不能同时使用

SQLFILE测试

C:\Users\XIFENFEI>impdp chf/xifenfei@pdb dumpfile=t_xifenfei.dmp tables=t_xifenfei logfile=t_xifenfei.log
 directory=temp  sqlfile=t_xifenfei.sql
Import: Release 12.1.0.1.0 - Production on 星期日 7月 14 20:42:13 2013
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
连接到: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Produc
tion
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
已成功加载/卸载了主表 "CHF"."SYS_SQL_FILE_TABLE_01"
启动 "CHF"."SYS_SQL_FILE_TABLE_01":  chf/********@pdb dumpfile=t_xifenfei.dmp ta
bles=t_xifenfei logfile=t_xifenfei.log directory=temp sqlfile=t_xifenfei.sql
处理对象类型 TABLE_EXPORT/TABLE/TABLE
处理对象类型 TABLE_EXPORT/TABLE/INDEX/INDEX
处理对象类型 TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
处理对象类型 TABLE_EXPORT/TABLE/STATISTICS/MARKER
作业 "CHF"."SYS_SQL_FILE_TABLE_01" 已于 星期日 7月 14 20:42:25 2013 elapsed 0 00:00:08 成功完成

t_xifenfei.sql内容

-- CONNECT CHF
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "CHF"."T_XIFENFEI"
   (	"OWNER" VARCHAR2(128 BYTE),
	"OBJECT_NAME" VARCHAR2(128 BYTE),
	"SUBOBJECT_NAME" VARCHAR2(128 BYTE),
	"OBJECT_ID" NUMBER,
	"DATA_OBJECT_ID" NUMBER,
	"OBJECT_TYPE" VARCHAR2(23 BYTE),
	"CREATED" DATE,
	"LAST_DDL_TIME" DATE,
	"TIMESTAMP" VARCHAR2(19 BYTE),
	"STATUS" VARCHAR2(7 BYTE),
	"TEMPORARY" VARCHAR2(1 BYTE),
	"GENERATED" VARCHAR2(1 BYTE),
	"SECONDARY" VARCHAR2(1 BYTE),
	"NAMESPACE" NUMBER,
	"EDITION_NAME" VARCHAR2(128 BYTE),
	"SHARING" VARCHAR2(13 BYTE),
	"EDITIONABLE" VARCHAR2(1 BYTE),
	"ORACLE_MAINTAINED" VARCHAR2(1 BYTE)
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" ;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/INDEX
CREATE INDEX "CHF"."IND_T_XIFENFEI" ON "CHF"."T_XIFENFEI" ("OBJECT_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "USERS" PARALLEL 1 ;
  ALTER INDEX "CHF"."IND_T_XIFENFEI" NOPARALLEL;
-- new object type path: TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
-- new object type path: TABLE_EXPORT/TABLE/STATISTICS/MARKER

ORACLE 12C datapump关于LOGTIME和SQLFILE参数

使用dbms_pumpdata执行expdp操作

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：使用dbms_pumpdata执行expdp操作

使用dbms_pumpdata执行expdp操作

set serverout on
declare
  h1 number; -- Datapump handle
  dir_name varchar2(30); -- Directory Name
  job_status VARCHAR2(30);
begin
  dir_name := 'DATA_PUMP_DIR';
  h1 := dbms_datapump.open(
  operation =>'EXPORT', --是export还是impport
--导出表配置
  job_mode =>'TABLE',  --job_mode可以为SCHEMA/TABLE等
--导出用户配置
  job_mode =>'SCHEMA',
  remote_link => NULL, --是否使用dblink导出(就是NETWORK_LINK）
  job_name =>'TABLE_XFF' --job_name expdpjob的名称
  );
  dbms_datapump.add_file(handle =>h1,
                         filename => 'XIFENFEI.DMP',
                         directory => dir_name,
                         filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE,
                         reusefile => 1); -- value of 1 instructs to overwrite existing file
  dbms_datapump.add_file(handle =>h1,
                         filename => 'XIFENFEI.LOG',
                         directory => dir_name,
                         filetype => DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE,
                         reusefile => 1);
--导出表配置
  dbms_datapump.metadata_filter(handle =>h1,
                         name => 'TABLE_FILTER',
                         value => 'CHF.T_XIFENFEI');
--导出用户配置
  dbms_datapump.metadata_filter (handle => dp_handle,
                                 name => 'SCHEMA_EXPR',
                                 value => 'IN (''CHF'')');
-- Start the job.
  dbms_datapump.start_job(h1);
  dbms_datapump.wait_for_job (handle => dp_handle,
                              job_state => job_status);
  dbms_output.put_line ('DataPump Export - '||to_char(sysdate,'DD/MM/YYYY HH24:MI:SS')||' Status '||job_status);
  begin
     dbms_datapump.detach(handle => h1);
   end;
end;
/

何种情况下imp的fromuser/touser改变tablespace失效

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：何种情况下imp的fromuser/touser改变tablespace失效

exp/imp是大家在数据库迁移中最常见的工具,但是该工具对于表空间的转换不是很智能(最少没有datapump方便),使得很多人在导入数据的时候,吃够了表空间不存在的苦.这里有个细节:fromuser和touser在哪些情况下会失效.这里通过试验,简单证明了对于常见的lob对象和分区表对象的时候fromuser和touser修改表空间会失效.
exp/imp支持表空间变化

--创建测试用户
SQL> create user chf identified by xifenfei;
User created.
SQL> grant dba to chf;
Grant succeeded.
SQL> conn chf/xifenfei
Connected.
--创建测试对象
SQL> create table t_xifenfei01 tablespace users
  2  as
  3  select * from dba_objects;
Table created.
SQL> create index in_t_xifenfei01 on t_xifenfei01(object_id) tablespace xifenfei;
Index created.
SQL> create table t_xifenfei02 tablespace xifenfei
  2  as
  3  select * from dba_objects;
Table created.
SQL> create index in_t_xifenfei02 on t_xifenfei02(object_id) tablespace users;
Index created.
--查询测试对象分布表空间情况
SQL> select OWNER,table_name,TABLESPACE_NAME from dba_tables where table_name like 'T_XIFENFEI%';
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
CHF                            T_XIFENFEI01                   USERS
CHF                            T_XIFENFEI02                   XIFENFEI
SQL> SELECT OWNER,INDEX_NAME,TABLESPACE_NAME FROM DBA_INDEXES WHERE INDEX_NAME LIKE 'IN_T_XIFENFEI%';
OWNER                          INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
CHF                            IN_T_XIFENFEI01                XIFENFEI
CHF                            IN_T_XIFENFEI02                USERS
--导出测试对象
[oracle@xifenfei ~]$ exp chf/xifenfei tables=t_xifenfei01,t_xifenfei02 file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log
Export: Release 10.2.0.4.0 - Production on Thu Dec 15 07:33:27 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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                   T_XIFENFEI01      50053 rows exported
. . exporting table                   T_XIFENFEI02      50055 rows exported
Export terminated successfully without warnings.
--为了试验证实,离线该表涉及表空间
SQL> alter tablespace xifenfei read only;
Tablespace altered.
SQL> alter tablespace users read only;
Tablespace altered.
--创建新用户
SQL> create user chf1 identified by xifenfei;
User created.
SQL> grant dba to chf1;
Grant succeeded.
--创建新表空间
SQL> create tablespace xifenfei1 datafile '/u01/oracle/oradata/XFF/xifenfei02.dbf' size 10m autoextend on
  2  next 10m maxsize 10g;
Tablespace created.
SQL> alter user chf1 default tablespace xifenfei1;
User altered.
--两个测试用户分别默认表空间
SQL> select username,default_tablespace from dba_users where username like 'CHF%';
USERNAME                       DEFAULT_TABLESPACE
------------------------------ ------------------------------
CHF                            USERS
CHF1                           XIFENFEI1
--导入测试数据
[oracle@xifenfei ~]$ imp chf1/xifenfei fromuser=chf touser=chf1 file=/tmp/xifenfei.dmp log=/tmp/xifenfei.log
Import: Release 10.2.0.4.0 - Production on Thu Dec 15 07:37:54 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by CHF, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF's objects into CHF1
. . importing table                 "T_XIFENFEI01"      50053 rows imported
. . importing table                 "T_XIFENFEI02"      50055 rows imported
Import terminated successfully without warnings.
--查询导入结果
SQL> SELECT OWNER,INDEX_NAME,TABLESPACE_NAME FROM DBA_INDEXES WHERE INDEX_NAME LIKE 'IN_T_XIFENFEI%'
  2  and owner='CHF1';
OWNER                          INDEX_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
CHF1                           IN_T_XIFENFEI01                XIFENFEI1
CHF1                           IN_T_XIFENFEI02                XIFENFEI1
SQL> select OWNER,table_name,TABLESPACE_NAME from dba_tables where table_name like 'T_XIFENFEI%'
  2  AND OWNER='CHF1';
OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
CHF1                           T_XIFENFEI01                   XIFENFEI1
CHF1                           T_XIFENFEI02                   XIFENFEI1

通过这里的试验证明:对于无lob对象的普通表和普通index使用fromuser和touser可以实现表空间完美变化

含LOB对象测试

--read write相关表空间
SQL> alter tablespace users read write;
Tablespace altered.
SQL> alter tablespace xifenfei read write;
Tablespace altered.
SQL> create tablespace xifenfei2 datafile '/u01/oracle/oradata/XFF/xifenfei03.dbf' size 10m;
Tablespace created.
SQL> conn chf/xifenfei
Connected.
--创建表,lob分别属于不同空间(数据导入到另外表空间)
SQL> create table t_lob
  2  (id number,clob1 clob,blob1 blob) tablespace users
  3   LOB ("CLOB1") STORE AS (   TABLESPACE xifenfei)
  4   LOB ("BLOB1") STORE AS (   TABLESPACE xifenfei1 );
Table created.
SQL> select table_name,COLUMN_NAME,TABLESPACE_NAME from user_lobs;
TABLE_NAME                     COLUMN_NAME     TABLESPACE_NAME
------------------------------ --------------- ------------------------------
T_LOB                          CLOB1           XIFENFEI
T_LOB                          BLOB1           XIFENFEI1
SQL> select tablespace_name from user_tables where table_name='T_LOB';
TABLESPACE_NAME
------------------------------
USERS
--创建表和lob属于一个表空间(数据导入到另外表空间)
SQL>  create table t_lob_n
  2  (id number,clob1 clob) tablespace users;
Table created.
SQL> select segment_name,segment_type,tablespace_name  from user_segments where SEGMENT_NAME not like '%XIFENFEI%';
SEGMENT_NAME                                                                      SEGMENT_TYPE       TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ----------------
SYS_IL0000051858C00002$$                                                          LOBINDEX           USERS
SYS_LOB0000051858C00002$$                                                         LOBSEGMENT         USERS
T_LOB_N                                                                           TABLE              USERS
T_LOB                                                                             TABLE              USERS
SYS_IL0000051851C00002$$                                                          LOBINDEX           XIFENFEI
SYS_LOB0000051851C00002$$                                                         LOBSEGMENT         XIFENFEI
SYS_IL0000051851C00003$$                                                          LOBINDEX           XIFENFEI1
SYS_LOB0000051851C00003$$                                                         LOBSEGMENT         XIFENFEI1
--表和lob不同表空间(数据导入到lob对应表空间)
SQL>  create table t_lob2
  2  (id number,clob1 clob) tablespace users
  3  LOB ("CLOB1") STORE AS (   TABLESPACE xifenfei2);
Table created.
SQL> select table_name,COLUMN_NAME,TABLESPACE_NAME from user_lobs;
TABLE_NAME                     COLUMN_NAME     TABLESPACE_NAME
------------------------------ --------------- ------------------------------
T_LOB_N                        CLOB1           USERS
T_LOB                          CLOB1           XIFENFEI
T_LOB                          BLOB1           XIFENFEI1
T_LOB2                         CLOB1           XIFENFEI2
SQL>  select segment_name,segment_type,tablespace_name  from user_segments where SEGMENT_NAME not like '%XIFENFEI%';
SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME
------------------------------ ------------------ ------------------------------
T_LOB2                         TABLE              USERS
SYS_IL0000051858C00002$$       LOBINDEX           USERS
SYS_LOB0000051858C00002$$      LOBSEGMENT         USERS
T_LOB_N                        TABLE              USERS
T_LOB                          TABLE              USERS
SYS_IL0000051851C00002$$       LOBINDEX           XIFENFEI
SYS_LOB0000051851C00002$$      LOBSEGMENT         XIFENFEI
SYS_IL0000051851C00003$$       LOBINDEX           XIFENFEI1
SYS_LOB0000051851C00003$$      LOBSEGMENT         XIFENFEI1
SYS_IL0000051863C00002$$       LOBINDEX           XIFENFEI2
SYS_LOB0000051863C00002$$      LOBSEGMENT         XIFENFEI2
11 rows selected.
SQL> select table_name,COLUMN_NAME,TABLESPACE_NAME,SEGMENT_NAME from user_lobs;
TABLE_NAME                     COLUMN_NAME     TABLESPACE_NAME                SEGMENT_NAME
------------------------------ --------------- ------------------------------ ------------------------------
T_LOB                          BLOB1           XIFENFEI1                      SYS_LOB0000051851C00003$$
T_LOB                          CLOB1           XIFENFEI                       SYS_LOB0000051851C00002$$
T_LOB_N                        CLOB1           USERS                          SYS_LOB0000051858C00002$$
T_LOB2                         CLOB1           XIFENFEI2                      SYS_LOB0000051863C00002$$
--得到在默认情况下LOBINDEX和LOBSEGMENT在同一个表空间
--导出三种情况下lob表
[oracle@xifenfei ~]$ exp chf/xifenfei tables=t_lob_n file=/tmp/lob1.dmp log=/tmp/xifenfei.log indexes=y
Export: Release 10.2.0.4.0 - Production on Thu Dec 15 08:57:38 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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                        T_LOB_N          0 rows exported
Export terminated successfully without warnings.
[oracle@xifenfei ~]$ exp chf/xifenfei tables=t_lob file=/tmp/lob.dmp log=/tmp/xifenfei.log indexes=y
Export: Release 10.2.0.4.0 - Production on Thu Dec 15 08:31:25 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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                          T_LOB          0 rows exported
Export terminated successfully without warnings.
[oracle@xifenfei ~]$ exp chf/xifenfei tables=t_lob2 file=/tmp/lob2.dmp log=/tmp/xifenfei.log
Export: Release 10.2.0.4.0 - Production on Thu Dec 15 16:23:18 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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                         T_LOB2          0 rows exported
Export terminated successfully without warnings.
--修改default tablespace 和 read only相关表空间
SQL> alter user chf1 default tablespace xifenfei2;
User altered.
SQL> alter tablespace users read only;
Tablespace altered.
SQL> alter tablespace xifenfei read only;
Tablespace altered.
SQL> alter tablespace xifenfei1 read only;
Tablespace altered.
--导入lob表
[oracle@xifenfei ~]$ imp chf1/xifenfei tables=t_lob_n file=/tmp/lob1.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1
Import: Release 10.2.0.4.0 - Production on Thu Dec 15 08:58:12 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by CHF, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF's objects into CHF1
IMP-00017: following statement failed with ORACLE error 1647:
 "CREATE TABLE "T_LOB_N" ("ID" NUMBER, "CLOB1" CLOB)  PCTFREE 10 PCTUSED 40 I"
 "NITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 "
 "BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("CLOB1") ST"
 "ORE AS  (TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCA"
 "CHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POO"
 "L DEFAULT))"
IMP-00003: ORACLE error 1647 encountered
ORA-01647: tablespace 'USERS' is read only, cannot allocate space in it
Import terminated successfully with warnings.
--使用fromuser和touser并未修改table segment初始化参数
[oracle@xifenfei ~]$ imp chf1/xifenfei tables=t_lob file=/tmp/lob.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1
Import: Release 10.2.0.4.0 - Production on Thu Dec 15 08:35:05 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by CHF, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF's objects into CHF1
IMP-00017: following statement failed with ORACLE error 1647:
 "CREATE TABLE "T_LOB" ("ID" NUMBER, "CLOB1" CLOB, "BLOB1" BLOB)  PCTFREE 10 "
 "PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELI"
 "ST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB "
 "("CLOB1") STORE AS  (TABLESPACE "XIFENFEI" ENABLE STORAGE IN ROW CHUNK 8192"
 " RETENTION NOCACHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROU"
 "PS 1 BUFFER_POOL DEFAULT)) LOB ("BLOB1") STORE AS  (TABLESPACE "XIFENFEI1" "
 "ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NOCACHE LOGGING  STORAGE(INITIAL"
 " 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))"
IMP-00003: ORACLE error 1647 encountered
ORA-01647: tablespace 'USERS' is read only, cannot allocate space in it
Import terminated successfully with warnings.
--结论同上
[oracle@xifenfei ~]$ imp chf1/xifenfei tables=t_lob2 file=/tmp/lob2.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1
Import: Release 10.2.0.4.0 - Production on Thu Dec 15 16:24:03 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by CHF, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF's objects into CHF1
IMP-00017: following statement failed with ORACLE error 1647:
 "CREATE TABLE "T_LOB2" ("ID" NUMBER, "CLOB1" CLOB)  PCTFREE 10 PCTUSED 40 IN"
 "ITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 B"
 "UFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS LOB ("CLOB1") STO"
 "RE AS  (TABLESPACE "XIFENFEI2" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION N"
 "OCACHE LOGGING  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_"
 "POOL DEFAULT))"
IMP-00003: ORACLE error 1647 encountered
ORA-01647: tablespace 'USERS' is read only, cannot allocate space in it
Import terminated successfully with warnings.
--结论也同上

通过三种不同情况的table segment 和lob segment的分别表空间和导入表空间测试情况,可以判断出来在使用exp/imp迁移数据时候,如果遇到含lob字段表,不能通过fromuser和touser来实现修改,就算lob的表空间存在,或者lob和table segment是同一个表空间,而table segment的表空间不存在,依然会报错,导入不成功.

分区表测试

--read write 相关表空间
SQL> alter tablespace users read write;
Tablespace altered.
SQL> alter tablespace xifenfei read write;
Tablespace altered.
SQL> alter tablespace xifenfei1 read write;
Tablespace altered.
--创建分区表
SQL> conn chf/xifenfei
Connected.
SQL> create table tab_par
  2  (
  3    F_KJND VARCHAR2(4) default ' ' not null,
  4    F_CODE VARCHAR2(30) default ' ' not null,
  5    F_KMBH VARCHAR2(30) default ' ' not null,
  6    F_BKBH VARCHAR2(30) default ' ' not null,
  7    UNIT_ID VARCHAR2(30)
  8  )
  9  partition by range (F_KJND)
 10  (partition TABL_NAME_PT_2009 values less than ('2010')tablespace users,
 11  partition TABL_NAME_PT_2010 values less than ('2011')tablespace xifenfei,
 12  partition TABL_NAME_PT_MAX values less than (MAXVALUE)  tablespace xifenfei1
 13  );
Table created.
--查询分区分布
SQL> select PARTITION_NAME,TABLESPACE_NAME from ALL_TAB_PARTITIONS where TABLE_NAME='TAB_PAR';
PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
TABL_NAME_PT_2009              USERS
TABL_NAME_PT_2010              XIFENFEI
TABL_NAME_PT_MAX               XIFENFEI1
--导出分区表
[oracle@xifenfei ~]$ exp chf/xifenfei tables=tab_par file=/tmp/tab_par.dmp log=/tmp/xifenfei.log
Export: Release 10.2.0.4.0 - Production on Thu Dec 15 18:33:19 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 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                        TAB_PAR
. . exporting partition              TABL_NAME_PT_2009          0 rows exported
. . exporting partition              TABL_NAME_PT_2010          0 rows exported
. . exporting partition               TABL_NAME_PT_MAX          0 rows exported
Export terminated successfully without warnings.
--导入分区表
[oracle@xifenfei ~]$ imp chf1/xifenfei tables=tab_par file=/tmp/tab_par.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1
Import: Release 10.2.0.4.0 - Production on Thu Dec 15 18:33:52 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by CHF, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF's objects into CHF1
. . importing partition  "TAB_PAR":"TABL_NAME_PT_2009"          0 rows imported
. . importing partition  "TAB_PAR":"TABL_NAME_PT_2010"          0 rows imported
. . importing partition   "TAB_PAR":"TABL_NAME_PT_MAX"          0 rows imported
Import terminated successfully without warnings.
--导入成功
--查看导入进入表空间
SQL> select PARTITION_NAME,TABLESPACE_NAME from ALL_TAB_PARTITIONS where TABLE_NAME='TAB_PAR' and TABLE_OWNER='CHF1';
PARTITION_NAME                 TABLESPACE_NAME
------------------------------ ------------------------------
TABL_NAME_PT_2009              USERS
TABL_NAME_PT_2010              XIFENFEI
TABL_NAME_PT_MAX               XIFENFEI1
--发现还是进入和以前相同的表空间,fromuser和touser未生效
SQL> DROP TABLE CHF1.TAB_PAR PURGE;
Table dropped.
--read only相关表空间测试
SQL> ALTER TABLESPACE USERS READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE XIFENFEI READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE XIFENFEI1 READ ONLY;
Tablespace altered.
--再次导入
[oracle@xifenfei ~]$ imp chf1/xifenfei tables=tab_par file=/tmp/tab_par.dmp log=/tmp/xifenfei.log fromuser=chf touser=chf1
Import: Release 10.2.0.4.0 - Production on Thu Dec 15 18:36:38 2011
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
Warning: the objects were exported by CHF, not by you
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF's objects into CHF1
IMP-00017: following statement failed with ORACLE error 1647:
 "CREATE TABLE "TAB_PAR" ("F_KJND" VARCHAR2(4) NOT NULL ENABLE, "F_CODE" VARC"
 "HAR2(30) NOT NULL ENABLE, "F_KMBH" VARCHAR2(30) NOT NULL ENABLE, "F_BKBH" V"
 "ARCHAR2(30) NOT NULL ENABLE, "UNIT_ID" VARCHAR2(30))  PCTFREE 10 PCTUSED 40"
 " INITRANS 1 MAXTRANS 255 TABLESPACE "USERS" LOGGING PARTITION BY RANGE ("F_"
 "KJND" )  (PARTITION "TABL_NAME_PT_2009" VALUES LESS THAN ('2010')  PCTFREE "
 "10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FRE"
 "ELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" LOGGING NOCOMPRESS, "
 "PARTITION "TABL_NAME_PT_2010" VALUES LESS THAN ('2011')  PCTFREE 10 PCTUSED"
 " 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROU"
 "PS 1 BUFFER_POOL DEFAULT) TABLESPACE "XIFENFEI" LOGGING NOCOMPRESS, PARTITI"
 "ON "TABL_NAME_PT_MAX" VALUES LESS THAN (MAXVALUE)  PCTFREE 10 PCTUSED 40 IN"
 "ITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 B"
 "UFFER_POOL DEFAULT) TABLESPACE "XIFENFEI1" LOGGING NOCOMPRESS )"
IMP-00003: ORACLE error 1647 encountered
ORA-01647: tablespace 'USERS' is read only, cannot allocate space in it
Import terminated successfully with warnings.
--进步一证明分区表在导入的时候fromuser和touser未能改变其对应表空间

通过对分区表的测试,证明exp/imp在操作分区表的时候fromuser和touser也不能实现表空间的转换

在使用imp和exp实现数据迁移的时候,遇到我们常见的lob和分区表时候fromuser和touser修改表空间会失效,数据还是会导入到原对象锁对应的表空间,所以在处理含这些对象的数据迁移时,一般方法有:1.创建好这些对象所属表空间;2.先导出来这些对象对应的创建脚本,创建好这些对象,然后使用IGNORE=Y导入

ORA-39126: 在 KUPW$WORKER.PUT_DDLS [TABLE_STATISTICS] 中 Worker 发生意外致命错误

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORA-39126: 在 KUPW$WORKER.PUT_DDLS [TABLE_STATISTICS] 中 Worker 发生意外致命错误

使用impdp导入数据报如下错误导致导入终止

处理对象类型 SCHEMA_EXPORT/TABLE/TRIGGER
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
处理对象类型 SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
处理对象类型 SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39126: 在 KUPW$WORKER.PUT_DDLS [TABLE_STATISTICS] 中 Worker 发生意外致命错误
ORA-06502: PL/SQL: 数字或值错误
LPX-00225: end-element tag "HIST_GRAM_LIST_ITEM" does not match start-element tag "EPVALUE"
ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: 在 "SYS.KUPW$WORKER", line 9001
----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
26ABF4B0     20462  package body SYS.KUPW$WORKER
26ABF4B0      9028  package body SYS.KUPW$WORKER
26ABF4B0     16665  package body SYS.KUPW$WORKER
26ABF4B0      3956  package body SYS.KUPW$WORKER
26ABF4B0      9725  package body SYS.KUPW$WORKER
26ABF4B0      1775  package body SYS.KUPW$WORKER
290D454C         2  anonymous block
ORA-39097: 数据泵作业出现意外的错误 -1427
ORA-39065: DISPATCH 中出现意外的主进程异常错误
ORA-01427: 单行子查询返回多个行
作业 "EAS"."SYS_IMPORT_SCHEMA_01" 因致命错误于 15:21:20 停止

从这里可以看出是在执行TABLE_STATISTICS的时候因为EPVALUE列的数据类型和导入数据不匹配,问题发生上面错误,导致impdp job终止.

解决办法
参考文档:[ID 878626.1]
1.如果数据已经expdp导出,建议在导入的时候屏蔽掉统计信息导入EXCLUDE=STATISTICS,导入后使用DBMS_STATS 重新收集统计信息
2.如果数据尚未expdp导出,建议在导出的时候屏蔽掉统计信息导出EXCLUDE=STATISTICS导入后使用DBMS_STATS 重新收集统计信息

使用exp/imp 导入11g数据到9i

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：使用exp/imp 导入11g数据到9i

现在有个需求,需要使用exp/imp导入11g的数据库数据到9i中,解决这个问题一般来说想到三种方法思路,一个个尝试(其实从高版本服务端支持低版本客户端的原则,可以大概的猜测出使用9i的客户端处理该问题)
方法1:导出导入都使用11g客户端

--11g客户端导出
[oracle@xifenfei ~]$ exp chf/xifenfei file=/tmp/t_xifenfei.dmp
>log=/tmp/t_xifenfei.log tables=chf.t_xifenfei
Export: Release 11.2.0.3.0 - Production on Fri May 18 18:15:18 2012
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                     T_XIFENFEI          2 rows exported
Export terminated successfully without warnings.
--11g客户端导入
[oracle@xifenfei ~]$ imp chf/xifenfei@ora9i file=/tmp/t_xifenfei_11g.dmp
>log=/tmp/t_xifenfei.log tables=chf.t_xifenfei
Import: Release 11.2.0.3.0 - Production on Fri May 18 18:17:24 2012
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
IMP-00058: ORACLE error 6550 encountered
ORA-06550: line 1, column 33:
PLS-00302: component 'SET_NO_OUTLINES' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
IMP-00000: Import terminated unsuccessfully

这个错误是版本不兼容导致:PLS-00302: component ‘SET_NO_OUTLINES’ must be declared

方法2:11g客户端导出,9i客户端导入

--11g客户端导出
[oracle@xifenfei ~]$ exp chf/xifenfei file=/tmp/t_xifenfei.dmp
>log=/tmp/t_xifenfei.log tables=chf.t_xifenfei
Export: Release 11.2.0.3.0 - Production on Fri May 18 18:15:18 2012
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                     T_XIFENFEI          2 rows exported
Export terminated successfully without warnings.
--传输到9i
[oracle@xifenfei tmp]$ scp t_xifenfei.dmp 192.168.1.10:/tmp/
The authenticity of host '192.168.1.10 (192.168.1.10)' can't be established.
RSA key fingerprint is 3d:0c:d1:4b:45:bd:a3:f5:25:eb:4d:52:d2:32:03:69.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.1.10' (RSA) to the list of known hosts.
oracle@192.168.1.10's password:
t_xifenfei.dmp                          100%   56KB  56.0KB/s   00:00
--9i客户端导入
[oracle@xifenfei ~]$ imp chf/xifenfei file=/tmp/t_xifenfei.dmp tables=t_xifenfei
Import: Release 9.2.0.4.0 - Production on Thu May 24 23:32:18 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
IMP-00010: not a valid export file, header failed verification
IMP-00000: Import terminated unsuccessfully
--版本不兼容(高版本的dump文件低版本不能识别)

方法3:9i客户端导出,9i客户端导入

--9i客户端导出
[oracle@xifenfei ~]$ exp chf/xifenfei@ora11g file=/tmp/t_xifenfei_11g.dmp
>log=/tmp/t_xifenfei.log tables=chf.t_xifenfei
Export: Release 9.2.0.4.0 - Production on Thu May 24 23:37:20 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                     T_XIFENFEI          2 rows exported
Export terminated successfully without warnings.
--9i客户端导入
[oracle@xifenfei log]$ imp chf/xifenfei file=/tmp/t_xifenfei_11g.dmp log=/tmp/xifenfei.log full=y
Import: Release 9.2.0.4.0 - Production on Fri May 25 03:22:14 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character setSegmentation fault
--导入数据遇到setSegmentation fault异常终止

解决setSegmentation fault异常终止

--修改exu9defpswitches视图
[oracle@xifenfei ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Fri May 18 22:29:00 2012
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE OR REPLACE VIEW exu9defpswitches (
  2                  compflgs, nlslensem ) AS
  3          SELECT  a.value, b.value
  4          FROM    sys.v$parameter a, sys.v$parameter b
  5          WHERE   a.name = 'plsql_code_type' AND
  6                  b.name = 'nls_length_semantics' ;
View created.
--9i导出11g数据
[oracle@xifenfei tmp]$ exp chf/xifenfei@ora11g file=/tmp/t_xifenfei_11g.dmp
>log=/tmp/xifenfei.log tables=t_xifenfei
Export: Release 9.2.0.4.0 - Production on Fri May 25 04:08:32 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table                     T_XIFENFEI          2 rows exported
Export terminated successfully without warnings.
--9i导入数据
[oracle@xifenfei tmp]$ imp chf/xifenfei file=/tmp/t_xifenfei_11g.dmp
>log=/tmp/xifenfei.log tables=t_xifenfei
Import: Release 9.2.0.4.0 - Production on Fri May 25 04:08:53 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing CHF's objects into CHF
. . importing table                   "T_XIFENFEI"          2 rows imported
Import terminated successfully without warnings.
--至此导入成功,完成了11gr2数据导入到9ir2中

通过一系列的实验证明,需要把11g的数据导入到9i中,需要使用9i的客户端进行,其中exu9defpswitches视图需要重建,否则会出现setSegmentation fault异常,导致导入失败.

exp导出数据报EXP-00056/ORA-01403错误

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：exp导出数据报EXP-00056/ORA-01403错误

exp导出数据报EXP-00056/ORA-01403错误

[oracle@xifenfei ~]$ exp chf/xifenfei file=/tmp/chf.dmp log=/tmp/chf.log owner=chf
Export: Release 9.2.0.4.0 - Production on Sun Apr 29 03:11:31 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user CHF
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user CHF
About to export CHF's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 1403 encountered
ORA-01403: no data found
EXP-00000: Export terminated unsuccessfully

查看组件信息

SQL> select * from v$version;
BANNER
-------------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
PL/SQL Release 9.2.0.4.0 - Production
CORE    9.2.0.3.0       Production
TNS for Linux: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
SQL> col comp_id for a15
SQL> col status for a7
SQL> col version for a10
SQL> col comp_name for a30
SQL> set pagesize 1000
SQL> SELECT substr(comp_id,1,15) comp_id, status, substr(version,1,10) version,
  2     substr(comp_name,1,30) comp_name FROM dba_registry ORDER BY 1;
COMP_ID         STATUS  VERSION    COMP_NAME
--------------- ------- ---------- ------------------------------
AMD             VALID   9.2.0.4.0  OLAP Catalog
APS             LOADED  9.2.0.4.0  OLAP Analytic Workspace
CATALOG         VALID   9.2.0.4.0  Oracle9i Catalog Views
CATJAVA         VALID   9.2.0.4.0  Oracle9i Java Packages
CATPROC         VALID   9.2.0.4.0  Oracle9i Packages and Types
CONTEXT         VALID   9.2.0.4.0  Oracle Text
JAVAVM          VALID   9.2.0.4.0  JServer JAVA Virtual Machine
ODM             LOADED  9.2.0.1.0  Oracle Data Mining
ORDIM           VALID   9.2.0.4.0  Oracle interMedia
OWM             VALID   9.2.0.1.0  Oracle Workspace Manager
SDO             LOADED  9.2.0.4.0  Spatial
WK              VALID   9.2.0.4.0  Oracle Ultra Search
XDB             VALID   9.2.0.4.0  Oracle XML Database
XML             VALID   9.2.0.6.0  Oracle XDK for Java
XOQ             LOADED  9.2.0.4.0  Oracle OLAP API
15 rows selected.
SQL> SELECT status, object_id, object_type, owner||'.'||object_name
  2     "OWNER.OBJECT" FROM dba_objects WHERE owner='XDB' AND status != 'VALID'
  3     ORDER BY 4,2;
no rows selected

做1403跟踪

SQL> ALTER SYSTEM SET EVENTS '1403 trace name errorstack level 3';
[oracle@xifenfei ~]$ exp chf/xifenfei file=/tmp/chf.dmp log=/tmp/chf.log owner=chf
SQL> ALTER SYSTEM SET EVENTS '1403 trace name errorstack off';

trace文件关键内容

*** SESSION ID:(11.17) 2012-04-29 03:17:13.555
*** 2012-04-29 03:17:13.555
ksedmp: internal or fatal error
ORA-01403: no data found
Current SQL statement for this session:
SELECT xdb_uid FROM SYS.EXU9XDBUID

问题原因
因为控制文件重建或者使用历史控制文件恢复,忘记添加临时文件

SQL> select name from v$tempfile;
no rows selected

解决方法
添加临时文件

SQL> alter tablespace TEMP add tempfile
  2 '/u01/oracle/oradata/xifenfei/temp01.dbf' size 10M reuse;
Tablespace altered.
SQL> select name from v$tempfile;
NAME
------------------------------------------------
/u01/oracle/oradata/xifenfei/temp01.dbf

验证exp导出

[oracle@xifenfei udump]$ exp chf/xifenfei file=/tmp/chf.dmp log=/tmp/chf.log owner=chf
Export: Release 9.2.0.4.0 - Production on Sun Apr 29 05:20:21 2012
Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.
Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
server uses WE8ISO8859P1 character set (possible charset conversion)
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user CHF
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user CHF
About to export CHF's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export CHF's tables via Conventional Path ...
. . exporting table ACC_OWE_TASK_LIST_HIS_07711202       4111 rows exported
. . exporting table                   CHF_XIFENFEI        868 rows exported
. . exporting table                     PLAN_TABLE          0 rows exported
. . exporting table                        T_XFF01          0 rows exported
. . exporting table                     T_XIFENFEI          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully no warnings.

impdp报ORA-00904: "ORIGINAL_OBJECT_NAME": invalid identifier

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：impdp报ORA-00904: "ORIGINAL_OBJECT_NAME": invalid identifier

发现问题
impdp导入数据库不成功,一直在报ORA-00904: “ORIGINAL_OBJECT_NAME”: invalid identifier错误

[oracle@back1 backup]$ impdp username/password  schemas=center_admin dumpfile=center_admin20120427.dmp
> logfile=center_admin20120427.log directory=impdir parallel=10 job_name=center_admin08;
Import: Release 11.1.0.6.0 - 64bit Production on Friday, 27 April, 2012 21:35:06
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORA-39002: invalid operation
ORA-31694: master table "USERNAME"."CENTER_ADMIN08" failed to load/unload
ORA-02354: error in exporting/importing data
ORA-02373: Error parsing insert statement for table "USERNAME"."CENTER_ADMIN08".
ORA-00904: "ORIGINAL_OBJECT_NAME": invalid identifier

分析问题
看到这个错误,我第一个感觉根据ORA-31694,怀疑是没有创建相关用户,或者是该用户无权限权限CENTER_ADMIN08表.等我登陆目标数据库查看时候发现该用户存在,并且已经授予了DBA权限,所以不存在是用户相关问题导致.ORA-02354错误我怀疑是expdp导出来的文件在传输过程中发生意外(如使用ftp传输未使用二进制模式),当我使用md5sum命令检查发现两边一致,证明该文件传输正常.目标端不能检查明显故障,怀疑导出文件本身存在问题检查导出文件日志

[oracle@fcdb2 backup]$ more center_admin20120427.log
;;;
Export: Release 11.1.0.7.0 - 64bit Production on Friday, 27 April, 2012 17:32:30
Copyright (c) 2003, 2007, Oracle.  All rights reserved.
;;;
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
Starting "USERNAME"."CENTER_ADMIN08":USERNAME/**** schemas=center_admin directory=expdir dumpfile=center_admin20120427.dmp
er_admin20120427.log parallel=10 job_name=center_admin08
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 158.2 GB

发现新大陆,两边的数据库版本不一致,源端版本是11.1.0.7,目标端版本是11.1.0.6.这个时候我对问题的定位是可能版本兼用问题(毕竟是高版本到低版本)或者是bug.先查询datapump版本之间兼容性列表

     Version      Written by     Can be imported into Target:
   Data Pump   database with      10gR1      10gR2      11gR1      11gR2
Dumpfile Set   compatibility   10.1.0.x   10.2.0.x   11.1.0.x   11.2.0.x
------------ --------------- ---------- ---------- ---------- ----------
         0.1          10.1.x  supported  supported  supported  supported
         1.1          10.2.x         no  supported  supported  supported
         2.1          11.1.x         no         no  supported  supported
         3.1          11.2.x         no         no         no  supported

这里可以看出11.1.0.7和11.1.0.6之间是相互兼容的,不应该会存在上述问题,那么现在对于该问题的解释很可能是bug导致,继续查询资料发现[ID 752374.1]描述的正是该问题.

解决问题

1. Apply 11.1.0.7 Patch:6890831 on the target database.
2. Workaround this issue by re-running an expdp from the 11.1.0.7 database with an additional
parameter VERSION=10.2. This will create a new dump file compatible to be imported into 10gR2,
which will also import successfully into 11.1.0.6.
Unfortunately, please be aware that the new 11g specific features will not be
exported if expdp is run from 11.1.0.7 with the parameter VERSION=10.2.
If only normal objects and features are involved, this could be a good workaround.

分类目录归档：逻辑备份/恢复