ORA-00904: "DROP_SEGMENTS": invalid identifier 解决方法

之前按用户imp了一个9i的数据库，今天发现Job执行失败，alertlog显示：

ORA-00904:"DROP_SEGMENTS":invalididentifier

上网搜了一下，摘抄如下，说是要执行下catpatch.sql脚本。

Oracle9.2.0.5addedtheDROP_SEGMENTScolumntotheDBA_TAB_MODIFICATIONSview.Internally,thedollartablesys.mon_mods$receivedthenewdrop_segmentscolumn.Guesswho'sresponsibleforaddingthisnewcolumnduringthepatchprocess?

Yup,it'scatpatch.sql.Oh,joy!

Ifyoudon'truncatpatchwhenpatchingto9.2.0.5,afewthingswillhappenwithregardstothedrop_segmentscolumn:

SMONwillcomplainprofuselyinalert.logwheneverittriestoupdatesys.mon_mods$.Thatmeansyougetthiserrormessageevery15minutes,orsooner:

ThuNov2517:21:052004

Errorsinfile/u01/app/oracle/admin/mydb/bdump/mydb_smon_8201.trc:

ORA-00904:"DROP_SEGMENTS":invalididentifier

DBMS_STATS.FLUSH_DATABASE_MONITORING_INFOwillfail.

AccordingtoMetalinkNote285315.1,you'llalsogettheORA-00904errorinthealert.logonDROPTABLEandTRUNCATETABLE.

YouwillgettheORA-00904errorwhenyoutrytoupdatestatisticswith"GATHERSTALE".

Ifyoucan'tupdatestatistics,youwillhavebadoptimizerplans.

解决方法如下：

1.shutdowndatabase

2.startupmigrate

3.@?/rdbms/admin/catpatch.sql--升级数据字典

4.@?/rdbms/admin/catalog.sql--创建系统常用的数据字典视图和同义词

5.@?/rdbms/admin/utlrp.sql--编译存储过程

6.重启数据库

SQL>describesys.mon_mods$;

NameNull?Type

-------------------------------------

OBJ#NUMBER

INSERTSNUMBER

UPDATESNUMBER

DELETESNUMBER

TIMESTAMPDATE

FLAGSNUMBER

DROP_SEGMENTSNUMBER<==出现了该字段

<!--EndFragment-->