ORA-00904: "drop_segments": Invalid identifier Solution

 

 

 

Previously, a 9i database was created based on the user imp. Today, the execution of the job failed. The alert log shows:

ORA-00904: "drop_segments": Invalid identifier

 

 

 

I searched the internet and excerpted the following excerpt to execute the catpatch. SQL script.

 

 

 

Oracle 9.2.0.5 added the drop_segments column to the dba_tab_modifications view. internally, the dollar table sys. mon_mod $ received the new drop_segments column. guess who's responsible for adding this new column during the patch process?

Yup, it's catpatch. SQL. Oh, joy!

 

If you don't run catpatch when patching to 9.2.0.5, a few things will happen with regards to the drop_segments column:

SMON will complain profusely in alert. Log whenever it tries to update SYS. mon_mod $. That means you get this error message every 15 minutes, or sooner:

 

 

Thu Nov 25 17:21:05 2004

Errors in file/u01/APP/Oracle/admin/mydb/bdump/mydb_smon_8201.trc:

ORA-00904: "drop_segments": Invalid identifier

 

 

Dbms_stats.flush_database_monitoring_info will fail.

According to Metalink note 285315.1, you'll also get the ORA-00904 error in the alert. Log On drop table and truncate table.

You will get the ORA-00904 error when you try to update statistics with "Gather stale ".

If you can't update statistics, you will have bad optimizer plans.

 

 

 

The solution is as follows:

1. Shutdown Database

2. startup migrate

3 .@? /Rdbms/admin/catpatch. SQL -- upgrade data dictionary

4 .@? /Rdbms/admin/CATALOG. SQL -- create common data dictionary views and synonyms of the system

5 .@? /Rdbms/admin/utlrp. SQL -- compile the Stored Procedure

6. Restart the database

 

 

 

SQL> describe SYS. mon_mod $;

Name null? Type

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

OBJ # Number

Inserts number

Updates number

Deletes number

Timestamp date

Flags number

Drop_segments number <= This field is displayed