认真就输

下面是一个朋友的数据库，通过exp全库导出的时候报下面的错误

1，版本与平台介绍

d:wendangSkyDrivers2sql>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 - Production

2，错误信息

这里看到遇到视图不存在。

3，处理过程

3.1 10046trace过程编译过程

SQL> oradebug setmypid
已处理的语句
SQL> oradebug event 10046 trace name context forever,level 12;
已处理的语句
SQL> oradebug tracefile_name;
D:APPLUOPINGdiagrdbmsorclorcltraceorcl_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
  2  /

授权成功。

刚运行就开始报了一些错误。不过这里报错都很简单，就是一些索引，视图不存在。

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 故障处理后结果