CATPROC Component Is Invalid and Will Not Validate

刘泽宇

SYMPTOMS
CATPROC is invalid in the registry and will not validate even after running catproc.sql or utlrp.sql

CAUSE
This can be caused by performing and upgrade on a database with invalid objects at the start of the upgrade.

SOLUTION
Execute the following script repeatedly while connected as SYS AS SYSDBA and perform the indicated operation until it returns 'CATPROC can be validated now'.

REM ***************
REM CHECKVALID.SQL
REM ***************
set serveroutput on;
declare
start_time date;
end_time date;
object_name varchar(100);
object_id char(10);
begin
SELECT date_loading, date_loaded into start_time, end_time FROM registry$ WHERE
cid = 'CATPROC';   
SELECT obj#,name into object_id,object_name
      FROM obj$
      WHERE status > 1 AND
            (ctime BETWEEN start_time AND end_time OR
   mtime BETWEEN start_time AND end_time OR
             stime BETWEEN start_time AND end_time) AND
             ROWNUM <=1;                          
dbms_output.put_line('Please compile Invalid object '||object_name||'  
Object_id '||object_id );
EXCEPTION
  WHEN NO_DATA_FOUND THEN
     dbms_output.put_line('CATPROC can be validated now' );         
end;
/

Once all of the dependent objects have been recompiled, validate CATPROC by executing the following command:

execute DBMS_REGISTRY_SYS.VALIDATE_CATPROC;