导入一个有lob类型字段的表且存在与导入表同名的同义词会报ora-01403错误

如果你一表包含一个lob类型的列如果存在一个与这个表同名的同义词那么在导入这个表的数据时会报ora-01403错:
在oracle10g中的测试情况如下:
1.创建两个用户test和cs

idle> conn sys/zzh_2046@jy_1 as sysdba
已连接。
sys@JINGYONG> grant dba to test identified by test;

授权成功。

sys@JINGYONG> grant dba to cs identified by cs;

授权成功。

2.在用户test中创建一个有clob类型的列的表emp并向表中插入几行记录

sys@JINGYONG> conn test/test@jy_1
已连接。
test@JINGYONG> create table emp (col_a number, col_b varchar2(3), c_lob clob);

表已创建。

test@JINGYONG>
test@JINGYONG> INSERT INTO EMP VALUES (180, 'a', '01010101010101');

已创建 1 行。

test@JINGYONG> INSERT INTO EMP VALUES (181, 'a', '01010101010101');

已创建 1 行。

test@JINGYONG> INSERT INTO EMP VALUES (182, 'a', '01010101010101');

已创建 1 行。

test@JINGYONG> INSERT INTO EMP VALUES (183, 'a', '01010101010101');

已创建 1 行。

test@JINGYONG> INSERT INTO EMP VALUES (184, 'a', '01010101010101');

已创建 1 行。

test@JINGYONG> INSERT INTO EMP VALUES (185, 'a', '01010101010101');

已创建 1 行。

test@JINGYONG> commit;

提交完成。

连接到用户cs并创建一个同义词叫EMP
1.创建同义词EMP是使用test.emp表来创建(也可以用任意表来创建同义词EMP,只要保证同义词的名字与要导入表的名字相同就行)

test@JINGYONG> conn cs/cs@jy_1
已连接。
cs@JINGYONG> create synonym EMP for test.EMP;

同义词已创建。

cs@JINGYONG> commit;

提交完成。

cs@JINGYONG>



C:\Documents and Settings\Administrator>exp test/test@jy_1 file=f:\emp.dmp table
s=EMP

Export: Release 10.2.0.1.0 - Production on 星期四 12月 19 22:00:15 2013

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


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...
. . 正在导出表                             EMP导出了           6 行
成功终止导出, 没有出现警告。

C:\Documents and Settings\Administrator>imp cs/cs@jy_1 file=f:\emp.dmp tables=EM
P ignore=y

Import: Release 10.2.0.1.0 - Production on 星期四 12月 19 22:01:48 2013

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


连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

经由常规路径由 EXPORT:V10.02.01 创建的导出文件

警告: 这些对象由 TEST 导出, 而不是当前用户

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 TEST 的对象导入到 CS
. 正在将 TEST 的对象导入到 CS
. . 正在导入表                           "EMP"
IMP-00058: 遇到 ORACLE 错误 1403
ORA-01403: no data found导入了           6 行
成功终止导入, 但出现警告。

2.使用表t1来创建同义词EMP

cs@JINGYONG> create synonym EMP for sys.t1;

同义词已创建。

cs@JINGYONG> drop table emp;
drop table emp
           *
第 1 行出现错误:
ORA-00942: 表或视图不存在

C:\Documents and Settings\Administrator>imp cs/cs@jy_201 file=f:\emp.dmp tables=
EMP ignore=y

Import: Release 10.2.0.1.0 - Production on 星期五 12月 20 08:13:07 2013

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


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

经由常规路径由 EXPORT:V10.02.01 创建的导出文件

警告: 这些对象由 TEST 导出, 而不是当前用户

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 TEST 的对象导入到 CS
. 正在将 TEST 的对象导入到 CS
. . 正在导入表                           "EMP"
IMP-00058: 遇到 ORACLE 错误 1403
ORA-01403: 未找到任何数据
IMP-00058: 遇到 ORACLE 错误 904
ORA-00904: "C_LOB": 标识符无效
成功终止导入, 但出现警告。

11g的测试
1.创建两个用户test和cs

