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:
Post a Comment