案例：Oracle：EXP-00056 ORA-04063处理过程
下面是一个朋友的数据库，通过exp全库导出的时候报下面的错误
1，版本与平台介绍
d:\wendang\SkyDrive\rs2\sql>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期一 8月 18 15:19:36 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> host uname -a
windows32 luoping 2.6.1 7601 i686-pc Intel unknown MinGW
SQL> select * from v$version where rownum<3;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
PL/SQL Release 11.2.0.3.0 – Production2，错误信息
d:\wendang\SkyDrive\rs2\sql>exp system/oracle full=y file=e:\full.dmp indexes=n constraints=n
Export: Release 11.2.0.3.0 – Production on 星期一 8月 18 12:54:19 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 将不导出表的索引
注: 将不导出表的约束条件
即将导出整个数据库…
. 正在导出表空间定义
. 正在导出概要文件
. 正在导出用户定义
. 正在导出角色
. 正在导出资源成本
. 正在导出回退段定义
. 正在导出数据库链接
. 正在导出序号
. 正在导出目录别名
. 正在导出上下文名称空间
. 正在导出外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出对象类型定义
. 正在导出系统过程对象和操作
. 正在导出 pre-schema 过程对象和操作
. 正在导出簇定义
EXP-00056: 遇到 ORACLE 错误 4063
ORA-04063: view "SYS.KU$_XMLSCHEMA_VIEW" 有错误
EXP-00056: 遇到 ORACLE 错误 4063
ORA-04063: view "SYS.KU$_XMLSCHEMA_VIEW" 有错误
EXP-00000: 导出终止失败这里看到遇到视图不存在。
3，处理过程
3.1 10046trace过程编译过程
SQL> oradebug setmypid已处理的语句
SQL> oradebug event 10046 trace name context forever,level 12;已处理的语句
SQL> oradebug tracefile_name;
D:\APP\LUOPING\diag\rdbms\orcl\orcl\trace\orcl_ora_5824.trc
SQL> alter VIEW SYS.KU$_XMLSCHEMA_VIEW compile;警告: 更改的视图带有编译错误。
3.2 查看无效对象
MEDADM RC PROCEDURE INVALID Invalid Obj
PUBLIC APEX_ADMIN SYNONYM INVALID Invalid Obj
PUBLIC EQUALS_PATH SYNONYM INVALID Invalid Obj
PUBLIC F SYNONYM INVALID Invalid Obj
OBJECT_OWNER OBJECT_NAME TYPE STATUS PROB
————— —————————————- ——————- ——- ———–
PUBLIC HTMLDB_ADMIN SYNONYM INVALID Invalid Obj
PUBLIC PATH_VIEW SYNONYM INVALID Invalid Obj
PUBLIC RESOURCE_VIEW SYNONYM INVALID Invalid Obj
PUBLIC UNDER_PATH SYNONYM INVALID Invalid Obj
XDB XDBHI_IDXTYP INDEXTYPE INVALID Invalid Obj
XDB EQUALS_PATH OPERATOR INVALID Invalid Obj
XDB UNDER_PATH OPERATOR INVALID Invalid Obj
XDB DBMS_RESCONFIG PACKAGE BODY INVALID Invalid Obj
XDB XDB_PV_TRIG TRIGGER INVALID Invalid Obj
XDB XDB_RV_TRIG TRIGGER INVALID Invalid Obj
XDB PATH_VIEW VIEW INVALID Invalid Obj
XDB RESOURCE_VIEW VIEW INVALID Invalid Obj
XDB DBMS_XMLDOM_ICD PACKAGE VALID Miss Pkg Body
XDB XDB_PVTRIG_PKG PACKAGE VALID Miss Pkg Body这里可以看到xdb有很多无效的对象，估计是xdb的问题。
3.3手动重新运行xdb一些视图的创建
Rem NAME
Rem catmetx.sql – Metadata API: Real definitions for XDB object views.
SQL> @?/rdbms/admin/catmetx.sql
SQL> alter package xdb.xdb_funcimpl compile;程序包已变更。
SQL> alter index xdb.xdbhi_idx rebuild;
alter index xdb.xdbhi_idx rebuild
*
第 1 行出现错误:
ORA-01418: 指定的索引不存在
SQL>
SQL> — view for xmlschemas
SQL> — this view is used for direct use of MDAPI; not for datapump
SQL> create or replace force view sys.ku$_xmlschema_view of sys.ku$_xmlschema_t
2 with object identifier (schemaoid) as
3 select ‘1’,’0′,
4 u.user#, u.name, x.schema_url, x.schema_id,
5 (case when x.local=’YES’ then 1 else 0 end
6 + case when x.binary=’YES’ then 2 else 0 end),
7 xlvl.lvl,
8 value(s).getClobVal(),
9 xdb.dbms_xdbutil_int.XMLSchemaStripUsername(XMLTYPE(
10 value(s).getClobVal()),
11 u.name) — stripped
12 from sys.user$ u, sys.dba_xml_schemas x, xdb.xdb$schema s,
13 sys.dba_xmlschema_level_view xlvl
14 where x.owner=u.name and xlvl.schema_oid = x.schema_id and
15 s.sys_nc_oid$ = x.schema_id and
16 (SYS_CONTEXT(‘USERENV’,’CURRENT_USERID’) IN (u.user#, 0) OR
17 EXISTS ( SELECT * FROM session_roles
18 WHERE role=’SELECT_CATALOG_ROLE’ ))
19 /警告: 创建的视图带有编译错误。
SQL> grant select on sys.ku$_xmlschema_view to public
2 /
grant select on sys.ku$_xmlschema_view to public
*
第 1 行出现错误:
ORA-04063: view "SYS.KU$_XMLSCHEMA_VIEW" 有错误
SQL> — ku$_table_xmlschema_view is used to find the xmlschemas directly referenced
SQL> — for xmltype columns/tables and dependent schemas referenced.
SQL> — elclude hidden xmlschemas (32768 set in xdb$schema flags)
SQL> create or replace view ku$_table_xmlschema_view as
2 select opq.obj# tabobj_num, opq.schemaoid schemaoid, opq.schemaoid par_oid
3 from sys.opqtype$ opq
4 UNION
5 select opq.obj# tabobj_num, sd.dep_schema_oid schemaoid, opq.schemaoid par_oid
6 from sys.opqtype$ opq, dba_xml_schema_dependency sd
7 start with
8 sd.schema_oid=opq.schemaoid and opq.type=1 and opq.schemaoid is not null
9 connect by nocycle
10 prior sd.dep_schema_oid=sd.schema_oid and
11 prior opq.schemaoid=opq.schemaoid and opq.type=1
12 /视图已创建。
SQL> grant select on ku$_table_xmlschema_view to select_catalog_role授权成功。
刚运行就开始报了一些错误。不过这里报错都很简单，就是一些索引，视图不存在。
3.4 手动创建视图与索引
SQL> CREATE INDEX XDB.XDBHI_IDX ON XDB.XDB$RESOURCE
2 (SYS_MAKEXML(‘8758D485E6004793E034080020B242C6’,734,"XMLEXTRA","XMLDATA"))
3 INDEXTYPE IS XDB.XDBHI_IDXTYP
4 NOPARALLEL;
索引已创建。
SQL> CREATE OR REPLACE FORCE VIEW SYS.DBA_XMLSCHEMA_LEVEL_VIEW_DUP
2 (
3 SCHEMA_URL,
4 SCHEMA_OWNER,
5 SCHEMA_OID,
6 LVL,
7 IN_CYCLE
8 )
9 AS
10 SELECT schema_url,
11 schema_owner,
12 schema_oid,
13 MAX (LEVEL) + 1,
14 CONNECT_BY_ISCYCLE
15 FROM DBA_XML_SCHEMA_DEPENDENCY
16 CONNECT BY NOCYCLE PRIOR schema_url = dep_schema_url
17 AND PRIOR schema_owner = dep_schema_owner
18 AND PRIOR schema_oid = dep_schema_oid
19 GROUP BY schema_url,
20 schema_owner,
21 schema_oid,
22 CONNECT_BY_ISCYCLE
23 UNION
24 SELECT x.xmldata.schema_url,
25 x.xmldata.schema_owner,
26 x.sys_nc_oid$,
27 1,
28 0
29 FROM xdb.xdb$schema x
30 WHERE x.xmldata.includes IS NULL AND x.xmldata.imports IS NULL;
视图已创建。
SQL> GRANT SELECT ON SYS.DBA_XMLSCHEMA_LEVEL_VIEW_DUP TO SELECT_CATALOG_ROLE;
授权成功。
SQL> CREATE OR REPLACE FORCE VIEW SYS.DBA_XMLSCHEMA_LEVEL_VIEW
2 (
3 SCHEMA_URL,
4 SCHEMA_OWNER,
5 SCHEMA_OID,
6 LVL
7 )
8 AS
9 SELECT l.schema_url,
10 l.schema_owner,
11 l.schema_oid,
12 l.lvl
13 FROM DBA_XMLSCHEMA_LEVEL_VIEW_DUP l
14 WHERE NOT EXISTS
15 (SELECT 1
16 FROM DBA_XMLSCHEMA_LEVEL_VIEW_DUP
17 WHERE schema_url = l.schema_url
18 AND schema_owner = l.schema_owner
19 AND schema_oid = l.schema_oid
20 AND in_cycle = 1)
21 UNION
22 SELECT l.schema_url,
23 l.schema_owner,
24 l.schema_oid,
25 0
26 FROM DBA_XMLSCHEMA_LEVEL_VIEW_DUP l
27 WHERE EXISTS
28 (SELECT 1
29 FROM DBA_XMLSCHEMA_LEVEL_VIEW_DUP
30 WHERE schema_url = l.schema_url
31 AND schema_owner = l.schema_owner
32 AND schema_oid = l.schema_oid
33 AND in_cycle = 1);
视图已创建。
SQL> grant select on sys.ku$_xmlschema_view to public
2 ;
授权成功。
SQL> CREATE OR REPLACE PUBLIC SYNONYM DBA_XMLSCHEMA_LEVEL_VIEW FOR SYS.DBA_XMLSCHEMA_LEVEL_VIEW;
同义词已创建。
SQL> GRANT SELECT ON SYS.DBA_XMLSCHEMA_LEVEL_VIEW TO PUBLIC;
授权成功。再次运行catmetx.sql脚本，无任何报错
4 故障处理后结果
d:\wendang\SkyDrive\rs2\sql>exp system/oracle full=y file=e:\full.dmp indexes=n constraints=nExport: Release 11.2.0.3.0 – Production on 星期一 8月 18 14:03:15 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
连接到: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
已导出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集
注: 将不导出表的索引
注: 将不导出表的约束条件
即将导出整个数据库…
. 正在导出表空间定义
. 正在导出概要文件
. 正在导出用户定义
. 正在导出角色
. 正在导出资源成本
. 正在导出回退段定义
. 正在导出数据库链接
. 正在导出序号
. 正在导出目录别名
. 正在导出上下文名称空间
. 正在导出外部函数库名
. 导出 PUBLIC 类型同义词
. 正在导出专用类型同义词
. 正在导出对象类型定义
. 正在导出系统过程对象和操作
. 正在导出 pre-schema 过程对象和操作
. 正在导出簇定义
. 即将导出 SYSTEM 的表通过常规路径…
没有报错了
这里已经看到开始导system用户的表，没有再出现之前的故障。
------------------作者介绍-----------------------
姓名：黄廷忠
现就职：Oracle中国高级服务团队
曾就职：OceanBase、云和恩墨、东方龙马等