idle> conn sys/zzh_2046@jy_201 as sysdba
已连接。
sys@JINGYONG> grant dba to test identified by test;

授权成功。

sys@JINGYONG> grant dba to cs identified by cs;

2.在用户test中创建一个有clob类型的列的表emp并向表中插入几行记录

sys@JINGYONG> conn test/test@jy_201
已连接。
test@JINGYONG> create table emp (col_a number, col_b varchar2(3), c_lob clob);

表已创建。

test@JINGYONG>
test@JINGYONG> INSERT INTO EMP VALUES (180, 'a', '01010101010101');

已创建 1 行。

test@JINGYONG> INSERT INTO EMP VALUES (181, 'a', '01010101010101');

已创建 1 行。

test@JINGYONG> INSERT INTO EMP VALUES (182, 'a', '01010101010101');

已创建 1 行。

test@JINGYONG> INSERT INTO EMP VALUES (183, 'a', '01010101010101');

已创建 1 行。

test@JINGYONG> INSERT INTO EMP VALUES (184, 'a', '01010101010101');

已创建 1 行。

test@JINGYONG> INSERT INTO EMP VALUES (185, 'a', '01010101010101');

已创建 1 行。

test@JINGYONG> commit;

提交完成。

连接到用户cs并创建一个同义词叫EMP
1.创建同义词EMP是使用test.emp表来创建(也可以用任意表来创建同义词EMP,只要保证同义词的名字与要导入表的名字相同就行)

test@JINGYONG> conn cs/cs@jy_201
已连接。
cs@JINGYONG> create synonym EMP for test.EMP;

同义词已创建。

cs@JINGYONG> commit;

提交完成。

cs@JINGYONG>

使用10g的客户端连接到11g的数据库进行导出操作

C:\Documents and Settings\Administrator>exp test/test@jy_201 file=f:\emp.dmp tab
les=EMP

Export: Release 10.2.0.1.0 - Production on 星期四 12月 19 22:10:28 2013

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


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...
. . 正在导出表                             EMP导出了           6 行
成功终止导出, 没有出现警告。

使用10g的客户端连接到11g的数据库进行导入操作也会报ora-01403错误

C:\Documents and Settings\Administrator>imp cs/cs@jy_201 file=f:\emp.dmp tables=
EMP ignore=y

Import: Release 10.2.0.1.0 - Production on 星期四 12月 19 22:11:27 2013

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


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

经由常规路径由 EXPORT:V10.02.01 创建的导出文件

警告: 这些对象由 TEST 导出, 而不是当前用户

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 TEST 的对象导入到 CS
. 正在将 TEST 的对象导入到 CS
. . 正在导入表                           "EMP"
IMP-00058: 遇到 ORACLE 错误 1403
ORA-01403: 未找到任何数据导入了           6 行
成功终止导入, 但出现警告。

使用11g的客户端连接到11g的数据库进行导出操作

[oracle@jingyong ~]$ exp test/test file=/home/oracle/emp.dmp tables=EMP

Export: Release 11.2.0.1.0 - Production on Sat Dec 7 01:41:44 2013

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.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                            EMP         12 rows exported
Export terminated successfully without warnings.

使用11g的客户端连接到11g的数据库进行导出操作在有与导入表同名的同义词的情况下不会报ora-01403错误.

[oracle@jingyong ~]$ imp cs/cs file=/home/oracle/emp.dmp tables=EMP ignore=y

Import: Release 11.2.0.1.0 - Production on Sat Dec 7 01:42:20 2013

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

Warning: the objects were exported by TEST, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing TEST's objects into CS
. importing TEST's objects into CS
. . importing table                          "EMP"         12 rows imported
Import terminated successfully without warnings.
[oracle@jingyong ~]$

下面删除与表同名的同义词而且使用10g的客户端连接11g数据库进行导入操作也是不会报ora-01403错误的

C:\Documents and Settings\Administrator>sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on 星期五 12月 20 07:51:34 2013

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

idle> conn cs/cs@jy_1
已连接。
cs@JINGYONG> drop synonym EMP;

同义词已删除。

