Total Pageviews

2016/01/03

[Oracle] How to compiles all procedures, functions, packages, and triggers in the specified schema.

Problem
If you do alter table in Oracle database, your stored procedures or views' state may become invalid as bellows:


You need to rebuild all invalid stored procedures one by one
1
ALTER PROCEDURE PROC_FMS406R_TAB5_RPT1_STEP1 COMPILE;

If I have multiple invalid stored procedures, it will be very annoying. 
Does Oracle provide any convenient way to rebuild all invalid stored procedures?

How-To
You can use exec dbms_utility.compile_schema(SCHEMA NAME) to fulfill this requirement. Assume my schema name is AP_PSR, then the command is as follows:
1
exec dbms_utility.compile_schema('AP_PSR');

See...all invalid stored procedures had become invalid




Reference
[1] http://dbataj.blogspot.tw/2007/08/how-to-compile-invalid-objects.html

No comments: