Printable View
Hello Gurus,
I have many invalid objects that belongs to system and sys. I could not recompile them as some says insufficient privilege and other says other objects are missing. I want to recompile them to enable me to proceed with my work. How can I do this. I am afraid to execute the catalog.sql as it may ruin my existing DB.
Can you please advise me. I have attached a list of invalid objects in this post.
no worries is running catalog and catproc just make sure no users are connected to make sure.
Its pefectly safe as long as your db is up and running normally.
Have you recently done a migration or something?
log in as SYS and run $ORACLE_HOME\rdbms\admin\utlrp.sql
I tried executing the catalog.sql and I ended up with a corrupted database. I ended up deleting the DB and recreating it. We cannot take that risk now as the DB is used by many users and is critical to our projects.
You must have used utlrp.sql to compile objects...how did you got all these objects invalid???
Hello Gurus,
I have executed catproc, catrep, and utlrp to no avail. The same number and set of objects are still invalid. I am afraid to execute the catalog.sql as I had experience with it crashing an instance I have made. I eventually ended with recreating the DB.
Can you please teach me a work around on this problem please?
what types of objects are invalid & try finding root cause.
have u done any migration (from 8.x to 9.x)?
Abhay.
Hello Gurus,
We did not migrate from 8.x to 9.x. We have been trying to setup replication in this database instance. Finding the root cause is one that I would like to know how as this will lead me to being able to compile successfully.
Hello Gurus,
We have executed the catalog.sql, catproc.sql,and utlrp.sql but we still have many system invalid objects. Can you please help us recompile them successfully. Attached is the list of system objects that are invalid and a sample compilation output.
Thank you for your help.
hi,
Run this script from sqlplus.
Spool the file
declare
begin
for i in (select owner,OBJECT_NAME,OBJECT_TYPE from dba_objects where OBJECT_TYPE in ('PACKAGE','PACKAGE BODY') and status='INVALID') loop
if(i.OBJECT_TYPE = 'PACKAGE BODY')
then
execute immediate 'ALTER PACKAGE '||i.owner||'.'||i.OBJECT_NAME||' COMPILE BODY';
else
execute immediate 'ALTER PACKAGE '||i.owner||'.'||i.OBJECT_NAME||' COMPILE';
end if;
dbms_output.put_line(i.OBJECT_TYPE||i.object_name||'COMPILED');
end loop;
end;
/
U can add more object type in the in clause...
Please let me know if this workd for as it did for me...
regards
saurabh