C:\Documents and Settings\Administrator>imp cs/cs@jy_201 file=f:\emp.dmp tables=
EMP ignore=y

Import: Release 10.2.0.1.0 - Production on 星期五 12月 20 09:02:40 2013

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


连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

经由常规路径由 EXPORT:V10.02.01 创建的导出文件

警告: 这些对象由 TEST 导出, 而不是当前用户

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 TEST 的对象导入到 CS
. 正在将 TEST 的对象导入到 CS
. . 正在导入表                           "EMP"导入了           6 行
成功终止导入, 没有出现警告。

出现这个问题的原因:
是因为bug 7422758 IMPORTING A TABLE WITH A BLOB OR CLOB USING A SYNONYM CAUSES ORA-1403, fixed with 11.2.

解决方法:
1. 升级到11.2.
2. 打7422758补钉

注意:
在oracle11gr2版本中如果在对某个用户导入某个表时,而这个表有一个lob类型的列且这个用户存一个与要导入表同名的同义词,在这种情况下使用oracle11gr2版本的客户端进行导出再导入是不会报ora-01403错误.如果使用oracle11gr2版本以下的客户端进行导出和导入也是会报ora-01403错误的.

如何理解systemstate dump

什么是systemstate
一个systemstate是由在实例中调用生成systemstats时由每一个进程的进程状态组成.而每一个进程状态是由每一个进程所持有的当前对象所对应的详细对象状态信息组成.

生成systemstate的例子如下

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug dump systemstate 267
Statement processed.
SQL> oradebug dump systemstate 267
Statement processed.
SQL> oradebug tracefile_name
/oracle/admin/RLZY/udump/rlzy_ora_54657104.trc

如何浏览系统状态信息
首先需要做的就是判断大多数会话正在等待什么(或者在你知道一个会话被阻塞时它的进程号).所以现在要从PROCESS XX或者一个例如’latch free’标示开始浏览.然后就是找到第一个PORCESS XX或者’latch free’标识.如果你正使用PROCESS XX那么你需要找到这个进程正在等待什么

PROCESS XX waits for YYYYYYY
然后你需要做的是找到PROCESS XX会话正在等待什么会话资源.

PROCESS xx waits for YYYYYY
PROCESS YY holds YYYYYY
然后可以开始查找正在等待的资源和资源的持所者.最终你会找到一个最后等待CPU资源的一个进程或者你将会导航到一个你已经了解的进程.对于等待CPU的进程你将需要生成了一个errorstack来判断为什么它正被阻塞.

PROCESS XX waits for YYYYYYY
PROCESS YY holds YYYYYYY and waits for ZZZZZZZZ
PROCESS ZZ holds ZZZZZZZ … etc etc

常见的场景和相关的条目
1:enqueue 队列

PROCESS 141
... 
waiting for 'enq: TX - row lock contention' blocking sess=0x39b3a5c90 seq=152 wait_time=0 seconds since wait
started=796
name|mode=54580006, usn< 54580006 is split into ASCII 54 + ASCII 58 (TX) + Mode 0006 (X) ...

为了找到对于这个队列的更详细信息可以简单地向下搜索'req':

SO: 39ad80d60, type: 5, owner: 393cb85e0, flag: INIT/-/-/0x00
(enqueue) TX-00020009-0001FA04 DID: 0001-0029-00000090
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 flag: 0x6
res: 39aef20c8, req: X, prv: 39aef20e8, own: 39b383aa8, sess: 39b383aa8, proc: 39b7384f0

那么现在已经有了这个队列的名字是一个字符串(TX-00020009-0001FA04)使用它可以用来搜索持有者:

(enqueue) TX-00020009-0001FA04 DID: 0001-002E-00000014
lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 flag: 0x6
res: 39aef20c8, mode: X, prv: 39aef20d8, own: 39b3a5c90, sess: 39b3a5c90, proc: 39b73ac78

可以看到持有的队列(mode:X)以一种不兼容的模式来执行X request(排他请求)

2:Rowcache locks 行缓存锁

