ORA-04063: view "SYS.KU$_XMLSCHEMA_VIEW" has error

1 使用exp导出数据，发现报错

EXP导出报错:

exporting cluster definitions

EXP-00056: ORACLE error 4063 encountered

ORA-04063: view "SYS.KU$_XMLSCHEMA_VIEW" has errors

EXP-00000: Export terminated unsuccessfully

2 解决方法：

1 使用 rman 做数据库全备

一致性备份 （ 适用于 NOARCHIVELOG 和 ARCHIVELOG 模式 ）

  c:\rman target sys/oracle@TEST nocatalog

  RMAN>shutdown immediate

  RMAN>startup mount

  RMAN>backup database; 如果备份到别的目录 backup database format ='d:\backup\%d_%s.dbf' ;

  RMAN>sql 'alter system archive log current '

2 查看组件信息

col comp_id for a15

col version for a15

col comp_name for a30

SQL> select comp_id,comp_name,version,status from dba_registry;

3 查看用户对象信息

SELECT status, object_id, object_type, owner||'.'||object_name     "OWNER.OBJECT" FROM dba_objects WHERE owner='XDB' AND status != 'VALID'    ORDER BY 4,2;

4 查看当前系统 XDB 默认的表空间及用户的密码

    select username,password,default_tablespace,temporary_tablespace from dba_users where username like '%XDB%';

5 检查环境变量 ：

Before installing or upgrading XDB,make sure the LD_LIBRARY_PATH / LIBPATH / SHLIB_PATH environment variable isset correctly.  

That is, the first directory referenced should be $ORACLE_HOME/lib.  This environment variable is used to resolve thelocation of the shared library "libxdb.so".

6 确保 XDB 有执行 execute permissionson the DBMS_LOB and UTL_FILE  packages 的权限

XDB must have execute permissionson the DBMS_LOB and UTL_FILE  packages.  XDB automatically has theseprivileges because they are granted to PUBLIC by default.  

If theseprivileges have been revoked from PUBLIC for security reasons, errors will be reportedduring the installation / upgrade of XDB and many XDB objects will becomeinvalid,

making the component itself invalid.  Therefore, grant executeprivileges on these packages back to PUBLIC before installing / upgrading XDB

or if you do not wish for PUBLIC to have these permissions, after the install /upgrade, grant execute permissions on these packages directly to XDB

and run$ORACLE_HOME/rdbms/admin/utlrp.sql to recompile the invalid objects.

SQL> DESC DBMS_LOB

SQL> DESC UTL_FILE

SQL> GRANT EXECUTE ON DBMS_LOB TO XDB;

SQL> GRANT EXECUTE ON UTL_FILE TO XDB;

7 检查以下几项

7.1 对 Oracle10.2 来说， XDK 组件是需要的

7.2 XDB 表空间最少要 200M

7.3 确保 SHARED_POOL_SIZE 和  JAVA_POOL_SIZE 至少

(4)    Prior to Oracle 10.2, a validinstallation of XDK is also required

(5)    Allocate at least 200 MB forthe XDB repository tablespace datafile.

(6)    Ensure that the SHARED_POOL_SIZE and JAVA_POOL_SIZE is set to at least 150 MB.

5 关闭数据库

6 启动数据库删除 XDB

SQL> startup

SQL> spool xdb_removal521.log

SQL> set echo on;

SQL> @?/rdbms/admin/catnoqm.sql

SQL> spool off;

7 检查 XDB 用户的信息是否删除干净

8 关闭数据库

9 启动数据库重新安装 XDB

SQL> startup;

SQL> spool xdb_install521.log

SQL> set echo on;

SQL> @?/rdbms/admin/catqm.sql <XDB pwd> <XDB default tbs><XDB temporary tbs>

SQL> @?/rdbms/admin/catxdbj.sql

SQL> @?/rdbms/admin/utlrp.sql

SQL> spool off

10 查询 XDB 组件及用户对象是否有无效对象。

SQL> col comp_id for a15

SQL> col version for a15

SQL> col comp_name for a30

SQL> select comp_id,comp_name,version,status from dba_registry;

SELECT status, object_id, object_type, owner||'.'||object_name     "OWNER.OBJECT" FROM dba_objects WHERE owner='XDB' AND status != 'VALID'    ORDER BY 4,2;

11 ．  Reload ORDIM 组件

在前面提到 Oracle Multimedia （ ORDIM ）组件需要使用 XDB 组件，在我们重建 XDB 组件之后需要 Reload 一些 ORDIM 组件。

Oracle 10g 版本

The XDB schemais also used by other products like interMedia and Spatial to register theirXML Schemas. When XDB has been deinstalled and reinstalled for whatever reasonthe XML Schemas for these products will have to be reinstalled as well.

--XDB Schema 被其他的组件所用 ， 比如 interMedia （ 11g 中叫 Multimedia ） 和 Spatial 组件 ， 他们注册自己的信息在 XML Schemas 中。   当 XDB 被删除或者重建时，其他组件在 XML 中的信息也被卸载，这就导致其他组件的无效。

-- Register XML Schemas for the Rules Manager component (part of CATPROC)

-- 在 XMLSchemas 中注册 Rules Manager 组件信息 ：

  connect / as sysdba

  alter session set current_schema  = EXFSYS;   

  @?/rdbms/admin/rulpbs.sql

-- Register XML Schemas for the interMedia product

-- 在 XMLSchemas 中注册 interMedia 组件信息 ：

 connect / as sysdba  

  alter session set current_schema=ORDSYS;  

  @?/ord/im/admin/imxreg.sql

 -- Register XML Schemas for the Locator/Spatial product

-- 在 XMLSchemas 中注册 Locator/Spatial 组件信息

 connect / as sysdba  

  alter session set current_schema=MDSYS;  

  @?/md/admin/sdogmlsc.sql

  @?/md/admin/sdoepsgx.sql

  @?/md/admin/sdogrxml.sql  -- not to be run for a Locatorinstallation

12 重建 XDB 组件后，还有无效的 XDB 对象

解决方法有两种：

（ 1 ）方法一

SQL> CONN / AS SYSDBA (Connect asSYSDBA)

SQL> DESC DBMS_LOB

SQL> DESC UTL_FILE

SQL> GRANT EXECUTE ON DBMS_LOB TO XDB;

SQL> GRANT EXECUTE ON UTL_FILE TO XDB;

SQL> @?/rdbms/admin/utlrp.sql  -- Run this afew times

SQL> SELECT * FROM DBA_ERRORS;

SQL> SELECT COMP_ID, COMP_NAME, STATUS FROM DBA_REGISTRY;

SQL> SELECT OBJECT_NAME, OBJECT_TYPE, OWNER, STATUS FROM DBA_OBJECTS WHEREOWNER = 'XDB' AND STATUS != 'VALID';

（ 2 ） 方法二 ：

a) Grant following privileges to XDBuser

SQL> GRANTEXECUTE ON DBMS_LOB TO XDB;

SQL> GRANT EXECUTE ON UTL_FILE TO XDB;

b) Reload the XDB Component (usingxdbrelod.sql).

       - Refer Master Note for Oracle XMLDatabase (XDB) Installation (Doc ID 1292089.1)

13 EXP 导出验证