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;
|