PROCESS 219:
... 
waiting for 'row cache lock' blocking sess=0x0 seq=2174 wait_time=0
cache id=7, mode=0, request=3 *
We do not hold it currently (mode=0), but want it in Shared (mode=3) ... 
--------------------------------------------------------------------------------
SO: 7000000c6de7678, type: 48, owner: 7000000a6c97cf8, flag: INIT/-/-/0x00
row cache enqueue: count=1 session=7000000a660b8b0 object=7000000eedc13a0, request=S*Here we see the request is Shared(S) 
savepoint=2148
row cache parent object: address=7000000eedc13a0 cid=7(dc_users)*dc_users is the cache type indicated by 7 
hash=2a057ebe typ=9 transaction=7000000c42297a0 flags=00000002
own=7000000eedc1480[7000000c6de8518,7000000c6de8518] wat=7000000eedc1490[7000000c6de7568,7000000c6deed98] mode=X *The holder has it in this mode
status=VALID/-/-/-/-/-/-/-/-
request=N release=TRUE flags=0

为了找到持有者可以搜索对象,持有模式(object,MODE)(比如object=7000000eedc13a0, mode=X)

SO: 7000000c6de84e8, type: 48, owner: 7000000c42297a0, flag: INIT/-/-/0x00
row cache enqueue: count=1 session=7000000a6702710 object=7000000eedc13a0, mode=X*This confirms the Mode we thought the holder had (X)
savepoint=109
row cache parent object: address=7000000eedc13a0 cid=7(dc_users)
hash=2a057ebe typ=9 transaction=7000000c42297a0 flags=00000002
own=7000000eedc1480[7000000c6de8518,7000000c6de8518] wat=7000000eedc1490[7000000c6de7568,7000000c6df1b08] mode=X
status=VALID/-/-/-/-/-/-/-/-
request=N release=TRUE flags=0
instance lock id=QH 00000440 00000000
set=0, complete=FALSE
set=1, complete=FALSE
set=2, complete=FALSE
data=

3:Library Cache Pins (10G – Mutexes)

PROCESS 116:

waiting for 'cursor: pin S wait on X' blocking sess=0x0 seq=58849 wait_time=0 seconds since wait started=0
idn=535d1a6c, value=c1600000000, where|sleeps=5003f2428

为了找到更详细的信息使用idn=XXXXXX来进行搜索(比如:idn=535d1a6c)

KGX Atomic Operation Log 7000002e5b9d160
Mutex 7000002b8e92268(3094, 0) idn 535d1a6c oper GET_SHRD *We can see (a) That SID 3094 holds it (3094,0) and (b) we want it in Shared (GET_SHRD)
Cursor Pin uid 2489 efd 0 whr 5 slp 58733
opr=2 pso=70000028c47def0 flg=0
pcs=7000002b8e92268 nxt=0 flg=34 cld=3 hd=70000030d6c6eb0 par=7000002eefe64d0
ct=31 hsh=0 unp=0 unn=0 hvl=b825a4d0 nhv=1 ses=700000309b42600
hep=7000002b8e922e8 flg=80 ld=1 ob=7000002de49f8a0 ptr=70000022cf39db8 fex=70000022cf390c8

为了找到持有者,搜索idn=XXXXXX oper直到找到一个持有者为止(不是使用GET_XXX)(比如: idn 535d1a6c oper)

KGX Atomic Operation Log 7000002cd934270
Mutex 7000002b8e92268(3094, 0) idn 535d1a6c oper EXCL *We can see SID 3094 holds in Exclusive (EXCL)
Cursor Pin uid 3094 efd 0 whr 7 slp 0
opr=3 pso=7000002a71c4180 flg=0
pcs=7000002b8e92268 nxt=0 flg=34 cld=3 hd=70000030d6c6eb0 par=7000002eefe64d0
ct=31 hsh=0 unp=0 unn=0 hvl=b825a4d0 nhv=1 ses=700000309b42600
hep=7000002b8e922e8 flg=80 ld=1 ob=7000002de49f8a0 ptr=70000022cf39db8 fex=70000022cf390c8

4:Library Cache Pins (Pre 10G – non mutex)

PROCESS 20:

waiting for 'library cache pin' blocking sess=0x0 seq=575 wait_time=0
handle address=c00000006c0f8490, pin address=c0000000689b19a8, 10*mode+namespace=14

为了找到更详细的信息使用handle=XXXXXX来搜索(比如:handle=c00000006c0f8490)就会看到一个’request’行信息

SO: c0000000689b19a8, type: 34, owner: c00000006cf85e80, flag: INIT/-/-/0x00 
LIBRARY OBJECT PIN: pin=c0000000689b19a8 handle=c00000006c0f8490 request=S lock=c00000006d00e218 *We can see we want it in Shared (S)
user=c00000005eeafeb0 session=c00000005eeafeb0 count=0 mask=0000 savepoint=17 flags=[00]

然后为了找到持有者搜索’handle=XXXXXX mode’直到你找到一个以不兼容模式的所持有它的持有者为止(比如

:handle=c00000006c0f8490 mode)SO: c00000006b1f4780, type: 34, owner: c0000000699758e8, flag: INIT/-/-/0x00
LIBRARY OBJECT PIN: pin=c00000006b1f4780 handle=c00000006c0f8490 mode=X lock=c00000006b6c40a0 *We hold it in Exclusive (X)
user=c00000005edf0f48 session=c00000005edf0f48 count=1 mask=0001 savepoint=49 flags=[00]

5:Library Cache Lock

PROCESS 35:

waiting for 'library cache lock' blocking sess=0x0 seq=35844 wait_time=0 seconds since wait started=14615
handle address=70000030de975a8, lock address=70000026947e190, 100*mode+namespace=12d

为了找到更多详细信息以handle=address格式来使用handle address来进行搜索(比如:handle=70000030de975a8)

SO: 70000026947e190, type: 53, owner: 700000308d726f0, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=70000026947e190 handle=70000030de975a8 request=X *We want it in Exclusive (X)
call pin=0 session pin=0 hpc=0000 hlc=0000
htl=70000026947e210[7000002b333ffe8,7000002b333ffe8] htb=7000002b333ffe8 ssga=7000002b333f2a0
user=700000307a7ca68 session=700000307a7ca68 count=0 flags=[0000] savepoint=0x23e411
LIBRARY OBJECT HANDLE: handle=70000030de975a8 mtx=70000030de976d8(0) cdp=0
name=ACSELP.POLIZA *This is the object we are trying to lock

为了找到持有者搜索’handle=XXXXXXXXXX mode=’直到你找到一个持有者(不能是NULL)(比如: handle=70000030de975a8 mode=)

SO: 700000288b03ae0, type: 53, owner: 7000002cc697468, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=700000288b03ae0 handle=70000030de975a8 mode=S *We hold in in Shared (S)
call pin=0 session pin=0 hpc=0000 hlc=0000
htl=700000288b03b60[7000002a179a1a8,7000002b3800878] htb=7000002b3800878 ssga=7000002b37ffb30
user=70000030fafab00 session=70000030fafab00 count=1 flags=[0000] savepoint=0x417
LIBRARY OBJECT HANDLE: handle=70000030de975a8 mtx=70000030de976d8(0) cdp=0
name=ACSELP.POLIZA *This confirms the object

6:Latch free

PROCESS 8: 

waiting for 'latch free' blocking sess=0x0 seq=4577 wait_time=0
address=99ff60018, number=9d, tries=0 *9d is the latch# from v$latchname in HEX

如果查看顶级进程转储信息你就会看到正在等待的精确的闩锁甚至是它的持有者:

waiting for 99ff60018 Child library cache level=5 child#=3 
Location from where latch is held: kglic: child
Context saved from call: 26
state=busy
possible holder pid = 127 ospid=23086 *This tell us PROCESS 127 (ospid:23086) holds it
wtr=99ff60018, next waiter 9993858b8

所以进程127持有它如果现在去查看进程127将会看到:

holding 99ff60018 Child library cache level=5 child#=3 
Location from where latch is held: kglic: child
Context saved from call: 26
state=busy

如果想知道持有者引用的什么对象可以使用’handle=XXXXXXXXXX’来进行搜索直到你看到LIBRARY OBJECT HANDLE为止
(比如:handle=c00000006c0f8490)

