本文探讨了Oracle 12c环境下，对于含有默认值字段的表使用EXPDIRECT导出时遇到的问题及解决方案。实验证明，当表中有带有默认值且未标记为NOT NULL的字段时，直接路径导出会失败。
[20180310]12c exp 无法dirct的情况.txt
--//前一阵子测试.exp 无法dirct的情况的链接: http://blog.itpub.net/267265/viewspace-2151290/
--//12c 改进增加字段与缺省值的情况,允许不要加not null修改表块.测试看看,看看导出会是什么情况.
--//关于12c下增加字段与缺省值,可以参考http://blog.itpub.net/267265/viewspace-1335561/
1.环境:
SCOTT@test01p> SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0 Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
create table t ( a number);
insert into t values(1);
insert into t values(2);
insert into t values(3);
commit;
alter table t add (name varchar2(10) default 'test');
SCOTT@test01p> select rowid,t.* , SYS_NC00002$ from t;
ROWID A NAME SYS_NC00002$
------------------ ---------- -------------------- --------------------
AAAaKGAAJAAAAC9AAA 1 test
AAAaKGAAJAAAAC9AAB 2 test
AAAaKGAAJAAAAC9AAC 3 test
--//以前的测试已经提到12c它通过增加1个隐含字段里面的bit来表示这些字段从那里来读取.
--//后续的插入即使insert不带这些字段,其缺省值也插入数据块中.
select obj#,col#,segcol#,name,default$,type# from sys.col$ where
obj# in ( select object_id from dba_objects where owner=user and object_name='T')
order by col#;
OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE#
---------- ---------- ---------- -------------------- ------------------------------ -----------
107142 0 2 SYS_NC00002$ 23
107142 1 1 A 2
107142 2 3 NAME 'test' 1
--//SYS_NC00002$是一个隐含字段,SEGCOL# 说明字段在段中保存顺序,安装上面的显示顺序是A,SYS_NC00002$,NAME.
SCOTT@test01p> @ rowid AAAaKGAAJAAAAC9AAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
107142 9 189 0 0x24000BD 9,189 alter system dump datafile 9 block 189 ;
SCOTT@test01p> alter system checkpoint ;
System altered.
SCOTT@test01p> alter system dump datafile 9 block 189 ;
System altered.
Block header dump: 0x024000bd
Object id on Block? Y
seg/obj: 0x1a286 csc: 0x00.19ab452 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x24000b8 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000d.00c.00000d7f 0x0140055b.02fe.1d --U- 3 fsc 0x0000.019ab456
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x024000bd
data_block_dump,data header at 0x20f8064
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x020f8064
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f86
avsp=0x1f65
tosp=0x1f65
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f92
0x14:pri[1] offs=0x1f8c
0x16:pri[2] offs=0x1f86
block_row_dump:
tab 0, row 0, @0x1f92
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 02
tab 0, row 1, @0x1f8c
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 03
tab 0, row 2, @0x1f86
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 04
end_of_block_dump
End dump data blocks tsn: 3 file#: 9 minblk 189 maxblk 189
--//可以发现对应块中没有test字符串.
2.使用exp direct导出看看:
D:\tools\rlwrap>exp scott/btbtms@test01p file=t.dmp tables=t direct=y RECORDLENGTH=65535
exp scott/btbtms@test01p file=t.dmp tables=t direct=y RECORDLENGTH=65535
Export: Release 12.1.0.1.0 - Production on Sat Mar 10 21:00:33 2018
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...
. . exporting table T
EXP-00008: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-01426: numeric overflow
Export terminated successfully with warnings.
--//可以发现无法使用direct导出.
D:\tools\rlwrap>exp scott/btbtms@test01p file=t.dmp tables=t buffer=10485760
exp scott/btbtms@test01p file=t.dmp tables=t buffer=10485760
Export: Release 12.1.0.1.0 - Production on Sat Mar 10 21:02:01 2018
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics 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 3 rows exported
Export terminated successfully without warnings.
--//只能使用传统导出方式.
3.imp导入看看.
SCOTT@test01p> alter table t rename to t1;
Table altered.
D:\tools\rlwrap>imp scott/btbtms@test01p tables=T file=t.dmp buffer=10485760
imp scott/btbtms@test01p tables=T file=t.dmp buffer=10485760
Import: Release 12.1.0.1.0 - Production on Sat Mar 10 21:04:16 2018
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export file created by EXPORT:V12.01.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "T" 3 rows imported
Import terminated successfully without warnings.
SCOTT@test01p> select rowid,t.* , SYS_NC00002$ from t; 2$ from t;
select rowid,t.* , SYS_NC00002$ from t
*
ERROR at line 1:
ORA-00904: "SYS_NC00002$": invalid identifier
--//隐含字段已经不存在了.
select obj#,col#,segcol#,name,default$,type# from sys.col$ where
obj# in ( select object_id from dba_objects where owner=user and object_name='T')
order by col#;
OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE#
---------- ---------- ---------- -------------------- ------------------------------ -----------
107143 1 1 A 2
107143 2 2 NAME 'test' 1
SCOTT@test01p> select rowid,t.* from t;
ROWID A NAME
------------------ ---------- --------------------
AAAaKHAAJAAACcHAAA 1 test
AAAaKHAAJAAACcHAAB 2 test
AAAaKHAAJAAACcHAAC 3 test
SCOTT@test01p> @ rowid AAAaKHAAJAAACcHAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
107143 9 9991 0 0x2402707 9,9991 alter system dump datafile 9 block 9991;
SCOTT@test01p> alter system checkpoint ;
System altered.
SCOTT@test01p> alter system dump datafile 9 block 9991;
System altered.
Block header dump: 0x02402707
Object id on Block? Y
seg/obj: 0x1a287 csc: 0x00.19abbb7 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x2402700 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.001.000060e6 0x014004b8.057b.1f --U- 3 fsc 0x0000.019abbb8
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x02402707
data_block_dump,data header at 0x20f8064
===============
tsiz: 0x1f98
hsiz: 0x18
pbl: 0x020f8064
76543210
flag=--------
ntab=1
nrow=3
frre=-1
fsbo=0x18
fseo=0x1f77
avsp=0x1f5f
tosp=0x1f5f
0xe:pti[0] nrow=3 offs=0
0x12:pri[0] offs=0x1f77
0x14:pri[1] offs=0x1f82
0x16:pri[2] offs=0x1f8d
block_row_dump:
tab 0, row 0, @0x1f77
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 4] 74 65 73 74
tab 0, row 1, @0x1f82
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 03
col 1: [ 4] 74 65 73 74
tab 0, row 2, @0x1f8d
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 04
col 1: [ 4] 74 65 73 74
end_of_block_dump
End dump data blocks tsn: 3 file#: 9 minblk 9991 maxblk 9991
4.回到前面测试direct的情况:
--//注意原来的表T1.
SCOTT@test01p> ALTER SYSTEM SET EVENTS ' 604 TRACE NAME ERRORSTACK LEVEL 12';
System altered.
SCOTT@test01p> ALTER SYSTEM SET EVENTS ' 1426 TRACE NAME ERRORSTACK LEVEL 12';
System altered.
D:\tools\rlwrap>exp scott/btbtms@test01p file=t1.dmp tables=t1 direct=y RECORDLENGTH=65535
exp scott/btbtms@test01p file=t1.dmp tables=t1 direct=y RECORDLENGTH=65535
Export: Release 12.1.0.1.0 - Production on Sat Mar 10 21:12:40 2018
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...
. . exporting table T1
EXP-00008: ORACLE error 604 encountered
ORA-00604: error occurred at recursive SQL level 1
ORA-01426: numeric overflow
Export terminated successfully with warnings.
--//检查跟踪文件:
*** 2018-03-10 21:12:42.101
*** SESSION ID:(355.225) 2018-03-10 21:12:42.101
*** CLIENT ID:() 2018-03-10 21:12:42.101
*** SERVICE NAME:(test01p) 2018-03-10 21:12:42.101
*** MODULE NAME:(exp.exe) 2018-03-10 21:12:42.101
*** ACTION NAME:() 2018-03-10 21:12:42.101
*** CONTAINER ID:(3) 2018-03-10 21:12:42.101
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=12, mask=0x0)
----- Error Stack Dump -----
ORA-01426: numeric overflow
----- Current SQL Statement for this session (sql_id=dpdxazctjyx2u) -----
select type#, property from col$ where obj#=:1 order by segcol#
....
*** 2018-03-10 21:12:45.812
dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=12, mask=0x0)
----- Error Stack Dump -----
ORA-00604: error occurred at recursive SQL level 1
ORA-01426: numeric overflow
----- SQL Statement (None) -----
Current SQL information unavailable - no cursor.
SCOTT@test01p> select type#, property from sys.col$ where obj#=107142 order by segcol#;
TYPE# PROPERTY
----------- ----------------------------
2 0
23 549755814176
1 1073741824
SCOTT@test01p> select col#,segcol#,type#, property from sys.col$ where obj#=107142 order by segcol#;
COL# SEGCOL# TYPE# PROPERTY
---------- ---------- ----------- ----------------------------
1 1 2 0
0 2 23 549755814176
2 3 1 1073741824
--//估计PROPERTY=549755814176,或者后面PROPERTY=1073741824,发生了溢出.
5.测试11g建立缺省值的情况如何:
SCOTT@test01p> drop table t purge ;
Table dropped.
SCOTT@test01p> drop table t1 purge ;
Table dropped.
create table t ( a number);
insert into t values(1);
insert into t values(2);
insert into t values(3);
commit;
alter table t add (name varchar2(10) default 'test' not null);
--//注意加入了not null约束.
select obj#,col#,segcol#,name,default$,type# from sys.col$ where
obj# in ( select object_id from dba_objects where owner=user and object_name='T')
order by col#;
OBJ# COL# SEGCOL# NAME DEFAULT$ TYPE#
---------- ---------- ---------- -------------------- ------------------------------ -----------
107144 1 1 A 2
107144 2 2 NAME 'test' 1
--//没有隐含字段.
D:\tools> exp scott/btbtms@test01p file=t.dmp tables=t direct=y RECORDLENGTH=65535
exp scott/btbtms@test01p file=t.dmp tables=t direct=y RECORDLENGTH=65535
Export: Release 12.1.0.1.0 - Production on Sat Mar 10 21:27:33 2018
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables via Direct Path ...
. . exporting table T 3 rows exported
Export terminated successfully without warnings.
SCOTT@test01p> alter table t rename to t1;
Table altered.
D:\tools> imp scott/btbtms@test01p tables=T file=t.dmp buffer=10485760
imp scott/btbtms@test01p tables=T file=t.dmp buffer=10485760
Import: Release 12.1.0.1.0 - Production on Sat Mar 10 21:28:28 2018
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Export file created by EXPORT:V12.01.00 via direct path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. importing SCOTT's objects into SCOTT
. . importing table "T"
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("SCOTT"."T"."NAME")
Column : 1
Column :
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("SCOTT"."T"."NAME")
Column : 2
Column :
IMP-00019: row rejected due to ORACLE error 1400
IMP-00003: ORACLE error 1400 encountered
ORA-01400: cannot insert NULL into ("SCOTT"."T"."NAME")
Column : 3
Column : 0 rows imported
Import terminated successfully with warnings.
--//遇到前面11g测试一样的情况.
扫一扫
590
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
