How to quickly recompile all stored procedures and compile stored procedures
The general idea is to obtain all the stored procedures from sys. Objects, and then combine dynamic strings to use sp_recompile to re-compile all the SP.
SELECT ROW_NUMBER() OVER(ORDER BY name) AS RID,'exec sp_recompile ''' + name +'''' AS TextINTO #TEMPFROM sys.objects WHERE TYPE = 'P'DECLARE @MaxID INTDECLARE @SQL VARCHAR(MAX)SELECT @MaxID = MAX(RID) FROM #TEMPWHILE (@MaxID IS NOT NULL)BEGINSELECT @SQL = TextFROM #TEMPWHERE RID = @MaxID--PRINT @SQLEXEC (@SQL)SELECT @MaxID = MAX(RID) FROM #TEMP WHERE RID < @MaxID END
I don't know if there are any better methods.
How to re-compile the stored procedure? What command is used?
Execute, for example:
Execute obooke;
How to batch compile views and stored procedures in oracle
1. Batch compile the stored procedure of the Storage Project
Create or replace procedure compile_invalid_procedures (
P_owner varchar2 -- owner name, that is, SCHEMA) as -- compile an invalid stored procedure under a user
Str_ SQL varchar2 (200); begin
For invalid_procedures in (select object_name from all_objects
Where status = 'invalid' and object_type = 'processed' and owner = upper (p_owner) loop
Str_ SQL: = 'alter procedure '| invalid_procedures.object_name | 'compile'; begin
Execute immediate str_ SQL; exception
-- When Others Then Null;
When OTHERS Then
Dbms_output.put_line (sqlerrm); end loop; end;
2. Batch compilation of view stored procedures