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.
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.
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?
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.
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...