LIBRARY OBJECT HANDLE: handle=c00000006c0f8490
name=SELECT USER FROM DUAL *This is the name of the handle
hash=cd1ceca0 timestamp=11-23-2013 09:00:00
namespace=CRSR flags=RON/TIM/PN0/SML/[12010000]*It is a CURSOR (CRSR).. but we can tell that by the name!

使用exp命令导出报EXP-00008 和ORA-01455的解决方法

问题描述:
用10g客户端导11g数据库数据时,出现ora-01455错误,如下:

C:\Windows\System32>exp hxhrlabor/hxhrlabor@hh log=f:/hjx/22/hxhrlaborazeexp.log
 file=f:/hjx/22/hxhrlaborexp.dmp  tables=(PROCESS_INST_MGR,SYS_BULLETIN_FILE,SYS
_DATAEXCHANGE_DATA,SYS_HELP,SYS_SYSTEM_LOG,SZA4)

Export: Release 10.2.0.3.0 - Production on 星期三 12月 18 15:04:35 2013

Copyright (c) 1982, 2005, Oracle.  All rights reserved.0

连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即将导出指定的表通过常规路径...
EXP-00008: 遇到 ORACLE 错误 1455
ORA-01455: 转换列溢出整数数据类型
EXP-00000: 导出终止失败

在mos上找关于这个错误的文
Errors EXP-8 ORA-1455 When Using EXP Utility To Export Large Tables (文档 ID 1513487.1)
上面说错误原因是当使用exp工具时当OCI代码引用SYS.EXU9STO表的内容时数字溢出与这个OCI变量有关.可以参考bug 15985925 EXP: ORA-01455: CONVERTING COLUMN OVERFLOWS INTEGER DATATYPE.

Export Fails With EXP-8 Oracle Error ORA-1455 Converting Column Overflows Integer Datatype (文档 ID 1096832.1)
上面说错误原因是因为Bug 9306119.
解决方法:
使用expdp进行导出

oracle impdp network_link直接导入数据报ora-39064 ora-29285

在使用impdp通过network_link方式从一个数据库中直接导入数据到另一个数据库中在导入第一个用户正常,在导入第二个用户时报错ora-39064,ora-29285故障,检查发现这里不存在没有权限,空间不足的问题

[oracle@localhost ~]$ impdp jbtest/jbtest network_link=JBZSK.REGRESS.RDBMS.DEV.US.ORACLE.COM schemas=jb  remap_schema=jb:jbtest job_name=jbjy1

