資料泵匯入分割槽表統計資訊報錯（三）
今天在進行資料泵匯入操作時，發現一個bug。
前兩篇文章分別介紹了問題的發現和深入，這篇文章檢查問題和表的MONITORING屬性之間的關係。
資料泵匯入分割槽表統計資訊報錯（一）：http://yangtingkun.itpub.net/post/468/456176
資料泵匯入分割槽表統計資訊報錯（二）：http://yangtingkun.itpub.net/post/468/456378
前不久檢查了一篇以往的BLOG，意外的發現這個bug居然被我忘記了，時隔一年繼續解決這個問題。
根據上一篇文章所介紹的分析過程，基本上可以確認和這幾張分割槽表的統計資訊本身有關。
由於當前資料庫是從920環境EXP匯出，IMP匯入到同版本的中間資料庫，最終透過資料泵IMPDP匯入到當前資料庫的。而分割槽表由於無法解決表空間的轉換問題，因此在中間資料庫手工建立，在IMP匯入的時候指定了IGNORE=Y引數。
而現在恰好問題出在這個使用者的所有分割槽表上，難道問題和遷移的過程有關係。檢查了當時的指令碼，沒有發現異常之處。
不過由於原始資料的版本是9204，而遷移後的版本是10203，有可能是版本的不同導致了遷移過程中某些引數的設定發生了變化。
表的儲存引數中與統計資訊相關的就是MONITORING了。這個儲存引數比較有意思，從9i開始引入，到了10g及以後版本中，這個引數又消失了。並不是Oracle認為這個引數沒有意義而去掉了，而是Oracle認為這個MONITORING功能的代價很小，而對於統計資訊十分有幫助，因此變成了Oracle的預設的行為，只留下了一個隱含引數來控制是否進行MONITORING的操作。
檢查這幾個表的MONITORING屬性，發現值都是YES：
SQL> SELECT TABLE_NAME, MONITORING
2 FROM USER_TABLES
3 WHERE TABLE_NAME IN
4 (SELECT TABLE_NAME FROM USER_PART_TABLES);
TABLE_NAME MON
------------------------------ ---
EMED_WEB_LOG YES
ORD_LOG_HIT_COMM YES
ORD_PURCHASE_ITEM YES
ORD_ORDER_ITEM YES
ORD_ORDER YES
CON_LOG_LIST_ITEM YES
已選擇6行。
但是10g和9i的資料字典中MONITORING的值的來源是不同的，在9i中：
SQL> SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME = 'DBA_TABLES';
TEXT
--------------------------------------------------------------------------------
select u.name, o.name, decode(bitand(t.property, 4194400), 0, ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
0, null, co.name),
decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, null),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))),
decode(bitand(t.property, 32), 32, null,
decode(bitand(t.flags, 32), 0, 'YES', 'NO')),
decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
t.avgspc, t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),
decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, 'YES', 'NO'),
decode(bitand(t.property, 64), 64, 'IOT',
decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',
decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null
))),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
decode(bitand(t.property, 8192), 8192, 'YES',
decode(bitand(t.property, 1), 0, 'NO', 'YES')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)),
decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),
decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
'SYS$SESSION', 'SYS$TRANSACTION')),
decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),
decode(bitand(t.flags, 2097152), 2097152, 'YES', 'NO'),
decode(bitand(t.property, 1024), 0, null, cu.name),
decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),
decode(bitand(t.property, 32), 32, null,
decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED'))
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.obj$ cx, sys.user$ cu
where o.owner# = u.user#
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
9i中MONITORING欄位的值是表的屬性：decode(bitand(t.flags, 2097152), 2097152, 'YES', 'NO')。
而10g中則不同：
SQL> SELECT TEXT FROM DBA_VIEWS WHERE VIEW_NAME = 'DBA_TABLES';
TEXT
--------------------------------------------------------------------------------
select u.name, o.name, decode(bitand(t.property,2151678048), 0, ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name),
decode((bitand(t.property, 512)+bitand(t.flags, 536870912)),
0, null, co.name),
decode(bitand(t.trigflag, 1073741824), 1073741824, 'UNUSABLE', 'VALID'),
decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)),
decode(bitand(t.property, 32), 0, t.initrans, null),
decode(bitand(t.property, 32), 0, t.maxtrans, null),
s.iniexts * ts.blocksize,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extsize * ts.blocksize),
s.minexts, s.maxexts,
decode(bitand(ts.flags, 3), 1, to_number(NULL),
s.extpct),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))),
decode(bitand(ts.flags, 32), 32, to_number(NULL),
decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))),
decode(bitand(t.property, 32+64), 0,
decode(bitand(t.flags, 32), 0, 'YES', 'NO'), null),
decode(bitand(t.flags,1), 0, 'Y', 1, 'N', '?'),
t.rowcnt,
decode(bitand(t.property, 64), 0, t.blkcnt, null),
decode(bitand(t.property, 64), 0, t.empcnt, null),
t.avgspc, t.chncnt, t.avgrln, t.avgspc_flb,
decode(bitand(t.property, 64), 0, t.flbcnt, null),
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
lpad(decode(bitand(t.flags, 8), 8, 'Y', 'N'),5),
decode(bitand(t.flags, 6), 0, 'ENABLED', 'DISABLED'),
t.samplesize, t.analyzetime,
decode(bitand(t.property, 32), 32, 'YES', 'NO'),
decode(bitand(t.property, 64), 64, 'IOT',
decode(bitand(t.property, 512), 512, 'IOT_OVERFLOW',
decode(bitand(t.flags, 536870912), 536870912, 'IOT_MAPPING', null
))),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
decode(bitand(t.property, 8192), 8192, 'YES',
decode(bitand(t.property, 1), 0, 'NO', 'YES')),
decode(bitand(o.flags, 2), 2, 'DEFAULT',
decode(s.cachehint, 0, 'DEFAULT', 1, 'KEEP', 2, 'RECYCLE', NULL)),
decode(bitand(t.flags, 131072), 131072, 'ENABLED', 'DISABLED'),
decode(bitand(t.flags, 512), 0, 'NO', 'YES'),
decode(bitand(t.flags, 256), 0, 'NO', 'YES'),
decode(bitand(o.flags, 2), 0, NULL,
decode(bitand(t.property, 8388608), 8388608,
'SYS$SESSION', 'SYS$TRANSACTION')),
decode(bitand(t.flags, 1024), 1024, 'ENABLED', 'DISABLED'),
decode(bitand(o.flags, 2), 2, 'NO',
decode(bitand(t.property, 2147483648), 2147483648, 'NO',
decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO'))),
decode(bitand(t.property, 1024), 0, null, cu.name),
decode(bitand(t.flags, 8388608), 8388608, 'ENABLED', 'DISABLED'),
decode(bitand(t.property, 32), 32, null,
decode(bitand(s.spare1, 2048), 2048, 'ENABLED', 'DISABLED')),
decode(bitand(o.flags, 128), 128, 'YES', 'NO')
from sys.user$ u, sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi
where o.owner# = u.user#
and o.obj# = t.obj#
and bitand(t.property, 1) = 0
and bitand(o.flags, 128) = 0
and t.bobj# = co.obj# (+)
and t.ts# = ts.ts#
and t.file# = s.file# (+)
and t.block# = s.block# (+)
and t.ts# = s.ts# (+)
and t.dataobj# = cx.obj# (+)
and cx.owner# = cu.user# (+)
and ksppi.indx = ksppcv.indx
and ksppi.ksppinm = '_dml_monitoring_enabled'
在這個表示式中：
decode(bitand(o.flags, 2), 2, 'NO',
decode(bitand(t.property, 2147483648), 2147483648, 'NO',
decode(ksppcv.ksppstvl, 'TRUE', 'YES', 'NO')))
首先判斷這個表是否是臨時表，然後判斷是否這個表是否是外部表。對於臨時表和外部表，設定MONITORING沒有意義，因此表對應的值也是NO。如果不屬於上面兩種情況，則表中欄位的值有初始化引數的值確定：
and ksppi.ksppinm = '_dml_monitoring_enabled'
所以10g中所有的普通表都是MONITORING為YES。是否是表本身上的屬性是NOMONITORING，但是顯示的結果是MONITORING導致了這個問題呢。
但是檢查DBA_TAB_MODIFICATIONS檢視發現了檢視記錄了這個表的修改，而這些修改的記錄是依靠MONITORING屬性的：
SQL> SELECT TABLE_NAME, PARTITION_NAME, INSERTS, UPDATES, DELETES, TIMESTAMP
2 FROM USER_TAB_MODIFICATIONS
3 WHERE TABLE_NAME = 'ORD_ORDER';
TABLE_NAME PARTITION_NAME INSERTS UPDATES DELETES TIMESTAMP
---------- --------------- ---------- ---------- ---------- -------------------
ORD_ORDER 2735139 10791966 491 2007-05-03 22:00:02
ORD_ORDER ORD0304 437 0 0 2007-05-03 22:00:02
ORD_ORDER ORD0307 380 0 0 2007-05-03 22:00:02
ORD_ORDER ORD0310 190 0 0 2007-05-03 22:00:02
ORD_ORDER ORD0401 1092 29 0 2007-05-03 22:00:02
ORD_ORDER ORD0404 28138 27 0 2007-05-03 22:00:02
ORD_ORDER ORD0407 41737 104 0 2007-05-03 22:00:02
ORD_ORDER ORD0410 55830 482 0 2007-05-03 22:00:02
ORD_ORDER ORD0501 56141 444 0 2007-05-03 22:00:02
ORD_ORDER ORD0504 54723 546 0 2007-05-03 22:00:02
ORD_ORDER ORD0507 77262 1000 0 2007-05-03 22:00:02
ORD_ORDER ORD0510 191954 2090 0 2007-05-03 22:00:02
ORD_ORDER ORD0601 107024 1724 0 2007-05-03 22:00:02
ORD_ORDER ORD0604 118910 1341 0 2007-05-03 22:00:02
ORD_ORDER ORD0607 129039 2431 0 2007-05-03 22:00:02
ORD_ORDER ORD0610 122062 5496 0 2007-05-03 22:00:02
ORD_ORDER ORD0701 119021 16262 0 2007-05-03 22:00:02
ORD_ORDER ORD0704 137767 686953 35 2007-05-03 22:00:02
ORD_ORDER ORD0707 149083 1138992 60 2007-07-01 22:00:01
ORD_ORDER ORD0710 157907 1177425 55 2007-10-01 22:00:02
ORD_ORDER ORD0801 155147 1052681 45 2008-01-01 22:00:03
ORD_ORDER ORD0804 175290 1187081 29 2008-04-01 22:00:02
ORD_ORDER ORD0807 187778 1271790 32 2008-07-01 22:00:02
ORD_ORDER ORD0810 194450 1298860 57 2008-10-01 22:00:03
ORD_ORDER ORD0901 176321 1170333 54 2009-01-01 22:00:02
ORD_ORDER ORD0904 204774 1279347 70 2009-04-01 22:00:04
ORD_ORDER ORD0907 92682 496528 54 2009-07-01 22:00:02
已選擇27行。
Oracle記錄了每個分割槽的修改以及表的總修改記錄數。這個資訊應該在統計資訊收集後被清除，由於統計資訊一直沒有收集，因此這個資訊已經積累了很長時間。
問題看來和MONITORING沒有關係，還要進一步檢查導致問題的原因。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/4227/viewspace-611647/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料泵匯入分割槽表統計資訊報錯（七）
- 資料泵匯入分割槽表統計資訊報錯（二）
- 資料泵匯入分割槽表統計資訊報錯（四）
- 資料泵匯入分割槽表統計資訊報錯（六）
- 資料泵匯入分割槽表統計資訊報錯（五）
- 資料泵匯入分割槽表長時間HANG住
- 使用PARTITION_OPTIONS引數控制資料泵分割槽表匯入
- 分割槽表匯入資料庫資料庫
- 匯入匯出 Oracle 分割槽表資料Oracle
- 資料泵匯出匯入表
- 大資料量分割槽表統計資訊的管理大資料
- 分割槽表入無分割槽的資料庫資料庫
- Oracle使用資料泵匯出匯入表Oracle
- 使用expdp匯出分割槽表中的部分分割槽資料
- 資料泵匯出匯入
- 資料泵匯出索引資料和統計資訊嗎索引
- 【實驗】【PARTITION】exp匯出分割槽表資料
- Impdp資料泵匯入
- [oracle] expdp 匯出分割槽表的分割槽Oracle
- 對比資料泵與原始匯入匯出工具（三）
- 資料泵的匯入匯出
- 表統計資訊匯出匯入指令碼指令碼
- Oracle資料泵－schema匯入匯出Oracle
- 使用資料泵impdp匯入資料
- 資料泵匯出匯入資料標準文件
- Oracle使用資料泵在異機之間匯出匯入表Oracle
- 10g資料泵和匯入匯出效能對比（三）
- Oracle資料泵的匯入和匯出Oracle
- Oracle資料泵匯出匯入（expdp/impdp）Oracle
- 資料泵取匯出和匯入（一）
- 資料庫系統設計：分割槽資料庫
- 11g解決imp匯入資料時報錯：插入資料找不到相應分割槽
- 資料泵無法匯入JOB
- 資料泵匯出資料包錯處理
- 轉oracle資料泵匯出時報錯Oracle
- 【學習筆記】分割槽表和分割槽索引——分割槽表的其他管理（三）筆記索引
- 自動備份、截斷分割槽表分割槽資料
- 資料庫泵（expdp/impdp）匯入匯出流程資料庫