Invalid Db Objects
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???
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)?
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
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.
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.
Run this script from sqlplus.
Spool the file
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')
execute immediate 'ALTER PACKAGE '||i.owner||'.'||i.OBJECT_NAME||' COMPILE BODY';
execute immediate 'ALTER PACKAGE '||i.owner||'.'||i.OBJECT_NAME||' COMPILE';
U can add more object type in the in clause...
Please let me know if this workd for as it did for me...
Click Here to Expand Forum to Full Width