Import: Release 10.2.0.1.0 - Production on Wednesday, 11 December, 2013 7:40:46

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "JBTEST"."JBJY1":  jbtest/******** network_link=JBZSK.REGRESS.RDBMS.DEV.US.ORACLE.COM schemas=jb remap_schema=jb:jbtest job_name=jbjy1
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 22.62 GB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"JBTEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
ORA-31684: Object type SYNONYM:"JBTEST"."HISOPERLOG" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."OPERLOG" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."SYN_GL_PZML" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."SYN_GL_PZNR" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."SYN_IF_CORP_ARRIVE_INFO" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."SYN_IF_CORP_ASSIGN_INFO" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."SYN_IF_CORP_BASE_INFO" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."SYN_IF_CORP_FEE_INFO" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."SYN_IF_CORP_INSR_INFO" already exists
Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC
ORA-31684: Object type TYPE:"JBTEST"."TY_STR_SPLIT" already exists
Processing object type SCHEMA_EXPORT/DB_LINK
ORA-31684: Object type DB_LINK:"JBTEST"."CAIWU" already exists
ORA-31684: Object type DB_LINK:"JBTEST"."CAIWU.REGRESS.RDBMS.DEV.US.ORACLE.COM" already exists
ORA-31684: Object type DB_LINK:"JBTEST"."JBCSK" already exists
ORA-31684: Object type DB_LINK:"JBTEST"."JBCSK.REGRESS.RDBMS.DEV.US.ORACLE.COM" already exists
ORA-31684: Object type DB_LINK:"JBTEST"."JBTEST" already exists
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"JBTEST"."OFS_ID_SEQ" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_AAE062" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_AAE064" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_AAE911" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_AB01_DUAN" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_AC01_DUAN" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_A_AAB001" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_A_AAC001" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_A_AAC021" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_A_ICKH" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_CC02_ACC020" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_CC03_ACC030" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_K_BILLNO" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_K_HOSPITEM" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_K_ICKH" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_K_PAYNO" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_K_RECIPENO" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_LOCALDB_UPDATE_NO" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_SYS" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_TRANSNO" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDD_OP_SEQ" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDJ_JOB_ID_SEQ" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDJ_JOB_OUTPUT_SEQ" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDM_PAGING_CRR_ID_SEQ" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDM_SEQUENCE_NUM" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDS_SESSIONS_SEQUENCE" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDU_OBJECTS_SEQUENCE" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDU_PRINCIPALS_SEQUENCE" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SQE_GS01_XH" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SQE_GS02_XH" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SQE_TESTNO" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
ORA-39064: unable to write to the log file
ORA-29285: file write error

ORA-39064: unable to write to the log file
ORA-29285: file write error

Job "JBTEST"."JBJY1" stopped due to fatal error at 07:41:17

加入nologfile=y参数可以解决这个问题.

[oracle@localhost ~]$ impdp jbtest/jbtest network_link=JBZSK.REGRESS.RDBMS.DEV.US.ORACLE.COM schemas=sy02 remap_schema=jb:jbtest job_name=jbtest nologfile=y

Import: Release 10.2.0.1.0 - Production on Wednesday, 11 December, 2013 7:57:14

Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Starting "JBTEST"."JBTEST":  jbtest/******** network_link=JBZSK.REGRESS.RDBMS.DEV.US.ORACLE.COM schemas=sy02 remap_schema=jb:jbtest job_name=jbtest nologfile=y
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 22.62  GB
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"SYTEST" already exists
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/SYNONYM/SYNONYM
ORA-31684: Object type SYNONYM:"JBTEST"."HISOPERLOG" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."OPERLOG" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."SYN_GL_PZML" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."SYN_GL_PZNR" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."SYN_IF_CORP_ARRIVE_INFO" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."SYN_IF_CORP_ASSIGN_INFO" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."SYN_IF_CORP_BASE_INFO" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."SYN_IF_CORP_FEE_INFO" already exists
ORA-31684: Object type SYNONYM:"JBTEST"."SYN_IF_CORP_INSR_INFO" already exists
Processing object type SCHEMA_EXPORT/DB_LINK
ORA-31684: Object type DB_LINK:"JBTEST"."CAIWU" already exists
ORA-31684: Object type DB_LINK:"JBTEST"."DB_SHENGTING" already exists
Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE
ORA-31684: Object type SEQUENCE:"JBTEST"."OFS_ID_SEQ" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_AAE062" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_AAE064" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_AB01_DUAN" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_AC01_DUAN" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_A_AAB001" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_A_AAC001" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_A_AAC021" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_A_ICKH" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_CC02_ACC020" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_CC03_ACC030" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_K_BILLNO" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_K_HOSPITEM" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_K_ICKH" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_K_PAYNO" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_K_RECIPENO" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_LOCALDB_UPDATE_NO" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_SYS" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_SYS_SY" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SEQ_TRANSNO" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDD_OP_SEQ" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDJ_JOB_ID_SEQ" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDJ_JOB_OUTPUT_SEQ" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDM_PAGING_CRR_ID_SEQ" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDM_SEQUENCE_NUM" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDS_SESSIONS_SEQUENCE" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDU_OBJECTS_SEQUENCE" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SMP_VDU_PRINCIPALS_SEQUENCE" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SQE_GS01_XH" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SQE_GS02_XH" already exists
ORA-31684: Object type SEQUENCE:"JBTEST"."SQE_TESTNO" already exists
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . imported "JBTEST"."AC13"                            18879318 rows
. . imported "JBTEST"."JC01"                            8791169 rows
Proudly powered by WordPress | Indrajeet by Sus Hill.