Oracle 11g是从Oracle 10g上升级来的。
今天执行exp导出遇到以下错误:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified users ...
. exporting pre-schema procedural objects and actions
EXP-00008: ORACLE error 4063 encountered
ORA-04063: package body "DMSYS.DBMS_DM_MODEL_EXP" has errors
ORA-06508: PL/SQL: could not find program unit being called: "DMSYS.DBMS_DM_MODEL_EXP"
ORA-06512: at line 1
EXP-00083: The previous problem occurred when calling DMSYS.DBMS_DM_MODEL_EXP.schema_info_exp
用以下步骤重装Oracle Data Mining组件:
conn /as sysdba
@?/rdbms/admin/dminst.sql sysaux temp -组件包
@?/rdbms/admin/odmpatch.sql --补丁
@?/rdbms/admin/utlrp.sql --编译
--确认是否安装成功
select comp_name,version,status from dba_registry where comp_name='Oracle Data Mining';
但exp依然报同样的错误。
接着看:
SQL> select owner,count(*) from dba_objects where status= 'INVALID' group by owner;
no rows selected
SQL> select owner,object_name,status from dba_objects where object_name = 'DBMS_DM_MODEL_EXP';
OWNER OBJECT_NAME STATUS
-------------------- -------------------- -------
SYS DBMS_DM_MODEL_EXP VALID
SYS DBMS_DM_MODEL_EXP VALID
--没有DMSYS的DBMS_DM_MODEL_EXP包
#vi $ORACLE_HOME/rdbms/admin/dminst.sql
@@odmcrt.sql &&1 &&2
execute sys.dbms_registry.loading('ODM','Oracle Data Mining','validate_odm','SYS',NULL,NULL);
@@odmproc.sql
alter session set current_schema = "SYS";
Rem @@catodm.sql
execute sys.dbms_registry.loaded('ODM');
execute sys.validate_odm;
--怀疑脚本中用户搞错了,把SYS改成DMSYS再装了遍Oracle Data Mining组件
结果DMSYS下安装没成功:
SQL> select owner,object_name,status from dba_objects where object_name = 'DBMS_DM_MODEL_EXP';
OWNER OBJECT_NAME STATUS
-------------------- -------------------- -------
SYS DBMS_DM_MODEL_EXP VALID
SYS DBMS_DM_MODEL_EXP VALID
DMSYS DBMS_DM_MODEL_EXP INVALID
SQL> select comp_name,version,status from dba_registry where comp_name='Oracle Data Mining';
COMP_NAME
--------------------------------------------------------------------------------
VERSION STATUS
------------------------------ ----------------------
Oracle Data Mining
11.2.0.2.0 INVALID
--很无解。。。。
google了很多资料发现:
Oracle 10g中使用DMSYS来管理Oracle Data Mining,Oracle 11g中使用SYS管理;
这个可以从$ORACLE_HOME/rdbms/admin/dminst.sql脚本中可以看出差别:
--oracle 10g
@@odmcrt.sql &&1 &&2
execute sys.dbms_registry.loading('ODM','Oracle Data Mining','validate_odm','DMSYS',NULL,NULL);
@@odmproc.sql
alter session set current_schema = "DMSYS";
@@catodm.sql
execute sys.dbms_registry.loaded('ODM');
execute sys.validate_odm;
--最终解决:
drop user dmsys cascade;
将dminst.sql中的用户改回sys后重新执行:
conn /as sysdba
@?/rdbms/admin/dminst.sql sysaux temp -组件包
@?/rdbms/admin/odmpatch.sql --补丁
@?/rdbms/admin/utlrp.sql --编译
--安装成功
总结:可能是安装人员升级时忘了执行odmpatch.sql脚本,另外从开头的重装还是报错来看,很有可能是由于升级到11g后同时存在dmsys存在原因导致的。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/405242/viewspace-687463/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/405242/viewspace-687463/