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')

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/32980/viewspace-1056431/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/32980/viewspace-1056431/