How to quickly recompile all stored procedures and compile stored procedures

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.