In Oracle databases, if we change or alter the structure of objects such as tables, the status of dependent objects such as procedures, packages, functions, views, and triggers becomes invalid. So in order to compile all the invalid dependent objects at once, you can run this SQL script. Note: Save the script below as *.sql and execute it from SQL*Plus. [* is any file name]
set heading off set feedback off spool x.dat select 'alter ' || decode(object_type,'PACKAGE BODY','PACKAGE',object_type) || ' ' || object_name || ' compile ' || decode(object_type,'PACKAGE BODY',' body;',';') from user_objects where object_type in ('FUNCTION','PACKAGE','PACKAGE BODY','PROCEDURE','TRIGGER','VIEW') and status = 'INVALID' order by object_type , object_name; spool off set heading on set feedback on @x.dat
Kevin Y. writes: This script is what we are using to deal with invalid objects as well. Sometimes you have to run it 2, 3, or 4 times because an object will become valid after the compile only when all objects it depended on are 'valid,' -- otherwise the compile will fail.
Jeff Z. writes: With Oracle 8i, there is a supplied pl/sql procedure to recompile all invalid objects in a particular schema. There were bugs in the package for Oracle 8.1.6 and earlier versions, but at least with 8.1.7 it works quite well. The procedure is in DBMS_UTILITY and the procedure is compile_schema. To compile all the invalid objects in a schema issue the command:
SQL> execute DBMS_UTILITY.compile_schema ('SCOTT', FALSE);
Yes, I realize that this is only one schema vs. the entire db, but usually if there are steps that make objects invalid it's on a schema basis.
Brian C. writes: There is also an Oracle supplied script that does this. The script can be run from an SQL prompt as the user sys. It's found at ORACLE_HOME\rdbms\admin\utlrp.sql
For More Information
- What do you think about this tip? E-mail the editor at email@example.com with your feedback.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
- Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.