[EXP-00056]全部数据库导出终止

使用Oracle导出工具exp导出全部数据时错误，如果使用单张表导出正常。
[oracle@oracle02 ~]$ exp userid=test/test file=test.dmp buffer=1024 owner=test

Export: Release 10.2.0.1.0 - Production on Tue Nov 28 15:09:42 2006

Copyright (c) 1982, 2005, Oracle. All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user TEST
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user TEST
About to export TEST's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 932 encountered
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
EXP-00000: Export terminated unsuccessfully
原因：数据字典中有存在一些无效KU$_视图；
SQL> select * from sys.ku$_xmlschema_view;
select * from sys.ku$_xmlschema_view
*
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected BLOB, CLOB got CHAR
解决办法：
1.执行对象视图定义脚本 $ORACLE_HOME/rdbms/admin/catmetx.sql
SQL> @?/rdbms/admin/catmetx.sql

Package altered.
Index altered.
View created.
Grant succeeded.
View created.
Grant succeeded.
Procedure created.
System altered.
System altered.
System altered.
PL/SQL procedure successfully completed.
Procedure dropped.

2.执行重新编译无效对象脚本 $ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> @?/rdbms/admin/utlrp.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
验证一下程序
SQL> select * from ku$_xmlschema_view;

V V OWNER_NUM OWNER_NAME URL
- - ---------- ------------------------------ ---------------
1 0 46 MDSYS http://www.opengis.net/gml/geometry.xsd
1 0 46 MDSYS http://www.w3.org/1999/xlink/xlinks.xsd
1 0 46 MDSYS http://www.opengis.net/gml/feature.xsd
重新执行导出程序exp，一些正常。

inbound connection timed out (ORA-3136)

早上发现数据库出现了一条警告信息WARNING: inbound connection timed out (ORA-3136)；查了一下Metalink关于ORA-3136错误代码，是Oracle Bug 4677000 WARNING: INBOUND CONNECTION TIMED OUT (ORA-3136)。
解决办法：
1.在$ORACLE_HOME/network/admin/listener.ora, 增加下面代码：INBOUND_CONNECT_TIMEOUT_LISTENER = 0

2.在$ORACLE_HOME/network/admin/sqlnet.ora,增加下面代码:
SQLNET.INBOUND_CONNECT_TIMEOUT = 0

3. 重新启动监听器和数据库使增加的代码生效。

ORA-20000错误

平常有个习惯，一上班，会泡杯咖啡，然后去查看数据库的alert_sid.ora文件。今天早上即如往常打开生产数据库，只是一些日志切换记录。已经好几天没有去查看客服系统的alert_sid.ora文件了（不是我不敬业，而是我是业余的DBA，呵呵），惊奇的发现，警告文件里面多了很多，像下面的信息
ORA-12012: error on auto execute of job 8888
ORA-20000: ORA-20000: Content of the tablespace specified is not permanent or tablespace name is invalid
ORA-06512: at "SYS.PRVT_ADVISOR", line 1624
ORA-06512: at "SYS.DBMS_ADVISOR", line 186
ORA-06512: at "SYS.DBMS_SPACE", line 1338
ORA-06512: at "SYS.DBMS_SPACE", line 1554
客服系统运行在RedHat AS4，Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
首先想到一定是JOB出了问题，于是查看一下JOB信息
SQL> select job_name,state,run_count,failure_count from dba_scheduler_jobs;

JOB_NAME STATE RUN_COUNT FAILURE_COUNT
------------------------------ --------------- ---------- -------------
AUTO_SPACE_ADVISOR_JOB SCHEDULED 19 1
GATHER_STATS_JOB SCHEDULED 19 0
FGR$AUTOPURGE_JOB DISABLED 0 0
PURGE_LOG SCHEDULED 22 0
RLM$SCHDNEGACTION SCHEDULED 546 0
RLM$EVTCLEANUP SCHEDULED 525 0

6 rows selected.
从上面的查询结果不难看出是由于AUTO_SPACE_ADVISOR_JOB出了问题。
Oracle 号称10G A self Managed Database ，AUTO_SPACE_ADVISOR_JOB 是在数据库安装时预定义的一个Scheduler Job。
原因：
查看Metalink它说是Oracle bug,when the tablespace is created the statistics are captured for this tablespace.When the tablespace is dropped the segments for which the segstats are collected continue to reference the dropped tablespace.AUTO_SPACE_ADVISOR_JOB找不到表空间返回的错误。通过查询dba_auto_segadv_ctl表可以获得被删除的表空间
SQL> select tablespace_name from dba_auto_segadv_ctl
2 where tablespace_name not in (select tablespace_name from dba_tablespaces)
3 /
TABLESPACE_NAME
------------------------------
TEST
1 rows selected.
由于TEST表空间已经被删除，在运行AUTO_SPACE_ADVISOR_JOB时还会对它进行段建议。导致了ORA-20000错误发生。Internal BUG:4707226
解决方法：
Bug 4707226 will be fixed in release 11.0
可以通过手动删除DBA_AUTO_SEGADV_CTL里表空间记录。
SQL> DELETE FROM dba_auto_segadv_ctl
2 where tablespace_name not in (select tablespace_name from dba_tablespaces)
3 /
1 rows deleted.
验证一下：
SQL> exec dbms_space.auto_space_advisor_job_proc

PL/SQL procedure successfully completed.