如何重新编译存储过程等
<DIV>工作中，存储过程、函数等难免失效，那么怎么找到失效的存储过程呢</DIV><DIV>
<DIV class=codeText id=codeText>
<OL class=dp-css style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-TOP: 5px">
<LI>SELECT OBJECT_NAME,TO_CHAR(LAST_DDL_TIME,'YYYY-MM-DD HH24:MI:SS'),STATUS FROM USER_OBJECTS WHERE STATUS='INVALID' AND OBJECT_TYPE IN('FUNCTION','PROCEDURE','PACKAGE') ;</LI></OL></DIV></DIV>
<P>重新编译对象的语法</P>
<DIV class=codeText id=codeText>
<OL class=dp-css style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-TOP: 5px">
<LI><SPAN style="COLOR: #000000"><SPAN style="COLOR: #0000ff">alter</SPAN> object_type object_name <SPAN style="COLOR: #ff0000">compile</SPAN><SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV>
<P>当然，上面两个步骤也可以通过一条语句完成</P>
<DIV class=codeText id=codeText>
<OL class=dp-css style="PADDING-RIGHT: 0px; PADDING-LEFT: 0px; PADDING-BOTTOM: 5px; MARGIN: 0px 1px 0px 0px; PADDING-TOP: 5px">
<LI><SPAN style="COLOR: #000000">declare<BR></LI>
<LI>v1 <SPAN style="COLOR: #0000ff">varchar2</SPAN><SPAN style="COLOR: #0000cc">(</SPAN>500<SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">begin</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">for</SPAN> k <SPAN style="COLOR: #0000ff">in</SPAN> <SPAN style="COLOR: #0000cc">(</SPAN><SPAN style="COLOR: #0000ff">select</SPAN> object_type<SPAN style="COLOR: #0000cc">,</SPAN>object_name <SPAN style="COLOR: #0000ff">from</SPAN> user_objects<BR></LI>
<LI><SPAN style="COLOR: #0000ff">where</SPAN> status<SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #ff00ff">'INVALID'</SPAN> <SPAN style="COLOR: #0000ff">and</SPAN> object_type <SPAN style="COLOR: #0000ff">in</SPAN><SPAN style="COLOR: #0000cc">(</SPAN><SPAN style="COLOR: #ff00ff">'FUNCTION'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><SPAN style="COLOR: #ff00ff">'PROCEDURE'</SPAN><SPAN style="COLOR: #0000cc">,</SPAN><SPAN style="COLOR: #ff00ff">'PACKAGE'</SPAN><SPAN style="COLOR: #0000cc">)</SPAN><SPAN style="COLOR: #0000cc">)</SPAN> loop<BR></LI>
<LI>v1<SPAN style="COLOR: #0000cc">:</SPAN><SPAN style="COLOR: #0000cc">=</SPAN><SPAN style="COLOR: #ff00ff">'alter '</SPAN><SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #0000cc">|</SPAN>k<SPAN style="COLOR: #0000cc">.</SPAN>object_type <SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #ff00ff">' '</SPAN><SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #0000cc">|</SPAN>k<SPAN style="COLOR: #0000cc">.</SPAN>object_name<SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #0000cc">|</SPAN><SPAN style="COLOR: #ff00ff">' compile'</SPAN><SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #ff0000">execute</SPAN> <SPAN style="COLOR: #0000ff">immediate</SPAN> v1<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">end</SPAN> loop<SPAN style="COLOR: #0000cc">;</SPAN><BR></LI>
<LI><SPAN style="COLOR: #0000ff">end</SPAN><SPAN style="COLOR: #0000cc">;</SPAN></SPAN></LI></OL></DIV>
<P> </P>
<P> </P>
<P> </P>
<P> </P>
页:
[1]