数据库版本:oracle 10.2.0.4

操作系统:windows server2008

 

问题阐述:执行expdp导出命令时,报如下错误:

C:\>expdp "'"/ as sysdba"'"  directory=test dumpfile=out2.dmp logfile=out2.log schemas=xytest

 

Export: Release 10.2.0.4.0 – 64bit Production on 星期四, 01 6月, 2017 11:07:16

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Produc

tion

With the Partitioning, OLAP, Data Mining and Real Application Testing options

启动 "SYS"."SYS_EXPORT_SCHEMA_02":  '/******** AS SYSDBA' directory=test dumpfile=out2.dmp logfile=out2.log schemas=xytest

正在使用 BLOCKS 方法进行估计…

处理对象类型 SCHEMA_EXPORT/TABLE/TABLE_DATA

ORA-39125: 在 KUPW$WORKER.GET_TABLE_DATA_OBJECTS 中 Worker 发生意外的致命错误 (在调用 DBMS_METADATA.FETCH_XML_CLOB [] 时)

ORA-31642: 以下 SQL 语句失败:

BEGIN "DMSYS"."DBMS_DM_MODEL_EXP".SCHEMA_CALLOUT(:1,0,1,'10.02.00.04.00'); END;

ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 86

ORA-06512: 在 "SYS.DBMS_METADATA", line 907

ORA-04063: package body "DMSYS.DBMS_DM_IMP_INTERNAL" 有错误

ORA-06508: PL/SQL: 无法找到正在调用 : "DMSYS.DBMS_DM_IMP_INTERNAL" 的程序单元

ORA-06512: 在 "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: 在 "SYS.KUPW$WORKER", line 6307

 

—– PL/SQL Call Stack —–

  object      line  object

  handle    number  name

000007FFAC9365B8     15032  package body SYS.KUPW$WORKER

000007FFAC9365B8      6372  package body SYS.KUPW$WORKER

000007FFAC9365B8      9206  package body SYS.KUPW$WORKER

000007FFAC9365B8      1936  package body SYS.KUPW$WORKER

000007FFAC9365B8      6944  package body SYS.KUPW$WORKER

000007FFAC9365B8      1314  package body SYS.KUPW$WORKER

000007FFAABF9BE8         2  anonymous block

 

作业 "SYS"."SYS_EXPORT_SCHEMA_02" 因致命错误于 11:07:18 停止 

 

问题分析,检查导出语句,是否参数问题导致,在确认语句无误的情况下,按照提示 需分析 DMSYS.DBMS_DM_IMP_INTERNAL该对象是否存在问题 

 

SQL> select owner,object_type,status from dba_objects where object_name='DBMS_DM_IMP_INTERNAL';

 

OWNER                          OBJECT_TYPE         STATUS

—————————— ——————- ——-

DMSYS                          PACKAGE             VALID

DMSYS                          PACKAGE BODY        INVALID

 

发现DBMS_DM_IMP_INTERNAL 该 PACKAGE BODY的状态不对,可能缺失某成分

(PACKAGE与PACKAGE BODY是同时存在,如需调用某PACKAGE,只需在PACKAGE BODY中写入存储过程等)

 

 

查询具体的错误 select * from dba_errors where name='DBMS_DM_IMP_INTERNAL' ;

主要信息如下:

PLS-00201: 必须声明标识符 'DM$P_MODEL'

 

问题可能原因为 缺少该表 DMSYS.DM$P_MODEL ,经查询,该表为创建实例时,自动建立,不知为何丢失该表

 

查询是否存在该表 

SQL> select count(*) from DMSYS.DM$P_MODEL ;

select count(*) from DMSYS.DM$P_MODEL

                  *

ERROR at line 1:

ORA-00942: table or view does not exist

 

方案1:在其他10g服务器上找到该表的ddl语句,重建该表胡,重新编辑下package

 

建立表:

CREATE TABLE "DMSYS"."DM$P_MODEL" 

   ( "MOD#" NUMBER NOT NULL ENABLE, 

"OWNER#" NUMBER NOT NULL ENABLE, 

"NAME" VARCHAR2(25) NOT NULL ENABLE, 

"FUNCTION_NAME" VARCHAR2(30), 

"ALGORITHM_NAME" VARCHAR2(30), 

"CTIME" DATE, 

"BUILD_DURATION" NUMBER, 

"TARGET_ATTRIBUTE" VARCHAR2(30), 

"MODEL_SIZE" NUMBER, 

CONSTRAINT "DM$P_MODEL_UNIQUE" UNIQUE ("NAME", "OWNER#")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS 

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "SYSAUX"  ENABLE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "SYSAUX" 

 

重新编辑package 

alter package DMSYS.DBMS_DM_IMP_INTERNAL compile; 

 

执行完成后,再次尝试expdp导出

C:\>expdp "'"/ as sysdba"'"  directory=test dumpfile=out3.dmp logfile=out3.log schemas=xytest

作业 "SYS"."SYS_EXPORT_SCHEMA_03" 已于 11:20:20 成功完成

 

执行成功

 

方法2:删除dmsys用户,利用rdbms/admin/dminst.sql脚本重建系统用户

 

删除用户

drop user dmsys cascade ;

 

重建该用户:

@?rdbms/admin/dminst.sql 

执行该脚本后,没有成功创建dmsys用户,可能原因为该环境由9i版本升级而来,不存在该用户

 

查询后得知,可手工创建该用户,并赋权:

CREATE USER "DMSYS" PROFILE "DEFAULT" IDENTIFIED BY "DMSYS" PASSWORD EXPIRE DEFAULT TABLESPACE "SYSAUX" TEMPORARY TABLESPACE "TEMP" QUOTA 204800 K ON "SYSAUX" ACCOUNT LOCK;

GRANT ALTER SESSION TO "DMSYS"                       ;

GRANT ALTER SYSTEM TO "DMSYS"                        ;

GRANT CREATE JOB TO "DMSYS"                          ;

GRANT CREATE LIBRARY TO "DMSYS"                      ;

GRANT CREATE PROCEDURE TO "DMSYS"                    ;

GRANT CREATE PUBLIC SYNONYM TO "DMSYS"               ;

GRANT CREATE SEQUENCE TO "DMSYS"                   acheter viagra   ;

GRANT CREATE SESSION TO "DMSYS"                      ;

GRANT CREATE SYNONYM TO "DMSYS"                      ;

GRANT CREATE TABLE TO "DMSYS"                        ;

GRANT CREATE TRIGGER TO "DMSYS"                      ;

GRANT CREATE TYPE TO "DMSYS"                         ;

GRANT CREATE VIEW TO "DMSYS"                         ;

GRANT DROP PUBLIC SYNONYM TO "DMSYS"                 ;

GRANT QUERY REWRITE TO "DMSYS"                       ;

GRANT SELECT ON "SYS"."DBA_JOBS_RUNNING" TO "DMSYS"  ;

GRANT SELECT ON "SYS"."DBA_REGISTRY" TO "DMSYS"      ;

GRANT SELECT ON "SYS"."DBA_SYS_PRIVS" TO "DMSYS"     ;

GRANT SELECT ON "SYS"."DBA_TAB_PRIVS" TO "DMSYS"     ;

GRANT SELECT ON "SYS"."DBA_TEMP_FILES" TO "DMSYS"    ;

GRANT EXECUTE ON "SYS"."DBMS_LOCK" TO "DMSYS"        ;

GRANT EXECUTE ON "SYS"."DBMS_REGISTRY" TO "DMSYS"    ;

GRANT EXECUTE ON "SYS"."DBMS_SYSTEM" TO "DMSYS"      ;

GRANT EXECUTE ON "SYS"."DBMS_SYS_ERROR" TO "DMSYS"   ;

GRANT DELETE ON "SYS"."EXPDEPACT$" TO "DMSYS"        ;

GRANT INSERT ON "SYS"."EXPDEPACT$" TO "DMSYS"        ;

GRANT SELECT ON "SYS"."EXPDEPACT$" TO "DMSYS"        ;

GRANT UPDATE ON "SYS"."EXPDEPACT$" TO "DMSYS"        ;

GRANT SELECT ON "SYS"."V_$PARAMETER" TO "DMSYS"      ;

GRANT SELECT ON "SYS"."V_$SESSION" TO "DMSYS"        ;

 

 

创建完用户后尝试执行expdp,还是报一样的错误,查询该表状态

SQL> select owner,object_type,status from dba_objects where object_name='DBMS_DM_IMP_INTERNAL';

 

OWNER                          OBJECT_TYPE         STATUS

—————————— ——————- ——-

SYS                            PACKAGE             VALID

SYS                            PACKAGE BODY        VALID

 

再查询是否有该表错误

select * from dba_errors where name='DBMS_DM_IMP_INTERNAL' ;

没有选定行 

 

猜测可能为导出时没有调用

解决:创建一个同义词指向sys用户的这个包

create synonym DMSYS.DBMS_DM_MODEL_EXP for SYS.DBMS_DM_MODEL_EXP;

 

再次执行导出,正常




Related posts

coded by nessus
分享:  DeliciousGReader鲜果豆瓣CSDN网摘
Trackback

no comment untill now

Add your comment now

无觅相关文章插件