ORA-39125 ORA-06502 錯誤解決

ORACLE 10.2.0.5的expdp/impdp工具有一些bug,在進行資料庫全庫遷移時,匯入物件的狀態時,報發生嚴重的錯誤,最後導致了impdp操作的異常終止,此時使用者資料已經成功匯入了資料庫.
但匯入statistics資料時,發生瞭如下的錯誤:
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
ORA-39125: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS while calling DBMS_METADATA.CONVERT [TABLE_STATISTICS]
ORA-06502: PL/SQL: numeric or value error
LPX-00225: end-element tag "HIST_GRAM_LIST_ITEM" does not match start-element tag "EPVALUE"
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 105
ORA-06512: at "SYS.KUPW$WORKER", line 6377
----- PL/SQL Call Stack -----
object line object
handle number name
70000020cb889d8 15370 package body SYS.KUPW$WORKER
70000020cb889d8 6436 package body SYS.KUPW$WORKER
70000020cb889d8 12590 package body SYS.KUPW$WORKER
70000020cb889d8 3397 package body SYS.KUPW$WORKER
70000020cb889d8 7064 package body SYS.KUPW$WORKER
70000020cb889d8 1340 package body SYS.KUPW$WORKER
7000001cc586438 2 anonymous block
Job "SYSTEM"."SYS_IMPORT_SCHEMA_03" stopped due to fatal error at 16:49:04[@more@]

oracle metalink 的一篇文章(ID 878626.1)中給出瞭解決的辦法:

DataPump Import Including Statistics Terminates With Fatal Error ORA-39126 ORA-6502 LPX-225 End-Element Tag "HIST_GRAM_LIST_ITEM" [ID 878626.1]
......
Cause
The statistics included in the import may be malformed.
Solution
To workaround this issue, repeat either the expdp or the impdp using the parameter EXCLUDE=STATISTICS.
After import, regather the statistics for the imported objects in the target database using the DBMS_STATS package.

下面重點說明一下如何使用DBMS_STATS包進行整庫統計資訊的手工遷移.

1.建立一個記錄統計資訊的表
建立表的函式說明如下：
procedure create_stat_table(
ownname varchar2, stattab varchar2,
tblspace varchar2 default null,
global_temporary boolean default false);
--
-- Creates a table with name 'stattab' in 'ownname's
-- schema which is capable of holding statistics. The columns
-- and types that compose（組成） this table are not relevant（關聯） as it
-- should be accessed solely（單獨） through the procedures in this
-- package.
--
-- Input arguments:
-- ownname - The name of the schema
-- stattab - The name of the table to create. This value should
-- be passed as the 'stattab' argument to other procedures
-- when the user does not wish to modify the dictionary statistics
-- directly.
-- tblspace - The tablespace in which to create the stat tables.
-- If none is specified, they will be created in the user's
-- default tablespace.
-- table_options - Whether or not the table should be created as a global
-- temporary table.
--
-- Exceptions:
-- ORA-20000: Insufficient privileges
-- ORA-20001: Tablespace does not exist
-- ORA-20002: Table already exists
--

執行DBMS_STATS.create_stat_table過程,建立儲存統計資訊的表.
SQL>execute dbms_stats.create_stat_table('SYSTEM','AIDU_STATS','SYSTEM');

SQL>select owner,table_NAME,TABLESPACE_NAME from all_tables where table_name like '%AIDU%'
OWNER TABLE_NAME TABLESPACE_NAME
-----------------------------------------------------------
SYSTEM AIDU_STATS SYSTEM

2. 匯出資料庫統計資訊到指定的表中.
procedure export_database_stats(
stattab varchar2, statid varchar2 default null,
statown varchar2 default null);
--
-- Retrieves statistics for all objects in the database
-- and stores them in the user stat tables identified
-- by statown.stattab
--
-- Input arguments:
-- stattab - The user stat table identifier describing where
-- to store the statistics.
-- statid - The (optional) identifier to associate with these statistics
-- within stattab.
-- statown - The schema containing stattab.
-- If statown is null, it is assumed that every schema in the database
-- contains a user statistics table with the name stattab.
--
-- Exceptions:
-- ORA-20000: Object does not exist or insufficient privileges
-- ORA-20002: Bad user statistics table, may need to upgrade it

本例對引數statid進行了一個測試,statid可以用來儲存多次統計資訊,是每次統計資訊的一個主鍵標識.

如果statid 沒有設定,存入統計資訊實體表的所有記錄的STATID列為NULL值
如果statown沒有設定,則預設為SYS 使用者

execute DBMS_STATS.EXPORT_DATABASE_STATS('AIDU_STATS',NULL,NULL);

SQL> desc sys.aidu_stats;
Name Null? Type
----------------------------------------- -------- ----------------------------
STATID VARCHAR2(30)
TYPE CHAR(1)
VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(30)
C2 VARCHAR2(30)
C3 VARCHAR2(30)
C4 VARCHAR2(30)
C5 VARCHAR2(30)
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
N7 NUMBER
N8 NUMBER
N9 NUMBER
N10 NUMBER
N11 NUMBER
N12 NUMBER
D1 DATE
R1 RAW(32)
R2 RAW(32)
CH1 VARCHAR2(1000)

SQL> select statid,count(1) from sys.aidu_stats group by statid;
STATID COUNT(1)
------------------------------ ----------
317763

C5列是儲存了使用者資訊的列,可以根據這個列進行分類統計,知道資料庫各個使用者的統計資訊資料量.
SQL> select c5,count(1) from sys.aidu_stats group by c5;

C5 COUNT(1)
------------------------------ ----------
GSMDIS 14954
SYSTEM 3681
SYS 47772
TSMSYS 9
SEDD 243039
SYSMAN 4040
LICKET_NEW 1845
FS4TRAN 1291
WMSYS 390
CTXSYS 413
DBSNMP 178

C5 COUNT(1)
------------------------------ ----------
OUTLN 49
SCDDDE_MIS 102

13 rows selected.

如果設定了STATID ,則STATID列值為'FIRST'
SQL> execute DBMS_STATS.EXPORT_DATABASE_STATS('AIDU_STATS','FIRST',NULL);

PL/SQL procedure successfully completed.

SQL> select statid,count(1) from sys.aidu_stats group by statid;

STATID COUNT(1)
------------------------------ ----------
317763
FIRST 317763

SQL> execute DBMS_STATS.EXPORT_DATABASE_STATS('AIDU_STATS','SECOND',NULL);

PL/SQL procedure successfully completed.

SQL> select statid,count(1) from sys.aidu_stats group by statid;

STATID COUNT(1)
------------------------------ ----------
317763
SECOND 317763
FIRST 317763

多次統計後,將覆蓋STATID鍵值相同的記錄
SQL> execute DBMS_STATS.EXPORT_DATABASE_STATS('AIDU_STATS',NULL,NULL);

PL/SQL procedure successfully completed.

SQL> select statid,count(1) from sys.aidu_stats group by statid;

STATID COUNT(1)
------------------------------ ----------
317763
SECOND 317763
FIRST 317763

SQL>truncate table sys.aidu_stats;

最後,做一個設定statid的統計資訊收集
SQL> execute DBMS_STATS.EXPORT_DATABASE_STATS('AIDU_STATS','STATS20111129','SYSTEM');

3.使用exp工具匯出儲存統計資訊的表

exp system/test4_rman file=/exp/aidu_stats.dmp tables=aidu_stats log=/exp/exp_aidu_stats.log

4.將dmp檔案複製到目標庫,並使用imp命令進行,匯入資料.

imp system/test4_rman file=/imp/aidu_stats.dmp log=/imp/imp_aidu_stats.log ignore=y

5.匯入統計資訊,覆蓋以前的統計資訊
procedure import_database_stats(
stattab varchar2, statid varchar2 default null,
statown varchar2 default null,
no_invalidate boolean default
to_no_invalidate_type(get_param('NO_INVALIDATE')),
force boolean default FALSE);
--
-- Retrieves statistics for all objects in the database
-- from the user stat table(s) and stores them in the
-- dictionary
--
-- Input arguments:
-- stattab - The user stat table identifier describing from where
-- to retrieve the statistics.
-- statid - The (optional) identifier to associate with these statistics
-- within stattab.
-- statown - The schema containing stattab.
-- If statown is null, it is assumed that every schema in the database
-- contains a user statistics table with the name stattab.
-- no_invalidate - Do not invalide the dependent cursors if set to TRUE.
-- The procedure invalidates the dependent cursors immediately
-- if set to FALSE.
-- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to
-- invalidate dependend cursors. This is the default. The default
-- can be changed using set_param procedure.
-- force - Override statistics lock.
-- TRUE- Ignores the statistics lock on objects and import
-- the statistics.
-- FALSE-The statistics of an object will be imported only if it
-- is not locked.
-- ie if both DATA and CACHE statistics is locked, it will not
-- import anything. If CACHE statistics of an object is locked,
-- only DATA statistics will be imported and vice versa.
--
-- Exceptions:
-- ORA-20000: Object does not exist or insufficient privileges
-- ORA-20001: Invalid or inconsistent values in the user stat table
-- ORA-20002: Bad user statistics table, may need to upgrade it
--

SQL>execute dbms_stats.import_database_stats('AIDU_STATS','STATS20111129','SYSTEM')