The first thing to do is to recompile all of your invalid objects. Sign on to the database as SYS or SYSTEM and run the ORACLE_HOME/rdbms/admin/utlrp.sql script. This will recompile all objects that can be recompiled successfully.
There may be some objects that will not compile. For instance, the stored procedure may refer to a table that has been dropped. The best way to find out why the stored procedure will not compile correctly is to figure out which object is invalid with the following query in SQL*Plus:
SELECT owner,object_name,object_type FROM dba_objects WHERE status='INVALID';
Next, sign on to the schema that owns the stored procedure. Then issue the following:
ALTER PROCEDURE procedure_name COMPILE;
You will get an error saying that the procedure is not valid. Then type "SHOW ERRORS." This should give you an indication of what the real problem is.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.