Re: PL/SQL Procedures Get Invalidated
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: sbootsma@xxxxxxxxxxxxx
- Date: Sat, 22 Apr 2006 01:44:27 -0400
On 04/21/2006 12:43:22 PM, Sam Bootsma wrote:
> We have an inconsistent, but frequent, problem in our non-production
> environment. Our web application, accessing a non-production database,
> will return a "page not found" error. The problem is always traced to
> one or more PL/SQL packages or procedures that are invalidated.
> Manually compiling the invalid object fixes the problem. Then, after a
> while, the package is invalidated again. The QA has also traced the
> source of the problem to clicking a back arrow on the web application
> (however, this does not always cause the problem, only sometimes).
>
>
>
> This problem exists only for databases on our TEST box, and all these
> databases share the same Oracle Home on this box. The PROD database, by
> itself on a separate box, does not have this problem (whew!). I tried
> to get our QA or Systems Analyst to manually run the Oracle procedure
> from Sql*Plus, but I am told "the package can not be run directly from
> sql*plus since some build in package such as OWA, which is heavily
> depend on http context, is meant to be called from http
> request/response."
>
>
>
> Can anybody provide me with a clue as to what is happening and what I
> can do to fix it? Why do we have packages that sometimes become
> invalidated?
Sam, the first thing to do would be to turn on auditing. That would help you
catch
the operation which invalidates the package. I obviously don't have enough
information
to figure that out here, so allow me to speculate:
1) Somebody is changing an object referenced by a package, or an object
referenced
by an object referenced by a package.
2) Somebody is gathering statistics diligently. Too diligently. I've seen people
calculating statistics within the ETL script and wondering why do all
procedures
become invalid.
3) There is dynamic DDL somewhere in the loop. DDL commands like truncate or
analyze
executed on the tables on which the given procedure depends will normally
invalidate
the procedure.
In connection with that, in 10.2, I am unable to invalidate PL/SQL object, even
with copious
amount of DDL:
SQL> begin
2 dbms_output.put_line(sum_mgr(10));
3 end;
4 /
12600
PL/SQL procedure successfully completed.
SQL> create index emp1_deptno_i on emp1(deptno);
Index created.
SQL> analyze table emp1 compute statistics for table for all indexed columns
2 /
Table analyzed.
SQL> @/tmp/1
OBJECT_NAME STATUS
----------------------------------- -------
EMP1 VALID
SUM_MGR VALID
SQL> begin
2 dbms_output.put_line(sum_mgr(10));
3 end;
4 /
12600
PL/SQL procedure successfully completed.
SQL> truncate table emp1;
Table truncated.
SQL> @/tmp/1
OBJECT_NAME STATUS
----------------------------------- -------
EMP1 VALID
SUM_MGR VALID
SUM_MGR is a trivial function which has a hard reference to EMP table.
Here is the source code:
CREATE OR REPLACE FUNCTION "SCOTT"."SUM_MGR" (d number) return number
as
total_sal number(10,3);
begin
select sum(sal) into total_sal
from emp1
where deptno=d and
job != 'MANAGER';
return(total_sal);
end;
/
I created index, analyzed table and truncated it, while the function did not
change
the status to 'INVALID'. Personally, I find that extremely strange. In version
9.2,
the one you are using, I believe that the function would go invalid. Is anybody
here
aware of any changes in 10.2, with respect to dependency tracking? This
dependency was
properly recorded:
1 select referenced_owner, referenced_type,referenced_name,dependency_type
2 from user_dependencies
3* where name='SUM_MGR'
SQL> /
REFERENCED_OWNER REFERENCED_TYPE
------------------------------ -----------------
REFERENCED_NAME DEPE
---------------------------------------------------------------- ----
SYS PACKAGE
STANDARD HARD
SYS PACKAGE
SYS_STUB_FOR_PURITY_ANALYSIS HARD
SCOTT TABLE
EMP1
What the heck is "SYS_STUB_FOR_PURITY_ANALYSIS"? I know about the dependency on
package
STANDARD and dependency on table EMP1. I believe that part of the answer lies
there.
Sam, do you see where did an attempt to answer your question lead me? To a
completely
new problem! Shame on you!
--
Mladen Gogala
http://www.mgogala.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
