[20180310]12c exp 無法dirct的情況.txt
[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測試一樣的情況.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2151723/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 圖片無法載入的情況下的優化優化
- [20200219]strace跟蹤設定ENABLE=BROKEN的情況(網路的情況).txt
- [20231024]NULL值在索引的情況.txtNull索引
- fastadmin 偽靜態nginx 無法訪問後端的情況ASTNginx後端
- docker容器無法啟動的情況下，如果修改配置檔案Docker
- [20211011]跟蹤freespace空間的變化情況.txt
- [20220216]為什麼出現這樣的情況.txt
- [20200219]strace跟蹤設定ENABLE=BROKEN的情況.txt
- 如何解決使用mac聚焦搜尋無法搜尋軟體的情況Mac
- [20190520]exp imp on th fly.txt
- [20211203]演示job啟動時間改變的情況.txt
- [20211209]pdb資料庫kill job遇到的奇怪情況.txt資料庫
- PbootCms模板搭建網站，可能會遇到內頁無法正常訪問的情況boot網站
- 針對Sybase資料庫無法啟動的情況，我有話要說資料庫
- [20231011]查詢sys.optstat_snapshot$瞭解表的DML情況.txt
- ash報告中無sql_id的情況SQL
- 出現ESXi系統無法連線FreeNAS的情況？90%以上的人都做錯了！
- PostgreSQL無法kill(pg_terminate_backend,pg_cancel_backend)的情況分析-程式hangstrace,pstackSQL
- iPhone 在使用 Charles 抓包情況下無法開啟 APPstore 解決方法iPhoneAPP
- ubuntu核心切換失敗，無法啟動，bios不停檢測情況修復UbuntuiOS
- [20231003]windows 2003無法啟動.txtWindows
- 分析針對EFS加密檔案無法開啟的情況資料恢復的解決方式加密資料恢復
- [20190320]測試相同語句遇到導致cursor pin S的情況.txt
- eclipse 專案gradle無反應的幾種特殊情況EclipseGradle
- 使用 VSTS 進行 CI 的過程中，無法識別 .NET Core 2.x 的情況處理
- win10系統無法開機的情況下如何開啟命令提示符操作Win10
- Win10系統無法更改ip提示出現了一個意外的情況如何解決Win10
- [20180925]共享池中的NETWORK BUFFER(12c).txt
- vue.js - 過渡&動畫 - 無效情況Vue.js動畫
- phpredis 3.1.6 擴充套件，出現指定 database 無效的情況。PHPRedis套件Database
- [20190524]DISABLE TABLE LOCK(12c).txt
- [20190703]12c Hybrid histogram.txtHistogram
- [20190624]12c group by優化 .txt優化
- [20210119]sqlplus 12c LOBPREFETCH.txtSQL
- [20181026]12c Attribute Clustering特性.txt
- 12C SQL Translation Framework.txtSQLFramework
- [20181010]12c clone pdb.txt
- 流失原因分析方法6 版本消化情況分析法