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
Reader Feedback
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
dbmsdminutlrp.sql
For More Information
- What do you think about this tip? E-mail the editor at tdichiara@techtarget.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.
