Thanks Tom
Jeremy Nieuwoudt, December 10, 2001 - 9:49 pm UTC
The reason I would like to get rid of invalid objects is I'd like to find any problems before the end-users do.
Unable to understand the behaviour
murali, April 15, 2002 - 8:55 am UTC
Sir,
On 8.1.7,first, I connected as the user accounts with invalid objects and gave the command,
SQL> select object_type,count(*) from user_objects where status = 'INVALID'
2 group by object_type;
OBJECT_TYPE COUNT(*)
------------------ ----------
PACKAGE BODY 4
PROCEDURE 61
VIEW 3
next, as sys
exec dbms_utility.compile_schema( 'ACCOUNTS' );
PL/SQL procedure successfully completed.
then,
connect accounts
SQL> select object_type,count(*) from user_objects where status = 'INVALID' group by object_type;
OBJECT_TYPE COUNT(*)
------------------ ----------
PACKAGE BODY 1
PROCEDURE 182
VIEW 3
Without changing any code, i manually compiled the invalid objects using alter procedure proc_name compile;
SQL> select object_type,count(*) from user_objects where status = 'INVALID'
2 group by object_type;
OBJECT_TYPE COUNT(*)
------------------ ----------
PACKAGE BODY 1
PROCEDURE 49
VIEW 1
Could you please explain this behaviour.
thanks and regards
April 16, 2002 - 7:24 am UTC
Yes, because when you compile procedure X, and there is a procedure Y that calls procedure X, procedure Y will go invalid again -- even if it was valid before. Y needs to be compiled again.
select * from user_errors to see if in fact there are ERRORS. If not, no problems.
In my book if you have it -- there is a procedure that compiles ALL code in a schema cleanly (if possible) under all circumstances.
Compiling Invalid Objects
Vishwas Kulkarni, April 16, 2002 - 5:06 am UTC
Hi ALL
You can also use $ORACLE_HOME/rdbms/admin/utlrp.sql
which will compile all invalid objects thru SVRMGRL.
Invalid objects
Leigh Gold, October 04, 2002 - 2:54 pm UTC
Tom,
I have noticed that using Powerbuilder application to access an invalid object does not automatically recompile the object, it gave a ora-20003 error instead.
After I compiled it manually, the application ran fine.
Why is this so?
October 04, 2002 - 6:58 pm UTC
cause Sybase doesn't know how to program against Oracle?
Seriously, it is because they are trying to describe an invalid object, when they encounter the 20003, they should simply PARSE:
begin OBJECT; end;
regardless of what object is -- that will auto-compile it (a describe will not). They could then describe it successfully.
You could file an enhancement request with them to have them do it. Its really quite simple.
exception during recompile
June, February 21, 2003 - 5:23 pm UTC
Tom,
I tried to understand what's happening in here:
I use one dynamic script to recompile all invalid pkg/fn/procedure under one dba account, and interesting thing is, one of procedure in schema1 turned to invalid after same name of procedure in schema2 didn't recompiled successfully due to less privilege on common tables in schema3. (procedure in schema1 will be valid if recompiled individually). after I granted privilege from schema3, both procedures under schema1/2 recompiled successfully. However these two schemas didn't call each other, other than they access to same table in schema3.
have any idea why is that? It really shouldn't. Is it a good exercise to recompile invalid objects under dba accounts for all schema?
thanks in advance for any input.
February 21, 2003 - 6:48 pm UTC
I don't bother compiling objects, no -- they recompile themselves when invoked.
But if you give me a test case with create users, etc etc etc that shows the issue you hit, I'll explain why.
What you have above is insufficient to reproduce (it doesn't work the way you describe).... there is something missing in the details there.
Valid status in Oracle 8.0.5 on NT
A reader, June 12, 2003 - 9:13 am UTC
Hi Tom,
The issue that I have is, there is function F1 which is using table T1,T2,T3. After I modified the structure of table T1, the function F1 should become invalid. But it doesn't ! and it shows 'valid' status for that. But when I want to use it in application which is PB 8 , it fails and I have to recompile it manually, then it works.
Here are my questions:
1- Why oracle shows Valid status for an invalid object?
2- I didn't understand your comments about PB
"begin OBJECT; end;
regardless of what object is -- that will auto-compile it (a describe will not).
They could then describe it successfully.
"
What is describe ? What do you mean by "begin object end" ?
Thank you in advance for your valuable time.
Regards,
Arash
June 12, 2003 - 9:29 am UTC
you would need to supply a testcase that backs that up:
ops$tkyte@ORA806> create table t1 ( x int );
Table created.
ops$tkyte@ORA806> create table t2 ( y int );
Table created.
ops$tkyte@ORA806> create table t3 ( z int );
Table created.
ops$tkyte@ORA806>
ops$tkyte@ORA806> create or replace procedure p
2 as
3 begin
4 for x in ( select * from t1, t2, t3 )
5 loop
6 null;
7 end loop;
8 end;
9 /
Procedure created.
ops$tkyte@ORA806>
ops$tkyte@ORA806> @invalid
ops$tkyte@ORA806> break on object_type skip 1
ops$tkyte@ORA806> column status format a10
ops$tkyte@ORA806> select object_type, object_name, status
2 from user_objects
3 where status = 'INVALID'
4 order by object_type, object_name
5 /
no rows selected
ops$tkyte@ORA806> alter table t1 add a int;
Table altered.
ops$tkyte@ORA806> @invalid
ops$tkyte@ORA806> break on object_type skip 1
ops$tkyte@ORA806> column status format a10
ops$tkyte@ORA806> select object_type, object_name, status
2 from user_objects
3 where status = 'INVALID'
4 order by object_type, object_name
5 /
OBJECT_TYPE OBJECT_NAME STATUS
--------------- ------------------------------ ----------
PROCEDURE P INVALID
<b>i cannot reproduce</b>
and by "begin OBJECT; end;" what I mean is -- all PB needs to do is parse a string 'begin p; end;' since P is the "object" in question here. the block I generally use it:
begin if (1=0) then p; end if; end;
that'll cause p to compile (if it can) but most certainly would never actually execute it by accident.
Similar problem with Microsoft OLEDB driver for Oracle...
Kamal Kishore, June 12, 2003 - 9:50 am UTC
Hi Tom,
When a package goes invalid because of dependencies, some of our applications that call this package procedures (and connect to the Oracle database using Microsoft OLEDB Driver for Oracle) start to report errors that "can not execute; package state is invalid" ....
When we go to SQL*Plus and compile manually the package, everything than is fine. The application runs correctly.
Is this again a limitation of the OLEDB driver (as said before for the PowerBuilder app) that it fails to identify this and take corrective measures?
Is there something we could do different (apart from asking Microsoft to update its OLEDB driver !!!), which could circumvent this problem?
I keep telling everyone that there is no need to manually compile the invalid objects as Oracle automatically takes care of this, but when we see that package calls are failing through OLEDB driver, I'm asked always, "Now, why does Oracle not automatically compile this? Why must I manually compile the package before it starts to work again?"
Is it safe to point fingers at the OLEDB driver and put it as a limitation (or would you call it a bug?) of the driver implementation?
Thanks,
June 12, 2003 - 10:27 am UTC
they are trying to describe the object in order to discover its inputs and outputs.
you cannot describe an invalidate object.
you must either:
o compile it
o cause it to be compiled -- parse -- begin if (1=0) then p; end if; end; and
it'll compile itself
so, I guess a solution would be to catch the error in PB, dynamically parse that block and retry the operation.
PB working with Oracle
A reader, June 12, 2003 - 10:08 am UTC
Tom,
So it's the PB issue and I have to recompile it manually ! Am I right?
Thanks again,
Arash
I have a similar problem - Package body compiled successfully But status is Invalid
Balaji CN, September 26, 2003 - 12:03 pm UTC
Hi
I have a strange problem - I was trying to create a Package .
The package header and body compiled successfully But status is Invalid for the body alone.
I dont know how this is possible.
SQL> @D:\BalajiCN\CCR\Dev\ppdadmin.ph
Package created.
SQL> show err
No errors.
SQL> @D:\BalajiCN\CCR\Dev\ppdadmin.pb
Package body created.
SQL> show err
No errors.
SQL> select distinct name from user_errors;
NAME
------------------------------
COMMENTS
SQL> SELECT OBJECT_NAME , OBJECT_TYPE , STATUS FROM USER_OBJECTS WHERE OBJECT_NAME = 'PPDADMIN';
OBJECT_NAME
----------------------------------------------------------------------------------------------------
OBJECT_TYPE STATUS
------------------ -------
PPDADMIN
PACKAGE VALID
PPDADMIN
PACKAGE BODY INVALID
i DONT KNOW HOW THIS HAPPENS??
can you please help me??
I am calling procedure in an another database via a public db link and accessing the proc thro a public synonym .
and the grants everything are proper..
i am also able to execute the proc.
but second time it said , unable to find the pl sql unit being callled..
any help welcome.
regards
balaji
September 26, 2003 - 2:49 pm UTC
I have no idea what package was created in those scripts.
hows about looking at LAST_DDL_TIME to see when those guys where last modified as well.
Invalid objects
Sam, October 11, 2004 - 6:29 pm UTC
Hi Tom,
I upgraded my 9.2.0.x db to 10g a few days back. Upgrade looked ok at that point. Now I see 19 views, 19 synonyms and 2 packages invalid.
to be precise they are
DBA_HIST_FILESTATXS VIEW SYS
DBA_HIST_FILESTATXS SYNONYM PUBLIC
DBA_HIST_SQLSTAT VIEW SYS
DBA_HIST_SQLSTAT SYNONYM PUBLIC
DBA_HIST_SQLBIND VIEW SYS
DBA_HIST_SQLBIND SYNONYM PUBLIC
DBA_HIST_SYSTEM_EVENT VIEW SYS
DBA_HIST_SYSTEM_EVENT SYNONYM PUBLIC
DBA_HIST_WAITSTAT VIEW SYS
DBA_HIST_WAITSTAT SYNONYM PUBLIC
DBA_HIST_LATCH VIEW SYS
DBA_HIST_LATCH SYNONYM PUBLIC
DBA_HIST_LATCH_MISSES_SUMMARY VIEW SYS
DBA_HIST_LATCH_MISSES_SUMMARY SYNONYM PUBLIC
DBA_HIST_DB_CACHE_ADVICE VIEW SYS
DBA_HIST_DB_CACHE_ADVICE SYNONYM PUBLIC
DBA_HIST_ROWCACHE_SUMMARY VIEW SYS
DBA_HIST_ROWCACHE_SUMMARY SYNONYM PUBLIC
DBA_HIST_SGASTAT VIEW SYS
DBA_HIST_SGASTAT SYNONYM PUBLIC
DBA_HIST_SYSSTAT VIEW SYS
DBA_HIST_SYSSTAT SYNONYM PUBLIC
DBA_HIST_SYS_TIME_MODEL VIEW SYS
DBA_HIST_SYS_TIME_MODEL SYNONYM PUBLIC
DBA_HIST_OSSTAT VIEW SYS
DBA_HIST_OSSTAT SYNONYM PUBLIC
DBA_HIST_PARAMETER VIEW SYS
DBA_HIST_PARAMETER SYNONYM PUBLIC
DBA_HIST_SEG_STAT VIEW SYS
DBA_HIST_SEG_STAT SYNONYM PUBLIC
DBA_HIST_ACTIVE_SESS_HISTORY VIEW SYS
DBA_HIST_ACTIVE_SESS_HISTORY SYNONYM PUBLIC
DBA_HIST_TABLESPACE_STAT VIEW SYS
DBA_HIST_TABLESPACE_STAT SYNONYM PUBLIC
DBA_HIST_SERVICE_STAT VIEW SYS
DBA_HIST_SERVICE_STAT SYNONYM PUBLIC
DBA_HIST_SERVICE_WAIT_CLASS VIEW SYS
DBA_HIST_SERVICE_WAIT_CLASS SYNONYM PUBLIC
DBMS_SWRF_REPORT_INTERNAL PACKAGE BODY SYS
DBMS_SQLTUNE PACKAGE BODY SYS
I ran utlrp.sql almost twice but these objects seemed to go invalid after a few hours. I know you mentioned that invalid objects fix themselves but i am just concerned because these are sys objects. Any insight would be highly appreciated. Thanks again.
October 11, 2004 - 7:59 pm UTC
it is OK and expected even.
The workload repository performs nightly split operations which will invalidate a number of views.
pasko, October 12, 2004 - 3:39 am UTC
Hi Tom,
some of my colleagues are getting this error from a java client calling some PL-SQL Package, and it happens when we modify some Package Bodies ...
Now we are currently discussing ways to get around this error..
some are suggesting for the Java Client to catch this Error and to reconnect the moment they catch it.
is it a feasible solution ?
shema.package: Exception occured: Sql-Error: -4061 ORA-04061: existing state of has been invalidated ORA-04061: existing state of package body "schema.pkg.body" has been invalidated ORA-04065: not executed, altered or dropped package body "package.body" ORA-06508: PL/SQL: could not find program unit being called ORA-06512: at "schema.pkg.body", line 8 ORA-04088: error during execution of trigger 'schema.trigger'
Thanks in advance.
Regards.
October 12, 2004 - 8:08 am UTC
you need not reconnect -- you would only need to figure out, "hmm, is it safe to just execute the call again"
What happened:
o you have a plsql package that has a 'state' (globals for example)
o you run that plsql. the state is established.
o your program (java code) is relying on this state (else, why would you
establish it). Just like your java code is relying on the connection object
to "remember" what database it is connected to (eg: think of a plsql package
like a 'class')
o you just came along and wiped out this state - you erased the running code
and replaced it.
So, can your APPLICATION deal with that loss of state???? will it survive? is it dependent on the variables you just totally wiped out?
What would happen to your java code if you zapped in new jdbc drivers in the middle? This is not any different.
I'm always curious why people recompile running code in a production system. Do you drop in new zip/jar/war/ear/whatever files in the middle of the day? Why do you do this to your plsql code? it is "real code" too.
Thanks for the Clarification Tom !
A reader, October 12, 2004 - 3:16 pm UTC
huge dependency tree
A reader, November 09, 2004 - 2:54 pm UTC
hi tom,
we have a web application grown, developed and gone through a lot of hands over time (hope this clarifies any further ;(
the developers produced a huge dependency tree on a procedure over time, containing over 200(!) dependencies (not including references to system objects like dbms_output) and also including references to remote objects. please note, that we are not able to put this into a package immediately because of ... see above.
this procedure is called out from a sybase server via sybase direct connect but producing an "unable to execute procedure because it is marked invalid" error in sybase. when we look into the database we see this top level procedure marked as invalid.
the dependency tree is sooo large that i simply have no idea where i should begin to look forward to catch this error. that means catching the event invalidating the tree and also the event which prevents for successful automatic recompilation. tom, can you give me a hint on this?
following you will find the full dependency tree from top (muecknick.aam_e2) to bottom. please note, that all calls to non muecknick objects are calls to a remote database via a database link (oracle 9.2.0.4 to 8.1.7.4).
SQL> r
1 select owner, name, type, referenced_owner, referenced_name, referenced_type
2 from (
3 select *
4 from dba_dependencies
5 start with owner = 'MUECKNICK'
6 and name = 'AAM_E2'
7 connect by PRIOR referenced_name = name
8 and PRIOR referenced_type = type
9 and PRIOR referenced_owner = owner
10 )
11 where owner not in ('SYS', 'PUBLIC')
12* and referenced_owner not in ('SYS', 'PUBLIC')
OWNER NAME TYPE REFERENCED REFERENCED_NAME REFERENCED
---------- ---------------------- ---------- ---------- ---------------------- ----------
MUECKNICK AAM_E2 PROCEDURE MUECKNICK CUST_PROD_SERV_SEQ SEQUENCE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK LOG_PROC_SEQ SEQUENCE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK AAE2 TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK CUST TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK LOG_PROC_AAM_E2 TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK PROD TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK PROD_SERV TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK CPS SYNONYM
MUECKNICK AAM_E2 PROCEDURE MUECKNICK CPSA SYNONYM
MUECKNICK AAM_E2 PROCEDURE MUECKNICK PS SYNONYM
MUECKNICK AAM_E2 PROCEDURE MUECKNICK CUST_PROD_SERV_VW1 VIEW
MUECKNICK CUST_PROD_SERV_VW1 VIEW MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK CUST_PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK CUST_PROD_SERV_VW5 VIEW
MUECKNICK CUST_PROD_SERV_VW5 VIEW MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK CUST_PROD_SERV_VW5 VIEW MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK CUST_PROD_SERV_VW5 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK CUST_PROD_SERV_VW5 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK CUST_PROD_SERV_VW5 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK BIE_TRANSFER_SEQ SEQUENCE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK BIE_TRANSFER TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK PROD_SERV_VW3 VIEW
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK PROD_SERV_VW2 VIEW
MUECKNICK PROD_SERV_VW2 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK PROD_SERV_VW2 VIEW MUECKNICK PROD_SERV_VW1 VIEW
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK DUAL NON-EXISTE
NT
MUECKNICK AAM_E2 PROCEDURE MUECKNICK NEWCPS PROCEDURE
MUECKNICK NEWCPS PROCEDURE MUECKNICK CUST_PROD_SERV_SEQ SEQUENCE
MUECKNICK NEWCPS PROCEDURE MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK NEWCPS PROCEDURE MUECKNICK PROD_SERV TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK DELETEPROD PROCEDURE
MUECKNICK DELETEPROD PROCEDURE MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK DELETEPROD PROCEDURE MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK DELETEPROD PROCEDURE MUECKNICK PROD_SERV TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK ATTRIBUTE PACKAGE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK CHECKATTR FUNCTION
MUECKNICK CHECKATTR FUNCTION MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK CHECKATTR FUNCTION MUECKNICK CHECK_EMAILADDR FUNCTION
MUECKNICK CHECK_EMAILADDR FUNCTION MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK CHECK_EMAILADDR FUNCTION MUECKNICK LOOKUP_EMAIL TABLE
MUECKNICK CHECK_EMAILADDR FUNCTION MUECKNICK RSVD_ATTR TABLE
MUECKNICK CHECKATTR FUNCTION MUECKNICK CHECK_WEBLINK FUNCTION
MUECKNICK CHECK_WEBLINK FUNCTION MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK CHECKATTR FUNCTION MUECKNICK CHECK_ZSZEMAILADDR FUNCTION
MUECKNICK CHECK_ZSZEMAILADDR FUNCTION MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK CHECK_ZSZEMAILADDR FUNCTION MUECKNICK CHECK_EMAILADDR FUNCTION
MUECKNICK CHECK_EMAILADDR FUNCTION MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK CHECK_EMAILADDR FUNCTION MUECKNICK LOOKUP_EMAIL TABLE
MUECKNICK CHECK_EMAILADDR FUNCTION MUECKNICK RSVD_ATTR TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK GETATTRIBUTEVALUE FUNCTION
MUECKNICK GETATTRIBUTEVALUE FUNCTION MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK GETATTRIBUTEVALUE FUNCTION MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK GETATTRIBUTEVALUE FUNCTION MUECKNICK PROD_SERV TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK DELATTRIBUTE PROCEDURE
MUECKNICK DELATTRIBUTE PROCEDURE MUECKNICK CUST TABLE
MUECKNICK DELATTRIBUTE PROCEDURE MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK DELATTRIBUTE PROCEDURE MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK DELATTRIBUTE PROCEDURE MUECKNICK CPSA SYNONYM
MUECKNICK AAM_E2 PROCEDURE MUECKNICK SETATTRIBUTE PROCEDURE
MUECKNICK SETATTRIBUTE PROCEDURE MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK SETATTRIBUTE PROCEDURE MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK SETATTRIBUTE PROCEDURE MUECKNICK PROD_SERV_VW3 VIEW
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK PROD_SERV_VW2 VIEW
MUECKNICK PROD_SERV_VW2 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK PROD_SERV_VW2 VIEW MUECKNICK PROD_SERV_VW1 VIEW
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK DBMS_OUTPUT NON-EXISTE
NT
MUECKNICK AAM_E2 PROCEDURE MUECKNICK ADD_IPADDRESS PROCEDURE
MUECKNICK ADD_IPADDRESS PROCEDURE MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK ADD_IPADDRESS PROCEDURE IMS UTA_WK_UTILS PACKAGE
MUECKNICK ADD_IPADDRESS PROCEDURE IMS4IDE IMS NON-EXISTE
NT
MUECKNICK ADD_IPADDRESS PROCEDURE MUECKNICK LOG_IP_ADD_SEQ SEQUENCE
MUECKNICK ADD_IPADDRESS PROCEDURE MUECKNICK LOG_IP_ADD TABLE
MUECKNICK ADD_IPADDRESS PROCEDURE IMSADMIN UTA_IMS_ACCOUNT_DETAIL TABLE
S
MUECKNICK ADD_IPADDRESS PROCEDURE MUECKNICK CUST_PROD_SERV_VW7 VIEW
MUECKNICK CUST_PROD_SERV_VW7 VIEW MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK CUST_PROD_SERV_VW7 VIEW MUECKNICK CUST_PROD_SERV_VW1 VIEW
MUECKNICK CUST_PROD_SERV_VW1 VIEW MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK CUST_PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK CUST_PROD_SERV_VW7 VIEW MUECKNICK PROD_SERV_VW3 VIEW
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK PROD_SERV_VW2 VIEW
MUECKNICK PROD_SERV_VW2 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK PROD_SERV_VW2 VIEW MUECKNICK PROD_SERV_VW1 VIEW
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK ADD_IPADDRESS PROCEDURE MUECKNICK SETATTRIBUTE PROCEDURE
MUECKNICK SETATTRIBUTE PROCEDURE MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK SETATTRIBUTE PROCEDURE MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK SETATTRIBUTE PROCEDURE MUECKNICK PROD_SERV_VW3 VIEW
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK PROD_SERV_VW2 VIEW
MUECKNICK PROD_SERV_VW2 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK PROD_SERV_VW2 VIEW MUECKNICK PROD_SERV_VW1 VIEW
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK ADD_IPADDRESS PROCEDURE MUECKNICK DBMS_OUTPUT NON-EXISTE
NT
MUECKNICK ADD_IPADDRESS PROCEDURE MUECKNICK GETNEWIMSCODE FUNCTION
MUECKNICK GETNEWIMSCODE FUNCTION MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK GETNEWIMSCODE FUNCTION MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK GETNEWIMSCODE FUNCTION MUECKNICK IMSU_LKUP TABLE
MUECKNICK GETNEWIMSCODE FUNCTION MUECKNICK PROD_SERV TABLE
MUECKNICK GETNEWIMSCODE FUNCTION MUECKNICK CPS SYNONYM
MUECKNICK GETNEWIMSCODE FUNCTION MUECKNICK CPSA SYNONYM
MUECKNICK GETNEWIMSCODE FUNCTION MUECKNICK CUST_PROD_SERV_VW5 VIEW
MUECKNICK CUST_PROD_SERV_VW5 VIEW MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK CUST_PROD_SERV_VW5 VIEW MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK CUST_PROD_SERV_VW5 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK CUST_PROD_SERV_VW5 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK CUST_PROD_SERV_VW5 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK GETNEWIMSCODE FUNCTION MUECKNICK DUAL NON-EXISTE
NT
MUECKNICK GETNEWIMSCODE FUNCTION MUECKNICK GETATTRIBUTEVALUE FUNCTION
MUECKNICK GETATTRIBUTEVALUE FUNCTION MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK GETATTRIBUTEVALUE FUNCTION MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK GETATTRIBUTEVALUE FUNCTION MUECKNICK PROD_SERV TABLE
MUECKNICK GETNEWIMSCODE FUNCTION MUECKNICK DBMS_OUTPUT NON-EXISTE
NT
MUECKNICK AAM_E2 PROCEDURE MUECKNICK CHECK_IMS_PARAMS FUNCTION
MUECKNICK CHECK_IMS_PARAMS FUNCTION IMSADMIN UTA_IMS_ACCOUNT_DETAIL TABLE
S
MUECKNICK CHECK_IMS_PARAMS FUNCTION IMSADMIN UTA_ENUM_VALUE TABLE
MUECKNICK CHECK_IMS_PARAMS FUNCTION IMSADMIN UTA_ENUM TABLE
MUECKNICK CHECK_IMS_PARAMS FUNCTION IMSADMIN UTA_IP_ENUM_VALUE TABLE
MUECKNICK CHECK_IMS_PARAMS FUNCTION IMSADMIN UTA_IP_ENUM TABLE
MUECKNICK CHECK_IMS_PARAMS FUNCTION MUECKNICK DBMS_OUTPUT NON-EXISTE
NT
MUECKNICK AAM_E2 PROCEDURE MUECKNICK CHECK_VALIDVALUENAME FUNCTION
MUECKNICK CHECK_VALIDVALUENAME FUNCTION MUECKNICK PROD_SERV_VW1 VIEW
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK AAM_E2 PROCEDURE MUECKNICK AAX_DOMAIN FUNCTION
MUECKNICK AAX_DOMAIN FUNCTION MUECKNICK CUST TABLE
MUECKNICK AAX_DOMAIN FUNCTION MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK AAX_DOMAIN FUNCTION MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK AAX_DOMAIN FUNCTION MUECKNICK PROD_SERV TABLE
MUECKNICK AAX_DOMAIN FUNCTION MUECKNICK CPS SYNONYM
MUECKNICK AAX_DOMAIN FUNCTION MUECKNICK PS SYNONYM
MUECKNICK AAX_DOMAIN FUNCTION MUECKNICK GETATTRIBUTEVALUE FUNCTION
MUECKNICK GETATTRIBUTEVALUE FUNCTION MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK GETATTRIBUTEVALUE FUNCTION MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK GETATTRIBUTEVALUE FUNCTION MUECKNICK PROD_SERV TABLE
MUECKNICK AAX_DOMAIN FUNCTION MUECKNICK SETATTRIBUTE PROCEDURE
MUECKNICK SETATTRIBUTE PROCEDURE MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK SETATTRIBUTE PROCEDURE MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK SETATTRIBUTE PROCEDURE MUECKNICK PROD_SERV_VW3 VIEW
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK PROD_SERV_VW2 VIEW
MUECKNICK PROD_SERV_VW2 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK PROD_SERV_VW2 VIEW MUECKNICK PROD_SERV_VW1 VIEW
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK AAX_DOMAIN FUNCTION MUECKNICK DBMS_OUTPUT NON-EXISTE
NT
MUECKNICK AAX_DOMAIN FUNCTION MUECKNICK EXCLUDE_MAILDOMAIN FUNCTION
MUECKNICK AAX_DOMAIN FUNCTION MUECKNICK MERGE_MAILDOMAIN FUNCTION
MUECKNICK MERGE_MAILDOMAIN FUNCTION MUECKNICK DBMS_OUTPUT NON-EXISTE
NT
MUECKNICK AAM_E2 PROCEDURE MUECKNICK MIGRATE_BASE_PROD PROCEDURE
MUECKNICK MIGRATE_BASE_PROD PROCEDURE MUECKNICK CUST_PROD_SERV_SEQ SEQUENCE
MUECKNICK MIGRATE_BASE_PROD PROCEDURE MUECKNICK CUST TABLE
MUECKNICK MIGRATE_BASE_PROD PROCEDURE MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK MIGRATE_BASE_PROD PROCEDURE MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK MIGRATE_BASE_PROD PROCEDURE MUECKNICK PROD_SERV TABLE
MUECKNICK MIGRATE_BASE_PROD PROCEDURE MUECKNICK CPS SYNONYM
MUECKNICK MIGRATE_BASE_PROD PROCEDURE MUECKNICK CPSA SYNONYM
MUECKNICK MIGRATE_BASE_PROD PROCEDURE MUECKNICK PS SYNONYM
MUECKNICK MIGRATE_BASE_PROD PROCEDURE MUECKNICK BIE_TRANSFER_SEQ SEQUENCE
MUECKNICK MIGRATE_BASE_PROD PROCEDURE MUECKNICK BIE_TRANSFER TABLE
MUECKNICK MIGRATE_BASE_PROD PROCEDURE MUECKNICK SETATTRIBUTE PROCEDURE
MUECKNICK SETATTRIBUTE PROCEDURE MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK SETATTRIBUTE PROCEDURE MUECKNICK CUST_PROD_SERV_ATT TABLE
MUECKNICK SETATTRIBUTE PROCEDURE MUECKNICK PROD_SERV_VW3 VIEW
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW3 VIEW MUECKNICK PROD_SERV_VW2 VIEW
MUECKNICK PROD_SERV_VW2 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK PROD_SERV_VW2 VIEW MUECKNICK PROD_SERV_VW1 VIEW
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV_ATTR TABLE
MUECKNICK PROD_SERV_VW1 VIEW MUECKNICK SERV_ATTR TABLE
MUECKNICK MIGRATE_BASE_PROD PROCEDURE MUECKNICK ADDMISSINGCPS PROCEDURE
MUECKNICK ADDMISSINGCPS PROCEDURE MUECKNICK CUST_PROD_SERV_SEQ SEQUENCE
MUECKNICK ADDMISSINGCPS PROCEDURE MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK ADDMISSINGCPS PROCEDURE MUECKNICK PROD_SERV TABLE
MUECKNICK ADDMISSINGCPS PROCEDURE MUECKNICK CUST_PROD_SERV_VW1 VIEW
MUECKNICK CUST_PROD_SERV_VW1 VIEW MUECKNICK CUST_PROD_SERV TABLE
MUECKNICK CUST_PROD_SERV_VW1 VIEW MUECKNICK PROD_SERV TABLE
MUECKNICK MIGRATE_BASE_PROD PROCEDURE MUECKNICK SERVICE PACKAGE
MUECKNICK SERVICE PACKAGE MUECKNICK CUST_PROD_SERV_ATH TABLE
MUECKNICK SERVICE PACKAGE MUECKNICK CUST_PROD_SERV_ATT TABLE
203 Zeilen ausgewählt.
November 09, 2004 - 3:36 pm UTC
what does the size of the dependency tree have to do with anything?
you catch this error in "sybase" don't you? it is the one that gets the error.
upon catching this error (which comes from their code, they are trying to describe and getting this error) you can either:
a) use dbms_sql to simply parse but not executing: 'begin procedure_name; end;'
b) issue alter procedure_name compile;
and the describe will "work" again for them.
same reader than before
A reader, November 10, 2004 - 6:49 am UTC
hi tom,
first, thanks for you help. if the automatic recompilation isn't done as they (sybase) try to describe invalided objects, we will simple catch and implement one of your suggested solutions.
the size of the dependency tree influences the chance of finding out the "what" and in following the "who" causing the invalidation. i want to know this "what" and "who" and there are more than 200 opportunities in my tree - unfortunately ;)
i know that there should be an automatic recompilation in following. i think that's why you told me that i shouldn't care about it. but wouldn't it influence the performance too if there is a recompilation required every time due to unknown things?
meanwhile, i found that someone had changed some of the remote procedures (=> dba_objects.last_ddl_time).
referencing this: </code>
http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/adg10pck.htm#36095 <code>
using the timestamp model for remote dependencies, why do i get "ORA-04062: timestamp of procedure "IMS4IDE.PTEST" has been changed" if the remote procedure is (also) invalid before an automatic recompilation is done with the next normal execution of the local procedure? seems to be senseless?
thanks,
max
November 10, 2004 - 7:15 am UTC
the problem is they are not just trying to run the procedure (which would auto-compile) but they are using stored procedures that try to "describe" the remote procedure before executing it -- and this is failing. auto-recompilation would take place if they just accepted your inputs and, well, ran it.
I don't understand your last paragraph there. But if you use timestamp and the last time your "local" compiled stored object was compiled the remote procedure "P" had a timestamp of 01-jan-2004 00:00:00 and the next time you goto run it, it has 30-jan-2004 00:00:00, the procedure will have to (by definition, you told it to) invalidate itself when it discovers this fact. This is why you probably want to use SIGNATURE based remote dependency tracking -- to avoid this, it'll only invalidate itself when the SIGNATURE (inputs and outputs) change.
same as before
A reader, November 10, 2004 - 8:17 am UTC
hi again tom,
the question has been why the local procedure invalidates itself and presents an error immediately? why doesn't it even try to recompile BEFORE invalidating and raising an error?
reading your last answer i guess, it is the way it works.
timestamp = restrictive = inform me every time something changed.
signature = less restrictive = try it to recompile/reuse before informing me about a change
question: is it possible to change the remote_dependencies_mode on a "per procedure" basis using "alter session set remote_dependencies_mode = signature" command + "alter procedure ... recompile"?
where can i find the remote_dependencies_mode per procedure?
thanks,
max
November 10, 2004 - 11:53 am UTC
because the DBLINK isn't touched until you touch the database link.
if you had:
if (day='TUE')
then
p@remote;
end if;
p would ONLY be accessed, and the dblink will only be opened -- when the day is TUE.
the procedure won't open each and every database link referenced until you hit them.
i would use one or the other method across the board, they are session - not compile time -- settings.
utl_recomp
AJ Allen, November 10, 2004 - 9:05 am UTC
First, for the prior reader about ability to alter remote_dependencies_mode at the session level. Try executing this query, it will answer your question:
SELECT p.name
p.value
p.isdefault
p.ISSES_MODIFIABLE,
p.ISSYS_MODIFIABLE,
p.ISMODIFIED,
p.ISADJUSTED
FROM v$parameter p
WHERE name = 'remote_dependencies_mode';
Now to my followup question. . .
I find that when importing several schemas by using fromuser/touser to change ownership I need to recompile procs to see if any remain invalid so I can fix them ( table permissions, etc.). I find this easy to do using the utl_recomp package. It is documented in 10g, and therefor supported, but not documented in 9i (Supplied PL-SQL Packages book), even though it can be installed by running ?/rdbms/admin/utlrcmp. Is utl_recomp supported in 9i? If it not supported, is it generally safe to use anyway?
Thanks for your support and infinite (nearly) patience.
November 10, 2004 - 12:07 pm UTC
it is not necessary -- not for an issue like this (and would not help this - these procedures are not invalid UNTIL they started running, the utl script would not touch them)
not able to put this into a package immediately ... ??
Gabe, November 10, 2004 - 10:36 am UTC
To "A reader" ...
<quote>
the developers produced a huge dependency tree on a procedure over time, containing over 200(!) dependencies (not including references to system objects like dbms_output) and also including references to remote objects. please note, that we are not able to put this into a package immediately because of ... see above.
this procedure is called out from a sybase server via sybase direct connect but producing an "unable to execute procedure because it is marked invalid" error in sybase. when we look into the database we see this top level procedure marked as invalid.
</quote>
Let us say your procedure is p(a,b,c) ...
Well, create package pkg with a procedure p_main(a,b,c) in the spec ... put all the code from p in the package body ... rewrite p(a,b,c) as:
procedure p(a ..., b ..., c ...) is
begin
pkg.p_main(a,b,c);
end;
The +200 dependecies are now for the PKG body ... hence p won't become invalid ... hence sybase won't complain when describing P.
Seems simple enough ... unless I'm missing something.
November 10, 2004 - 12:18 pm UTC
well done
perfect.
very well idea
A reader, November 10, 2004 - 2:07 pm UTC
@gabe:
this is a very well idea. supports soft migration from procedures into packages :) thanks.
@tom:
it sounds like BOTH, timestamp + signature, are stored locally and the init parameter only decides which method is invoked?
thanks,
max
November 10, 2004 - 8:12 pm UTC
at run time, it decides based on the remote dependencies setting to see of
a) the signature doesn't match
b) the timestamp of the remote object changed
they are both available.
Removing those pesky procedure/function calls
Bob B, November 10, 2004 - 6:29 pm UTC
In addition to Gabe's suggested, adding in an autonomous transaction procedure that uses WHO_AM_I (</code>
http://asktom.oracle.com/~tkyte/who_called_me/index.html <code> to log each call to the procedures/functions can also be helpful. This method can be used to quickly track down what procedures/functions are still being called (so they could be replaced with a call to the package directly) from the application and to be much more confident during testing that the procedures/functions are no longer needed (so, eventually, they can be dropped entirely)
Something like
CREATE OR REPLACE PROCEDURE LOG_BAD_CALL
IS
PRAGMA AUTONOMOUS_TRANSACTION;
v_bad_calls_row BAD_CALLS%ROWTYPE;
BEGIN
v_bad_calls_row.PROCEDURE_NAME := WHO_AM_I;
<set up other information to track>
INSERT INTO BAD_CALLS VALUES v_bad_calls_row;
commit; -- Autuonomous transactions require a commit
END LOG_BAD_CALL;
Then just add a call to LOG_BAD_CALL in all your procedures/functions, preferably as the very first line after BEGIN
CREATE OR REPLACE PROCEDURE p( a, b, ... )
IS
BEGIN
LOG_BAD_CALL;
pkg.p( a, b, ... );
END p;
November 10, 2004 - 8:41 pm UTC
dbms_trace !! search for it, good debug/diagnostic tool.
dbms_profiler -- code coverage as well.
DBMS_TRACE and DBMS_PROFILER
Bob B, November 10, 2004 - 10:21 pm UTC
Those work great, if the calls are in the database. Harder to find stuff in reports, cron jobs, jsps, asps, etc etc.
November 11, 2004 - 7:51 am UTC
not sure I understand -- who_am_i/who_called_me only work if you are IN the database.
doesn't matter where the call originated (reports, cron jobs, jsps, asps, etc etc tec) -- they "trace" just like this would "trace" (without having had to instrument the code in the first place)
good idea
max, November 11, 2004 - 7:46 am UTC
thanks bob. another good idea. does tom's procedure also work for calls from _external_ sources? case yes, what will i see for calls by ejbs, asps, jsps, ...?
thanks,
max
Invalid Objects
Jagadeswar Vallabhadas, November 22, 2004 - 3:02 am UTC
Hi Tom,
I have an interesting issue with invalid objects, one of our developers executed a pl/sql script which is suppose to create a package (spec and body), unfortunately it could execute only one script which is intended to create body, but created a package speicification with the ame "BODY".
I would like to drop that object but i could not do it as it has got the name BODY. when execute the following commands(different ways to i tried to drop) oracle raised error.
SQL> select object_name||'--'||object_type from user_objects where status='INVALID';
OBJECT_NAME||'--'||OBJECT_TYPE
-----------------------------------------------------------
BODY--PACKAGE
1.
SQL> drop package BODY;
drop package BODY
*
ERROR at line 1:
ORA-04050: invalid or missing procedure, function, or package name
2.
SQL> drop package "BODY";
drop package "BODY"
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [15264], [], [], [], [], [], [], []
3.
SQL> drop package 'BODY';
drop package 'BODY'
*
ERROR at line 1:
ORA-04050: invalid or missing procedure, function, or package name
is there any way to drop this object?
Thanks in advance.
November 22, 2004 - 7:46 am UTC
I cannot reproduce in 7.3, 8i, 9i, or 10g.....
You'd need to help me reproduce. but as with all internal errors, you should really be filing a tar with support too.
ops$tkyte@ORA9IR2> edit
Wrote file afiedt.buf
1 create package "BODY"
2 as
3 x number;
4* end;
5 /
Package created.
ops$tkyte@ORA9IR2> drop package "BODY";
Package dropped.
ops$tkyte@ORA817DEV> create package "BODY"
2 as
3 x number;
4 end;
5 /
Package created.
ops$tkyte@ORA817DEV> drop package "BODY";
Package dropped.
ops$tkyte@ORA734> create package "BODY"
2 as
3 x number;
4 end;
5 /
Package created.
ops$tkyte@ORA734> drop package "BODY";
Package dropped.
Bug
Dave, November 22, 2004 - 8:36 am UTC
metalink note 1781579.8
you have a corrupt dictionary my friend.
Go to your librarian and ask for a new dictionary :-)
November 22, 2004 - 8:48 am UTC
thanks - that looks like it could be it (not sure -- the details above are too sketchy, insufficient to reproduce). It takes multiple creation attempts of a really strange syntax to reproduce.
but if it is, they can contact support, they have a script to fix up the dictionary.
corrupted Dictionary
Jagadeswar Vallabhadas, November 23, 2004 - 12:49 am UTC
Thank you Tom and Dave, Dave is right it is Dictionary corruption. Oracle (bug 1781579) says it is fixed in Oracle 9iR2 but it doesn't seems to be, as we migrated from 8.1.7.4 to Oracle 9i that fix doesn't seems to be working for us as we migrated the corrupted dictionary as well. I am raising a TAR with oracle support.
Thanks
Jagadeswar Vallabhadas
dbms_utility
TP, January 12, 2005 - 9:47 pm UTC
i ran this script :
exec dbms_utility.compile_schema( 'APPS' )
but instead of reducing the invalids the number of invalids are increasing........
SQL> select count(1) from dba_objects where status ='INVALID';
COUNT(1)
----------
3674
SQL> /
COUNT(1)
----------
3743
SQL> /
COUNT(1)
----------
13869
SQL> /
COUNT(1)
----------
16319
SQL> /
COUNT(1)
----------
41674
Kindly Suggest, why this happened and how do i reduce it? as i have executed this on production instance.
Thanks
TP
January 13, 2005 - 8:17 am UTC
it rather brute forces it's way -- it can go up, but would eventually go down
(this is why I never actually do this, things recompile all by themselves).
if you were to have searched for
dbms_utility.compile_schema
on this site, you would find a rather extensive write up and a suggested "replacement" for it that compiles the absolute minimum number of things (and should have the invalid count decreasing always, not increasing).
</code>
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1418403973266 <code>
From Java
Alex, February 09, 2005 - 11:34 am UTC
Tom,
This was a useful thread I didn't know objects compiled when you called them. I tried testing this by doing the following:
1) I went into TOAD, and broke a stored procedure (tried to compile it with a syntax error)
2) I removed the syntax error but did not recompile.
3) Opened a sqlplus session and executed the proc, worked fine and the proc was valid after.
However, when I call the proc from our Java app (go to some window that uses the proc) as step 3 it does not recompile. Is this to be expected? Anything jump out at you or do you need more information?
February 09, 2005 - 2:55 pm UTC
java app is broken? no reason it would not.
but, you don't have any error messages or test case, so it is hard to say why java app is broken....
(java just calls the procedure, it works, i've done it)
Getting Status of Overridden Objects
Richard, March 29, 2005 - 9:25 am UTC
An example package spec:
create or replace package x
as
function x_or(p_parm number) return number;
function x_or(p_parm char) return char;
end x;
/
Its body:
create or replace package body x
as
function x_or(p_parm number) return number
is
l_temp_num number;
--
begin
l_temp_num := 1;
DBMS_OUTPUT.PUT_LINE(l_temp_num);
end x_or;
--
function x_or(p_parm char) return char
is
l_temp_char char(1);
--
begin
l_temp_char := '2';
DBMS_OUTPUT.PUT_LINE(l_temp_char);
end x_or;
end x;
/
How do I interrogate the database to see if the two functions (i.e. the two versions of x_or) are valid?
**Actually, I'm not even sure if two different objects are created**
I'm confused, as (a) I can't see them in USER_OBJECTS and (b) they have the same name (I bet I'm wrong, there, aren't I?).
Many thanks for any help!
March 29, 2005 - 10:33 am UTC
there is just a package spec and body created, the only two objects are the package specification of X and the package body of X
Either the spec is valid or not.
Same with the body.
The indvidual functions are part of the package.
Thank You!
Richard, March 29, 2005 - 11:16 am UTC
Very helpful, thank you!
describing invalid db objects
Shalu Aggarwall, March 31, 2005 - 11:11 am UTC
Hi Tom
I have a small doubt. I was reading Oracle 9i PL/SQL manual and it says that we cannot describe invalid database objects. I tried this in SQL Plus, this is correct for invalid standalone procedures and functions but not for invalid package specs and bodies. I was able to describe invalid packages.
Please help me clarify the concept.
Thanks
Shalu
March 31, 2005 - 11:34 am UTC
really? if the SPEC (which is what gets described, the body doesn't) is invalid:
ops$tkyte@ORA9IR2> create package foo as xxx; end;
2 /
Warning: Package created with compilation errors.
ops$tkyte@ORA9IR2> desc foo
ERROR:
ORA-24372: invalid object for describe
state misbehaviour
space, June 30, 2005 - 12:19 pm UTC
Tom,
I was going through the discussion above and was very useful.
Here is a small issue, I am facing, when ever I change an existing package and recompile. It looks good, compiled
But when the first time after compiling , I try to execute the package via database or VB application, it gives this error below.
Error Number :-4068
Error Description :ORA-04068: existing state of packages (PROD.L.HA.COM) has been discarded
ORA-04063: package body "MM.PACK_USERAPP" has errors
The solution I do now is I rerun the same thing(VB application) or I re-exec the package via db, and then it works fine.
Also again before re-running the VB app I look at the database end, the package looks complied
Please advise!
Thankyou.
June 30, 2005 - 12:27 pm UTC
search for ORA-04068 on this site and read a couple of the articles.
In short
- you have a package
- someone used the package
- the package maintains a state
- you recreate the package, killing their state.
- they (the vb program) needs to be TOLD this very important fact (that
the state they depend on was erased, wiped out, taken away, unsafe to continue)
- ora-04068 is how we do that notification
Makes sense ................Thankyou
space, June 30, 2005 - 1:53 pm UTC
may be we could basically catch that in exception and re-execute it ouselves.
Thankyou Tom.
Have a great weekend
package not re-compiled at execution time
J, July 24, 2005 - 10:41 am UTC
Hi Tom,
Understanding that invalidated package should be re-compiled everytime it is called. However, we do have couple packages will not recompile itself, rather, it gives error message as:
ORA-04068: existing state of packages has been discarded.
ORA-04061: existing state of package body "PKG_NAME" has been invalidated
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
It doesn't happen to every package, but it was happened to one package couple times, and today hits another package. After I manually recompile the package, it works fine.
This package is created under schema1, granted exec privilege to user Oload. After daily SQL load process from Oload account to schema1, one korn shell script execute the package to populate data into 2 other tables in the schema. However, these days we are hitting this error message (not happen everytime though). Since in production for 2 months, it happened 4 times now. All the work are through scheduler.
Do I really need re-compile package after data loading process?
Thanks!
July 24, 2005 - 11:30 am UTC
Ahh, but that package HAD BEEN RECOMPILED (it did not need to be recompiled, you are getting this message simply because SOMEONE HAS RECOMPILED THE PACKAGE)
You are recompiling code, that others have executed.
This code that was recompiled, it maintained a state (package global variables).
You have WIPED OUT THIS STATE, erased it.
Your application needs to know this very very very important fact, that it's brain has in effect been lobotomized. It's memory -- gone.
You do NOT need to recompile the package.
BUT -- it looks like you are doing something that DOES cause the package to be recompiled, and this package maintains a STATE (global variables) and hence they are getting reset.
Search this site for ora-04068, we've had lots of discussions on this. Does your package NEED to maintain a state? If so, they you need to find out what you are doing that causes the package to go invalid and recompile itself.
If not, by all means, get rid of the globals, globals can cause many problems (in code in general)
alternate to eliminate package state?
J, July 25, 2005 - 1:33 am UTC
Tom,
Thanks for your quick reply. I went through couple threads on this error. It seems that that is no other way to eliminate package state unless re-write the package to get rid of all global variables.
I am curious how it will happen in my case:
this package in schema1 are only accessed by user ID1, after data loading process. It seems that package is invalidated after SQL loading. However, ID1 will connect to database after sql loading, calling the package which invoke re-compiling of the package. First, I thought after re-connection, there really shouldn't be any state maintained from previous session; second, if this problem is caused by re-compiling, is there anyway to bypass it by executing directly (sorry for the naive question)? Just couldn't figure out why it only happens to one or two packages so far, as we have at least 10 processes running in same work flow (after sql loader -> execute package). It would require quite a lot effort to get rid of all global variables in all package.
Thanks for your feedback! I really appreciate it!
July 25, 2005 - 7:41 am UTC
Sorry, but this error happens when a package is compiled AFTER the state was already established -- so the timeline you provide does not compute.
If they connect after the package was invalidated
Then
they will not recieve this message
absolutely right
J, July 25, 2005 - 1:34 pm UTC
Tom, you are absolutely right - I checked into developer's code. They are calling same package different procedure in one connection, and error out on the second call. Thanks!
Invalid type in sys schema
Dawar, July 29, 2005 - 4:08 pm UTC
Tom,
DB Version: 10.1.0.3.0
How to fix that?
And why they are coming?
SQL> select object_type, object_name from dba_objects
2 where owner = 'SYS' and status = 'INVALID';
OBJECT_TYPE
-------------------
TYPE BODY
TYPE BODY
TYPE
TYPE BODY
OBJECT_NAME
-----------------------
COMPOSITE_CATEGORY_TYP
CATALOG_TYP
CUSTOMER_TYP
LEAF_CATEGORY_TYP
cheers,
Dawar
July 29, 2005 - 5:50 pm UTC
I have 67 invalid things in my sys schema.
I'm not losing sleep.
things go invalid during patching, upgrades, revokes, alters, many reasons.
And they have the ability to recompile themselves when and if they are needed.
if you like, you can (as sysdba)
alter type NAME compile body;
and
alter type NAME compile;
for the type that is invalid.
Invalid Objects
Deeps, October 21, 2005 - 10:29 am UTC
Hi tom,
We are on 11i10 production environment. Ther seem to be 2 packages (a & b) existing in our database which use remote procedures. On running these 2 packages, they invalid all other packages which use the procedures declard in them (a & b). However these should recompile dynamically and the objects should become valid again which is not the case.
Can you suggest something for this. Thanks.
October 21, 2005 - 10:42 am UTC
I did not follow you.
running a package would not invalidate other packages unless the code in the packages did DDL themselves and it was the commands the packages execute that do the invalidation. Or is there some sort of recompilation of the spec and body of A and B happening because the remote objects they depend on have changed?
and if things go invalid, they do recompile themselves?
Invalid objects
Deeps, October 25, 2005 - 7:38 am UTC
Hi Tom,
I am sorry that you could not follow what was the exact problem in my case, neither do I.
Tell me whenever you run a package, does the whole database recomplile dynamically? If not how do you set it up?
The packages i was talking about, have few procedures like file open & stuff. These are called by several other packages. Its a coincedence that these calling packages get invalid when the called package is run.
Please suggest something.
October 26, 2005 - 7:08 am UTC
when you run a package
and the package was invalid
then oracle will compile the package and anything it NEEDS (not the entire database).
You will need to determine what exactly is making these packages go invalid (DDL, someone is granting on something, someone it recompiling code, *something* is doing this, it just doesn't happen for no reason)
Invalid Objects
Deeps, October 26, 2005 - 8:46 am UTC
Thanks Tom,
I have been trying to figure out the exact reason of what might be the cause. I am going nowhere with this.
Well, Thanks for your time. I'll get back to you once i find a clue.
DBMS_Utility.compile_schema
Krishan Jaglan, January 07, 2006 - 12:55 pm UTC
hi Tom,
I tried this
SQL> connect / as sysdba
Connected.
SQL> execute dbms_utility.compile_schema('CLAS');
BEGIN dbms_utility.compile_schema('CLAS'); END;
*
ERROR at line 1:
ORA-20000: You have insufficient privileges for an object in this schema.
ORA-06512: at "SYS.DBMS_UTILITY", line 422
ORA-06512: at line 1
what is causing this problem, user i am logged in is sys.
DB version 9.2.0.6 RAC on Hp-ux 11.26 Itanium
thanks
January 07, 2006 - 4:41 pm UTC
stop using sys.
don't use sys.
don't use sysdba
use those credentials basically for starting and stopping the database.
log in as the schema you want to compile - if you truly and utterly believe you want to (me, I just let it fix itself, it will. watching dbms_utility compile schema and the like run is like watching paint dry)
Package Body compile
Pradikan, February 22, 2006 - 7:55 am UTC
Hi Tom,
Need your help. We have some packages in our database we get the following error when we try to compile these packages and package body.
SQL> alter package APPS.XXONT_CSI_SUBINV_TRANSFER compile;
alter package APPS.XXONT_CSI_SUBINV_TRANSFER compile
*
ERROR at line 1:
ORA-04021: timeout occurred while waiting to lock object
APPS.XXONT_CSI_SUBINV_TRANSFER
We've retried this many time and still get the same error
Please let me know how to compile them.
Thanks in Advance
Pradikan
February 22, 2006 - 8:45 am UTC
someone is using it.
package compile
pradikan, February 22, 2006 - 8:58 am UTC
Hi Tom,
Thanks for imeediate response. May i ask you If some one else is using this package is there any method / tool by which I can find who's using it.
Thanks in advance
Pradikan
February 22, 2006 - 9:12 am UTC
<quote src=Expert Oracle Database Architecture>
DDL Locks
DDL locks are automatically placed against objects during a DDL operation to protect them from changes by other sessions. For example, if I perform the DDL operation ALTERTABLE T, the table T will have an exclusive DDL lock placed against it, preventing other sessions from getting DDL locks and TM locks on this table. DDL locks are held for the duration of the DDL statement and are released immediately afterward. This is done, in effect, by always wrapping DDL statements in implicit commits (or a commit/rollback pair). For this reason, DDL always commits in Oracle. Every CREATE, ALTER, and so on statement is really executed as shown in this pseudo-code:
Begin
Commit;
DDL-STATEMENT
Commit;
Exception
When others then rollback;
End;
So, DDL will always commit, even if it is unsuccessful. DDL starts by committing—be aware of this. It commits first so that if it has to roll back, it will not roll back your transaction. If you execute DDL, it will make permanent any outstanding work you have performed, even if the DDL is not successful. If you need to execute DDL, but you do not want it to commit your existing transaction, you may use an autonomous transaction.
There are three types of DDL locks:
* Exclusive DDL locks: These prevent other sessions from gaining a DDL lock or TM (DML) lock themselves. This means that you may query a table during a DDL operation, but you may not modify it in any way.
* Share DDL locks: These protect the structure of the referenced object against modification by other sessions, but allow modifications to the data.
* Breakable parse locks: These allow an object, such as a query plan cached in the shared pool, to register its reliance on some other object. If you perform DDL against that object, Oracle will review the list of objects that have registered their dependence and invalidate them. Hence, these locks are “breakable”—they do not prevent the DDL from occurring.
Most DDL takes an exclusive DDL lock. If you issue a statement such as
Alter table t add new_column date;
the table T will be unavailable for modifications during the execution of that statement. The table may be queried using SELECT during this time, but most other operations will be prevented, including all DDL statements. In Oracle, some DDL operations may now take place without DDL locks. For example, I can issue the following:
create index t_idx on t(x) ONLINE;
The ONLINE keyword modifies the method by which the index is actually built. Instead of taking an exclusive DDL lock, preventing modifications of data, Oracle will only attempt to acquire a low-level (mode 2) TM lock on the table. This will effectively prevent other DDL from taking place, but it will allow DML to occur normally. Oracle accomplishes this feat by keeping a record of modifications made to the table during the DDL statement and applying these changes to the new index as it finishes the CREATE. This greatly increases the availability of data.
Other types of DDL take share DDL locks. These are taken out against dependent objects when you create stored, compiled objects, such as procedures and views. For example, if you execute
Create view MyView
as
select *
from emp, dept
where emp.deptno = dept.deptno;
share DDL locks will be placed against both EMP and DEPT, while the CREATE VIEW command is being processed. You can modify the contents of these tables, but you cannot modify their structure.
The last type of DDL lock is a breakable parse lock. When your session parses a statement, a parse lock is taken against every object referenced by that statement. These locks are taken in order to allow the parsed, cached statement to be invalidated (flushed) in the shared pool if a referenced object is dropped or altered in some way.
A view that is invaluable for looking at this information is DBA_DDL_LOCKS. There is no V$ view for you to look at. The DBA_DDL_LOCKS view is built on the more mysterious X$ tables and, by default, it will not be installed in your database. You can install this and other locking views by running the catblock.sql script found in the directory [ORACLE_HOME]/rdbms/admin. This script must be executed as the user SYS in order to succeed. Once you have executed this script, you can run a query against the view. For example, in a single-user database I see the following:
ops$tkyte@ORA10G> select session_id sid, owner, name, type,
2 mode_held held, mode_requested request
3 from dba_ddl_locks;
SID OWNER NAME TYPE HELD REQUEST
---- --------- --------------------- -------------------- ---- ---------
161 SYS DBMS_UTILITY Body Null None
161 SYS DBMS_UTILITY Body Null None
161 SYS DBMS_APPLICATION_INFO Table/Procedure/Type Null None
161 OPS$TKYTE OPS$TKYTE 18 Null None
161 SYS DBMS_OUTPUT Body Null None
161 SYS DATABASE 18 Null None
161 SYS DBMS_UTILITY Table/Procedure/Type Null None
161 SYS DBMS_UTILITY Table/Procedure/Type Null None
161 SYS PLITBLM Table/Procedure/Type Null None
161 SYS DBMS_APPLICATION_INFO Body Null None
161 SYS DBMS_OUTPUT Table/Procedure/Type Null None
11 rows selected.
These are all the objects that my session is “locking.” I have breakable parse locks on a couple of the DBMS_* packages. These are a side effect of using SQL*Plus; it calls DBMS_APPLICATION_INFO, for example. I may see more than one copy of various objects here—this is normal, and it just means I have more than one thing I’m using in the shared pool that references these objects. It is interesting to note that in the view, the OWNER column is not the owner of the lock; rather, it is the owner of the object being locked. This is why you see many SYS rows. SYS owns these packages, but they all belong to my session.
To see a breakable parse lock in action, letÂ’s first create and run a stored procedure, P:
ops$tkyte@ORA10G> create or replace procedure p as begin null; end;
2 /
Procedure created.
ops$tkyte@ORA10G> exec p
PL/SQL procedure successfully completed.
The procedure, P, will now show up in the DBA_DDL_LOCKS view. We have a parse lock on it:
ops$tkyte@ORA10G> select session_id sid, owner, name, type,
2 mode_held held, mode_requested request
3 from dba_ddl_locks
4 /
SID OWNER NAME TYPE HELD REQUEST
---- --------- --------------------- -------------------- ---- ---------
161 OPS$TKYTE P Table/Procedure/Type Null None
161 SYS DBMS_UTILITY Body Null None
161 SYS DBMS_UTILITY Body Null None
...
161 SYS DBMS_OUTPUT Table/Procedure/Type Null None
12 rows selected.
We then recompile our procedure and query the view again:
ops$tkyte@ORA10G> alter procedure p compile;
Procedure altered.
ops$tkyte@ORA10G> select session_id sid, owner, name, type,
2 mode_held held, mode_requested request
3 from dba_ddl_locks
4 /
SID OWNER NAME TYPE HELD REQUEST
---- --------- --------------------- -------------------- ---- ---------
161 SYS DBMS_UTILITY Body Null None
161 SYS DBMS_UTILITY Body Null None
...
161 SYS DBMS_OUTPUT Table/Procedure/Type Null None
11 rows selected.
We find that P is now missing from the view. Our parse lock has been broken.
This view is useful to you, as a developer, when it is found that some piece of code won’t compile in the test or development system—it hangs and eventually times out. This indicates that someone else is using it (actually running it), and you can use this view to see who that might be. The same will happen with GRANTS and other types of DDL against the object. You cannot grant EXECUTE on a procedure that is running, for example. You can use the same method to discover the potential blockers and waiters.
</quote>
Excellent
patel, March 08, 2006 - 6:56 pm UTC
Very useful
Another Question
K Prabala, March 20, 2006 - 1:08 pm UTC
Tom,
I have a question regarding package body recompilation.
In my web appliation. Iam invoking a stored procedure defined in a package at the time my JSP is rendered. The query is run through a datasource object.
If i change and recompile the body of this package the JSP throws a invalid package state error. This error is not repeating if i compile the pkg spec and body. but why should i do that if the pkg spec is not changing?
The package body is compiling with out any errors. Could you please help me on this.
Thank you
March 22, 2006 - 1:22 pm UTC
what is your "invalid package state error", do you mean "existing state of packages has been discarded". the ora-xxxxx msg is important.
Lock Hold on Oracle tables when Recompiling Objects
Satya, March 29, 2006 - 12:55 am UTC
Lock Hold on Oracle tables when Recompiling objects In Schema.Is it True ?.
Please Give Some Advise to us.how to handle this type of situations.
March 29, 2006 - 7:09 am UTC
what do you mean by "lock hold on oracle tables"
Is it ok to apply patch with 12 Invalid Objects?
Dawar Naqvi, April 21, 2006 - 7:04 pm UTC
Tom,
DB Version: 10.1.0.3.0
Is it ok to apply patch with 12 Invalid Objects?
If yes, you may ignore below.
After running catalog.sql & utlrp.sql
I still have 12 objects invalid in my database.
Two packages body in sys schema are invalid as below:
DBMS_STATS_INTERNAL PACKAGE BODY
DBMS_STATS PACKAGE BODY
I would like to recreate above objects?
In 9.2 release
I can recreate the DBMS_STATS package by running the following scripts:
ORACLE_HOME/rdbms/admin/dbmsstat.sql
ORACLE_HOME/rdbms/admin/prvtstas.plb
ORACLE_HOME/rdbms/admin/prvtstat.plb
But this not work with 10.1.0.3.0?
Please let me know. thanks
Regards,
Dawar
April 22, 2006 - 3:06 pm UTC
having dbms_stats invalid is not good, what is the reason (use the show errors command or query dba_errors directly)
dbms_utility,
A reader, April 26, 2006 - 3:03 pm UTC
I am getting following error in one of my 9.2 database.
Message: [Oracle][ODBC][Ora]ORA-04068: existing state of packages has been discarded
ORA-04063: has errors
ORA-04063: package body "SYS.DBMS_UTILITY" has err
Do you know why and hwo to fix it?
I checked the status in the dba_objects table and the status is INVALID for the package body.
The last_ddl_time and created date are 18th Jan.
Thanks,
April 27, 2006 - 8:10 am UTC
show errors package body dbms_utility
when logged in as sys, shows what exactly?
DBMS_STATS & DBMS_STATS_INTERNAL PACKAGE BODY invalid
Dawar, April 26, 2006 - 3:21 pm UTC
Tom,
DB Version: 10.1.0.3.0
After running catproc.sql & utlrp.sql.
After running catproc, I got following message in the end.
PL/SQL procedure successfully completed.
BEGIN
*
ERROR at line 1:
ORA-04063: package body "SYS.DBMS_STATS" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
ORA-06512: at "SYS.DBMS_REGISTRY", line 706
ORA-06512: at "SYS.DBMS_REGISTRY", line 755
ORA-06512: at line 2
308 Objects are invalid.
I have run utlrp.sql.
8 ojects were invalid as below.
OWNER OBJECT_NAME OBJECT_TYPE
-------------------------------------------------------
PUBLIC DBMS_XDBUTIL_INT SYNONYM
SYS DBMS_STATS PACKAGE BODY
SYS DBMS_STATS_INTERNAL PACKAGE BODY
XDB XDB$PATCHUPSCHEMA PROCEDURE
XDB DBMS_XDBUTIL_INT PACKAGE BODY
XDB DBMS_XDBZ0 PACKAGE BODY
XDB SETMODFLG PROCEDURE
XDB DBMS_XDBUTIL_INT PACKAGE
Then I compiled six objects manulally. (XDB Schema Objects)
I still have 2 sys.objects invalid in my database.
Two packages body in sys schema are still invalid as below:
DBMS_STATS_INTERNAL PACKAGE BODY
DBMS_STATS PACKAGE BODY
****************************************************
SQL> @dbmsstat.sql
Package created.
Synonym created.
Grant succeeded.
create role gather_system_statistics
*
ERROR at line 1:
ORA-01921: role name 'GATHER_SYSTEM_STATISTICS' conflicts with another user or
role name
Grant succeeded.
Grant succeeded.
Library created.
*************************************************
SQL> ALTER PACKAGE DBMS_STATS COMPILE;
Warning: Package altered with compilation errors.
SQL> SHOW ERROR
No errors.
SQL> ALTER PACKAGE BODY DBMS_STATS COMPILE;
ALTER PACKAGE BODY DBMS_STATS COMPILE
*
ERROR at line 1:
ORA-00922: missing or invalid option
SQL> SHOW ERROR
No errors.
Regards,
Dawar
April 27, 2006 - 8:11 am UTC
please utilize support for this.
connect to very above
Dawar, April 26, 2006 - 4:34 pm UTC
Here is the out put from
select owner, name, text
from dba_errors
all objects are Package Body.
Only ImportExport have JAVA SOURCE type.
OWNER NAME
-----------------------------------------
SYS DBMS_STATS
SYS DBMS_STATS
SYS DBMS_STATS
SYS DBMS_STATS
SYS DBMS_STATS
SYS DBMS_STATS
SYS DBMS_STATS
SYS DBMS_STATS
SYS DBMS_STATS
SYS DBMS_STATS
SYS DBMS_STATS
SYS DBMS_STATS
SYS DBMS_STATS_INTERNAL
SYS DBMS_STATS_INTERNAL
FLOWS_010500 ImportExport
FLOWS_010500 ImportExport
TEXT
----------------------------------------------------
PL/SQL: Statement ignored
PLS-00302: component 'DEBUG_FLAGS' must be declared
PL/SQL: Statement ignored
PLS-00302: component 'DEBUG_FLAGS' must be declared
PL/SQL: Statement ignored
PLS-00302: component 'IS_URGENT_ERROR' must be declared
PL/SQL: Statement ignored
PLS-00302: component 'IS_URGENT_ERROR' must be declared
PL/SQL: Statement ignored
PLS-00302: component 'TRACE_ERROR' must be declared
PL/SQL: Item ignored
PLS-00302: component 'DSC_DEBUG_AUTO_TLIST_ONLY' must be declared
PL/SQL: Statement ignored
PLS-00302: component 'DEBUG_FLAGS' must be declared
Note: Recompile with -deprecation for details.
Note: ImportExport uses or overrides a deprecated API.
Dawar
Issue has been solved.
Dawar, April 26, 2006 - 7:02 pm UTC
Tom,
Issue has been solved.
Here what I did? thanks for Oracle Support
Enter user-name: sys as sysdba
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 788529152 bytes
Fixed Size 781200 bytes
Variable Size 576984176 bytes
Database Buffers 209715200 bytes
Redo Buffers 1048576 bytes
Database mounted.
Database opened.
SQL> @prvtstas.plb
Package created.
Package body created.
SQL> @prvtstat.plb
Package body created.
No errors.
PL/SQL procedure successfully completed.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 788529152 bytes
Fixed Size 781200 bytes
Variable Size 576984176 bytes
Database Buffers 209715200 bytes
Redo Buffers 1048576 bytes
Database mounted.
Database opened.
After that I have new 7 objects were invalid but I was able to compile objects manually.
Dawar
A reader, May 08, 2006 - 10:15 am UTC
Tom,
We noticed the following package compilation errors in our log file.
Error: PACKAGE TIS_FTP is invalid.
Error: PACKAGE BODY TIS_FTP is invalid.
Error: PACKAGE BODY TIS_SOAP is invalid.
Error: PACKAGE BODY TIS_UTIL is invalid.
Due to dependencies, some more application packages also didn't compile but I think this is the root cause. I tried recompiling the packages and schema without success. The DBA took care of the problem. I am not sure what the DBA did other than recompiling the packages and/or schema I am not sure why they occur? The DBA fixed the problem. But now, the DBA asks us to find why these problems occur. Any idea?
May 08, 2006 - 10:52 am UTC
umm, sort of need to know what the DBA did to FIX it (as they should actually be able to tell you "why it is happening" - since they, well, fixed it???)
What did they do to fix it.
And what errors (user_errors, show errors, whatever) were reported against the packages before they fixed it.
A reader, May 08, 2006 - 11:05 am UTC
Tom,
I tried recompiling them and sent the show errors to DBA before he fixed them. Here are some. I am sure the DBA fixed these errors before recompiling packages and dependencies. These identifiers can be seen in the package specifications. Was this a permissions problem? I need to find what the DBA did to fix these. He is asking us to find why the objects are invalid.
PACKAGE BODY PLS-00201: identifier 'DBMS_LOB' must be declared
PACKAGE BODY PLS-00201: identifier 'UTL_HTTP' must be declared
PACKAGE BODY PLS-00201: identifier 'UTL_TCP' must be declared
PACKAGE BODY PLS-00201: identifier 'UTL_SMTP' must be declared
May 08, 2006 - 1:28 pm UTC
the DBA issued the correct and proper grants.
The objects are invalid because the DBA has not granted access to these packages.
A reader, May 08, 2006 - 11:14 am UTC
Tom,
This is what the DBA said:
"I would be in supported of automatic notification that there are invalid objects, but not automated recompiling. You need to know why objects are invalid, rectify the problem, then recompile."
I am not sure if he's right. I think in a production enviroment, they should be recompiled first before notifying.
May 08, 2006 - 1:31 pm UTC
no you don't, you usually just need to RUN THEM and they recompile all by themselves.
It should only be during an install of the packages for the first time that you need to do this (find out "why"). Normally, they will recompile themselves successfully - unless of course you CHANGE SOMETHING in the database (like a GRANT for example)
the dba cannot stop us (oracle) from doing the automagic recompile, it just happens.
what is wrong in this ?
parag jayant patankar, May 10, 2006 - 7:13 am UTC
Hi Tom,
I have tried following on Oracle 9.2 database
1 create or replace procedure p
2 as
3 begin
4 for x in ( select * from t1, t2, t3 )
5 loop
6 null;
7 end loop;
8* end;
6:55:56 SQL> /
Procedure created.
16:57:13 SQL> SELECT owner,
16:57:20 2 object_type,
16:57:20 3 object_name,
16:57:20 4 status
16:57:20 5 FROM dba_objects
16:57:20 6 WHERE status = 'INVALID'
16:57:20 7 and owner = 'PARAG'
16:57:20 8 ORDER BY owner, object_type, object_name;
OWNER OBJECT_TYPE OBJECT_NAME STATUS
------------------------------ ------------------ ------------------------------ -------
PARAG PROCEDURE P INVALID
PARAG VIEW EMP_MW INVALID
16:57:21 SQL> exec dbms_utility.compile_schema( 'PARAG' );
PL/SQL procedure successfully completed.
16:57:32 SQL> show errors
No errors.
16:57:35 SQL> SELECT owner,
16:57:41 2 object_type,
16:57:41 3 object_name,
16:57:41 4 status
16:57:41 5 FROM dba_objects
16:57:41 6 WHERE status = 'INVALID'
16:57:41 7 and owner = 'PARAG'
16:57:41 8 ORDER BY owner, object_type, object_name;
OWNER OBJECT_TYPE OBJECT_NAME STATUS
------------------------------ ------------------ ------------------------------ -------
PARAG PROCEDURE P INVALID
PARAG VIEW EMP_MW INVALID
Why this procedure not getting compiled by dbms_utility ? If I do compilation manually it get compiled without any problem
16:57:42 SQL> alter procedure p compile;
Procedure altered.
16:58:54 SQL> SELECT owner,
16:58:59 2 object_type,
16:58:59 3 object_name,
16:58:59 4 status
16:58:59 5 FROM dba_objects
16:58:59 6 WHERE status = 'INVALID'
16:58:59 7 and owner = 'PARAG'
16:58:59 8 ORDER BY owner, object_type, object_name;
OWNER OBJECT_TYPE OBJECT_NAME STATUS
------------------------------ ------------------ ------------------------------ -------
PARAG VIEW EMP_MW INVALID
Kindly tell me why procedure p not getting compiled by dbms_utility ?
thanks & regards
pjp
May 10, 2006 - 9:09 am UTC
it probably is - but goes invalid because of something else getting compiled again later, dbms_utility is sort of "not super smart" that way sometimes.
I never really bother with it, things compile themselves.
without an entire example to work through, I'm not going to try to guess what might have happened here. no show errors or anything (eg: cannot imagine why the procedure is invalid in the first place if t1..t3 are just simple tables)
Package State and Drop Partition DDL
Lalit, August 11, 2006 - 8:12 am UTC
Tom,
As we know, a DDL on a table will cause the package state to become invalid.
My query is that as a part of regular housekeeping, when the partitions are getting dropped, a DDL is executed. If a dependent package is already running, what would occur in that scenario? (e.g. Will the DDL fail? Will the existigng run of the package get effected, hung? etc..)
Further, is it possible that some of the users might get an error that the existing state of package is invalid. The user executing the package do not own the package. It only has the execute privilege.
August 11, 2006 - 11:12 am UTC
currently, the package would become invalidated and the state discarded - if it was directly dependent on that object.
if you truncate the partitions instead of dropping them - it will not become invalidated (if you can save them up for a safe time to drop)
Invalid objects in SYS schema
A reader, August 13, 2006 - 9:02 am UTC
Hello Tom,
Before a week we got a strange problem in one of our production databases.
Developers were getting "DBMS_UTILITY package invalid" error, on one of the production databases which is running on HP-UX, 9.2.0.6. I investigated into the matter and found that there were more than 200 objects invalid in SYS schema. Please note that no process was lockng the package when I tried to compile.
When I tried to compile all invalid objects in the datbase using UTLRP.SQL, the script hung while compileing DBMS_UTILITY package. For resolving the problem I had to shutdown the database and had to bounce it to use UTLRP to compile all invalid objects in the database. after restarting the database UTLRP.SQL script compiled alll the invalid objects and everything started running fine. Unfortunately I could not reproduce the problem again.
Do you have any idea what might have gone wrong? why the DBMS_UTILITY package was not compiling though nothing was locking it? I could not find anything in alert log or anywhere!
Any help would be helpful.
Thanks,
August 13, 2006 - 9:31 am UTC
(there was something preventing it from being compiled - else it would have)
After the fact, this is going to be one of those things where I could like a couple of possible reasons, none of which would be verifiable (since the problem no longer exists).
Invalid objects in SYS schema
A reader, August 13, 2006 - 10:06 am UTC
Thanks Tom,
I was expecting such answer.
As you said the problem not more exists so I can not invistigate the reason. I was just trying to know if you have come accross such kind of problem.
Just another question, any idea which could make SYS objects invalid in 9i?
Once again, thanks for all your help. Keep going.
Cheers,
August 13, 2006 - 3:43 pm UTC
hundreds of things...
a patch
a revoke
a compile by a dba of something else.
.........
DDL operation and package states
Lalit, August 14, 2006 - 11:29 am UTC
Thans for the confirmation Tom.
Can you suggest /advise me that will it be ok to have a nightly DBA houskeeping job running(i.e. drop partitions/add partitions ) when the calls are being made to the package which depends on these tables? When a "quiet" period is not availaible, how do you handle these housekeepng requirements?
Maybe I am going overboard, but if a package is being excuted and in the middle of the call, the dependenet table is altered, what would happen to the existing call ? Will it complete successfully. It would seem that since the state cant be maintained the package run will fail. Am i correct? Any suggestions (if this is correct) to workarond this.....
August 14, 2006 - 12:34 pm UTC
the existing call will complete, pending calls will wait until existing calls are done.
Note: in 10g, dropping a partition will not invalidate, this applies to 9i and before.
You can test with this to see the effects, you need three sqlplus sessions:
CREATE TABLE t
(
dt date,
x int,
y varchar2(25)
)
PARTITION BY RANGE (dt)
(
PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) ,
PARTITION junk VALUES LESS THAN (MAXVALUE)
)
/
insert into t
select to_date( '12-mar-2003', 'dd-mon-yyyy' )+mod(rownum,3), rownum, rownum
from all_users;
create or replace package my_pkg
as
procedure p( p_sleep in boolean default false );
end;
/
create or replace package body my_pkg
as
procedure p( p_sleep in boolean default false )
is
begin
for x in (select * from t)
loop
null;
end loop;
if ( p_sleep )
then
dbms_lock.sleep(20);
end if;
end;
end;
/
set echo off
prompt in another session execute:
prompt alter table t drop partition part1;;
prompt AFTER this procedure starts (after pause)
prompt
prompt in yet another another session execute:
prompt exec my_pkg.p;;
prompt AFTER this procedure starts
pause
prompt do it now...
set echo on
exec my_pkg.p( TRUE );
DDL for partition drop and Invalid packages
Lalit, August 16, 2006 - 9:55 am UTC
Thanx again Tom for calrifying things.
But I doubt whether the statement " Dropping partiton doesnt invalidate procedures/packages in 10g " holds true. I tested this with 10gR1, maybe need to go up to R2.....
August 16, 2006 - 10:38 am UTC
Yes, 10gR2
ops$tkyte%ORA10GR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(25)
6 )
7 PARTITION BY RANGE (dt)
8 (
9 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) , 10 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy')) , 11 PARTITION junk VALUES LESS THAN (MAXVALUE)
12 )
13 /
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace procedure p
2 as
3 begin
4 for x in ( select * from t )
5 loop
6 null;
7 end loop;
8 end;
9 /
Procedure created.
ops$tkyte%ORA10GR2> create or replace view vw as select * from t;
View created.
ops$tkyte%ORA10GR2> select object_name, status from user_objects where object_name in ( 'P', 'VW' );
OBJECT_NAME STATUS
------------------------------ -------
P VALID
VW VALID
ops$tkyte%ORA10GR2> alter table t drop partition part1;
Table altered.
ops$tkyte%ORA10GR2> select object_name, status from user_objects where object_name in ( 'P', 'VW' );
OBJECT_NAME STATUS
------------------------------ -------
P VALID
VW VALID
using dbms_utility.compile_schema in a job
Jean, September 22, 2006 - 11:55 am UTC
We had found that running our nightly job sometimes failed because of invalid objects - and did not automatically recompile them. So, In 9i we had added dbms_utility.compile_schema at the front of our job, which allowed the job to run successfully. Now that we are (finally) moving to 10g, we've found that the job again fails with invalid package errors. It does NOT fail if we run the dbms_utility.compile_schema manually immediately prior to starting the job, but does if the dbms_utility.compile_schema is within the job. Any ideas why?
September 22, 2006 - 3:59 pm UTC
not unless you help me reproduce the situation.
objects referenced by a procedure should be "automagically" FIXED simply by running the procedure, I've NEVER called compile schema for that.
problem compiling views automatically
William Moffatt, October 10, 2006 - 5:37 am UTC
Hi tom,
I'm having a similar problem to jean above, I think:
I haven't been able to reproduce the problem with a small example, so I'll try and describe what's happening - at the moment I don't even know where to start looking for a solution (or at least reason), so I am hoping that you can point me in the right direction.
We have a database in production that is exported/imported to our test enviroment nightly. As far as I am aware the export/import works fine without error, however three of the views we use remain invalid after the process, and the only way we can get them to work is to compile them by hand in the right order (each depends on the next, presumably two are failing simply because of the "base" view); there's nothing wrong with any of the views, as far as I can tell, and if you compile them by hand they will compile and work first time.
For example:
10:04:48 moff@db> select * from view1
10:04:55 2 /
select * from view1
*
ERROR at line 1:
ORA-04063: view "sch.view1" has errors
Elapsed: 00:00:00.00
10:04:59 moff@db> alter view view1 compile;
View altered.
Elapsed: 00:00:00.05
10:05:46 moff@db> select * from view1;
bselect * from view1
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
I interrupted the select once it was obvious the view had compiled.
One of our dbas has had a look at the problem; if he selects the view in OEM (version 9.2 I think) and tries to compile the view manually it replies with "view has errors", saying that line 0 is invalid; compiling the view in TOAD or in sqlplus works without errors.
We're stumped, and don't know where to go from here - any ideas?
There is obviously a lot of detail missing here, but I don't really know what might be relevant and hopefully you've come across something similar before - let me know if you need any more data in particular.
The database is 8.1.7.4.
thank you,
October 10, 2006 - 8:08 am UTC
please utilize support, no i have not heard of anything similar, not ringing any bells.
invalid synonyms
A reader, October 10, 2006 - 11:27 am UTC
Will the answer suggested for the original quuestion also take care of the invalid publica synonyms.
Further, what is the reason for a public synonym to get invalid, and what is the best way to get them back to a VALID state?
Thanks
dbms_utility.compile_schema Versus utl_recomp.recomp_serial
John, November 03, 2006 - 2:00 pm UTC
After reading the questions/responses in this thread I started looking into using dbms_utility.compile_schema instead of our custom script to re-compile invalid objects. When I used dbms_utility.compile_schema I still had invalid objects (although they validate when I access them), but when I use utl_recomp.recomp_serial all objects are valid. See my test cases and the link below for more info.
https://asktom.oracle.com/Misc/oramag/on-joins-and-query-plans.html
My Example:
SQL> select substr(object_name,1,30) as obj_name, object_type
2 from user_objects
3 where status = 'INVALID';
TYP_ABSS_ACCT_CLASS_OBJ TYPE BODY
.
V_LSR_SETA_SUM VIEW
54 rows selected.
SQL> exec dbms_utility.compile_schema('CCAR',TRUE);
PL/SQL procedure successfully completed.
SQL> select substr(object_name,1,30) as obj_name, object_type
2 from user_objects
3 where status = 'INVALID';
TYP_ABSS_ACCT_CLASS_OBJ TYPE BODY
.
V_LSR_SETA_SUM VIEW
54 rows selected.
SQL> connect sys@ccar25 as sysdba
Enter password: *******
Connected.
SQL> exec utl_recomp.recomp_serial('CCAR');
PL/SQL procedure successfully completed.
SQL> select substr(object_name,1,30) as obj_name, object_type
2 from all_objects
3 where status = 'INVALID' and owner = 'CCAR';
no rows selected
How to check if DDL lock freed from object?
A reader, September 10, 2007 - 12:35 am UTC
Dear Tom.
Let say I can not grant select on T1 to Somebody because of DDL lock on it.
How I can do, that Oracle autamotically run that grant statement when lock is freed?
Of couerse in Pl/SQL
Thanks in advance.
September 12, 2007 - 10:47 am UTC
before 11g, you will have to retry the DDL operation until it succeeds
Alen Oblak, September 12, 2007 - 2:47 pm UTC
What about in 11g? I searched the "New features guide" and "SQL Referene" in the 11g documentation, but found nothing. So, is there a special kind of "deffered" grant or is the lock on the granting object no longer needed?
September 15, 2007 - 7:07 pm UTC
by default, it does not work that way, what was your original set of create statements?
ops$tkyte%ORA9IR2> CREATE TABLE t
2 (
3 dt date,
4 x int,
5 y varchar2(30)
6 )
7 tablespace users
8 PARTITION BY RANGE (dt)
9 -- subpartition by hash(x)
10 (
11 PARTITION part1 VALUES LESS THAN (to_date('13-mar-2003','dd-mon-yyyy')) ,
12 PARTITION part2 VALUES LESS THAN (to_date('14-mar-2003','dd-mon-yyyy'))
13 )
14 /
Table created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> create index t_idx on t(x) local tablespace tools;
Index created.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> alter table t add partition part3 VALUES LESS THAN (to_date('15-mar-2003','dd-mon-yyyy'))
2 /
Table altered.
ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> select partition_name, tablespace_name from user_tab_partitions where table_name = 'T';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PART1 USERS
PART2 USERS
PART3 USERS
ops$tkyte%ORA9IR2> select partition_name, tablespace_name from user_ind_partitions where index_name = 'T_IDX';
PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
PART1 TOOLS
PART2 TOOLS
PART3 TOOLS
To Alen Oblak
Oscar de la Torre, September 13, 2007 - 3:21 am UTC
State of a package in different schemas
Marat, December 29, 2007 - 4:03 am UTC
Dear Tom,
thank you for your responses.
I have a question about dependencied among different schemas in the same database (Oracle 8.1.7.4.0). Assume the following package in the schema S1:
CREATE OR REPLACE package test_dep1
as
n1 number:=0;
procedure p1;
end;
/
CREATE OR REPLACE package body test_dep1
as
procedure p1
is
begin
dbms_output.put_line('test_dep1');
end;
end;
/
grant execute on test_dep1 to S2;
exec test_dep1.p1;
Then we call the procedure from a different user (S2):
exec S1.test_dep1.p1;
Everything goes normal yet. Then we recreate the package body under S1:
CREATE OR REPLACE package body test_dep1
as
procedure p1
is
begin
dbms_output.put_line('test_dep1');
end;
end;
/
And now the question: if we call test_dep1.p1 under
S1 it recomiles automatically, without an exception, but if we call test_dep1.p1 under
S2 we receive:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "S1.TEST_DEP1" has been invalidated
ORA-04065: not executed, altered or dropped package body "S1.TEST_DEP1"
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
so, why under S1 we don't get an exception OR why under S2 we do?
Thank you.
Same problem with non-owner user gettting invalidates
Guy Liesch, February 01, 2008 - 2:43 pm UTC
Found the previous entry is exactly a problem we are experiencing. The package body is recompiled and calls are invalid for the non-owning users. Any insight would be helpful.
Invalid SYS objects
Jarod, July 30, 2008 - 2:17 pm UTC
I am trying to create the repository and db control using emca for a 10.2.0.3.0 database which is a clone of another database. However, when I tried to create the repository I am receiving "CONFIG: ORA-24344: success with compilation error"
I discovered that some of my SYS owned objects are invalid. I ran the /rdbms/admin/utlrp.sql script as well as manually compiled these object which did validate most, but I still have some that return "Warning: Package Body altered with compilation errors." (I don't have any invalid DBSNMP objects) These objects are:
DBMS_DDL PACKAGE BODY
DBMS_TRACE PACKAGE BODY
DBMS_PLUGTS PACKAGE BODY
DBMS_RCVMAN PACKAGE BODY
DBMS_REPUTIL PACKAGE BODY
DBMS_REPCAT_UTL PACKAGE BODY
DBMS_REPCAT_CONF PACKAGE BODY
DBMS_REPCAT_MAS PACKAGE BODY
DBMS_PROFILER PACKAGE BODY
DBMS_EXTENDED_TTS_CHECKS PACKAGE BODY
DBMS_STREAMS_TBS_INT_INVOK PACKAGE BODY
DBMS_STREAMS_TABLESPACE_ADM PACKAGE BODY
DBMS_APPLY_ADM_LOCAL PACKAGE BODY
ALL_REPOBJECT VIEW
ALL_REPPROP VIEW
ALL_REPRESOLUTION VIEW
ALL_REPCOLUMN_GROUP VIEW
_ALL_REPCOLUMN_GROUP VIEW
ALL_REPKEY_COLUMNS VIEW
When I do a "show errors" it returns:
SQL> show errors
Errors for PACKAGE BODY SYS.DBMS_APPLY_ADM_LOCAL:
LINE/COL ERROR
-------- -----------------------------------------------------------------
4/13 PLS-00323: subprogram or cursor 'CREATE_APPLY' is declared in a
package specification and must be defined in the package body
Could this be a problem from a patch not yet installed? I appreciate your help.
BTW, I enjoyed your presentation over the new features of 11g at the OKCOUG last year.
August 02, 2008 - 5:40 pm UTC
please utilize support, yes, your installed set of packages is botched up. It would seem the specs and bodies do not match at this point - eg: a new spec has been installed, the new body has not.
invalid object
A reader, January 14, 2009 - 8:01 pm UTC
Tom:
Do you ahve any idea what might cause an object to become invalid without table or index change.
We had similar issue with Powerbuidler like you described above.
The procedure had no errors. Every once in a while the procedure becomes invalid and the PB call fails.
January 16, 2009 - 5:02 pm UTC
a grant was likely revoked. A dependent object (a procedure your procedure called) went invalid (because a table change or whatever).
It doesn't have to be a table YOU are dependent on, just a table that something you are dependent on is dependent on.
invalid
A reader, January 17, 2009 - 11:23 pm UTC
Tom:
I usually change the tables and i did not do anything.
In order to avoid this happening in the future and since PB does not compile the procedure would you write a trigger that sends an email whenver a specific object becomes invalid OR whenever anyone changes anything in the database schema.
January 19, 2009 - 8:40 pm UTC
"I usually"
not
"I am the only person on the planet that could do anything in the database - I'm the only one with an account and the only user on the system"
therefore, someone did something.
PB doesn't have to compile anything? PB just needs to try to run the procedure, WE compile it. We automagically compile stuff.
You could enable auditing so as to be able to retrospectively figure out what changed, sure.. not sure I'd send email - you might be surprised how often it happens and how annoying it would be.
Girish, May 05, 2009 - 6:23 am UTC
Hi Tom,
Explanations above cleared some of my doubts but still I have one case which I would like to put infront of you.
One package was invalidated a week back ( last_ddl_time in user_objects ).But when it was executed a day back then it took long time ( compared to its usual time of completion ) to complete and its status still is INVALID.
I could not understand this behaviour
Thanks ,
Girish
May 11, 2009 - 8:30 am UTC
if it actually ran - it's status was VALID at some point - the point in time it was running (invalid code - it won't run unless it can be made valid)
therefore, something made it go invalid again if it successfully ran.
object does not longer exists
Bart, July 27, 2009 - 7:31 am UTC
I dare to question that objects do not get invalidated on drop partition in 10gR2 :-)
Types (type bodies) are definitely forgotten in this change - they GET invalid (but as in previous releases almost everything -related to the partitioned table- gets invalid, we see indeed an improvement)
( We use OO-ish types extensively and I don't like it - but let's keep to the point )
We get a
ORA-08103: object no longer exists
ORA-08103: object no longer exists
ORA-06512: at "ADB_OWNER.MY_PKG", line 6
ORA-06512: at line 3
on dropping partitions. Difference is that I populate the table (to make it slightly more like a real world example... )
( as a workaround we will truncate partitions )
10gR2 - 10.2.0.3 (compatible 9.2.0)
drop table t
/
CREATE TABLE t
(
dt date,
x int,
y varchar2(25),
xml xmltype
)
PARTITION BY RANGE (dt)
(
PARTITION part1 VALUES LESS THAN (to_date('27-jul-2009','dd-mon-yyyy'))
, PARTITION
part2 VALUES LESS THAN (to_date('01-aug-2009','dd-mon-yyyy')) , PARTITION junk VALUES LESS
THAN (MAXVALUE)
)
/
insert into t
select sysdate - 5 + dbms_random.value * 15
, round( dbms_random.value * 100 )
, chr( trunc ( dbms_random.value * 256 ) )
, xmltype('<a/>')
from user_objects
/
commit;
create or replace package my_pkg
as
procedure p( p_sleep in boolean default false );
end my_pkg;
/
create or replace package body my_pkg
as
procedure p( p_sleep in boolean default false )
is
begin
for x in (select * from t)
loop
null;
end loop;
/*
if ( p_sleep )
then
dbms_lock.sleep(20);
end if;
*/
end p;
end my_pkg;
/
analyze table t compute statistics
/
create or replace procedure my_proc
is
l_max_date t.dt%type;
begin
select max(dt)
into l_max_date
from t;
end my_proc;
/
begin
my_pkg.p;
end;
/
alter table t drop partition part1
/
begin
my_pkg.p;
end;
/
Take back previous post
Bart, August 20, 2009 - 8:57 am UTC
I know what caused the ORA error 'object doesn't exist'. I was using a 9i client instead of 10g. In 10g client this error doesn't occur.
Object types still get invalid in 10g when they depend directly on a partitioned table where partitions get dropped.
Sorry for the inconvenience.
invalid objects
A reader, August 25, 2009 - 7:30 pm UTC
Tom:
I need to set a job that compiles invalid objects on a nightly basis because of powerbuilder client and send me a ACK email.
Shall i set a pl/sql procedure and do it only for invalid objects by type OR just run one command and compile the whole schema.
is there any harm of compiling the whole schmea every night?
August 25, 2009 - 8:44 pm UTC
you mean in the year 2009 powerbuilder still has the same defects it had when it was relevant in 1995? ouch.
how do you propose to compile the entire schema each night?
compile
A reader, August 26, 2009 - 11:28 am UTC
Tom:
I will either do this statement to compile everything (easier)
dbms_utility.compile_schema('SCHEMA_NAME');
or this for only invalid objects
BEGIN
FOR cur_rec IN (SELECT owner,
object_name,
object_type,
DECODE(object_type, 'PACKAGE', 1,
'PACKAGE BODY', 2, 2) AS recompile_order
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY')
AND status != 'VALID'
ORDER BY 4)
LOOP
BEGIN
IF cur_rec.object_type = 'PACKAGE' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||
' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
ElSIF cur_rec.object_type = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE 'ALTER PACKAGE "' || cur_rec.owner ||
'"."' || cur_rec.object_name || '" COMPILE BODY';
ELSIF ....same thing for FUNCTION, TRIGGER, VIEW
END IF;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' || cur_rec.owner ||
' : ' || cur_rec.object_name);
END;
END LOOP;
END;
/
August 26, 2009 - 7:34 pm UTC
try it, tell me if it works :)
(hint, it doesn't, meaning - either of your approaches can and will leave invalid objects)
...EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.put_line(cur_rec.object_type || ' : ' ||
cur_rec.owner ||
' : ' || cur_rec.object_name);
END;
...
I hate your code, stop that, do not ever do that, just stop.
compile
A reader, August 26, 2009 - 9:32 pm UTC
August 28, 2009 - 4:17 pm UTC
and it'll compile - but it does not guarantee the schema will be 100% valid afterwards.
in older releases (like 9i) altering a procedure and compiling it would invalidate the the packages that invoked it.
say you have a procedure p
and a package my_pkg
my_pkg calls p
p is dependent on table t
you alter table t, p and hence my_pkg go invalid.
If you compile the package, that will implicitly compile the procedure, which will then be compiled by you after the package - which will invalidate the package - which you won't compile again because you already did.
I've always been against this "recompile the schema" bit - because we do it implicitly - you can do whatever you want as long as you are aware "it might not return everything to a valid state"
powerbuilder has had this deficiency for years - as long as I've been here at Oracle, I'm a bit surprised they never fixed it.
oracle-base is a very good site, but it is not an Oracle (the company) site, it is hosted by Tim Hall. That is just FYI, all good information there.
Well now
DJB, August 27, 2009 - 7:28 am UTC
How about using utlrp.sql to compile when required ? That's what is recommended after applying CPU's.
August 28, 2009 - 4:48 pm UTC
and I still think compiling stuff is a waste of energy, it'll do it itself. powerbuilder is deficient in this regard - every other tool on the planet can deal with this.
utlrp is a sql script, would not be easier to run as a job
compile
A reader, August 29, 2009 - 1:45 pm UTC
Tom:
We cant have sybase fix their product.
So basically you can compile but only in a certain order that accounts for dependents.
By your example, if table T changes, the script has to compile MY_PKG only or compile P and then MY_PKG.
correct. I think you also mentioned you have a script that does that in expert one one one booK?
August 29, 2009 - 7:22 pm UTC
... We cant have sybase fix their product. ...
why not? umm, people do it to us all of the time?
... I think you also mentioned you have a script that does that in expert
one one one booK? ...
I do, but I still think "waste of our time", but do it anyway you want really, none of them will be perfect, nothing will ensure there won't be an invalid something after all is said and done.
Could you expand on this ?
DJB, September 01, 2009 - 6:28 am UTC
'utlrp is a sql script, would not be easier to run as a job'.
Tom, not quite sure what you mean by this. Could you expand please ? Also, maybe I'm missing something but I've never seen an invalid object recompiled unless someone logs in to do it, either through the GUI or by running a regular batch job which checks and then recompiles invalid objects
Thanks for the quick response
DJB, September 01, 2009 - 11:55 am UTC
However, I know what utlrp is. It was this bit I didn't understand - 'would not be easier to run as a job'.
In addition did you see my comment on objects beig recompiled when accessed if they are invalid >
September 01, 2009 - 5:39 pm UTC
it is a sql script, you would have to run sqlplus from the job, you would have to get the credentials to sqlplus, you would have to parse the log file to see if it worked, etc. It would not be easy to run and make sense of a script.
Yes, I know that objects are recompiled when accessed HOWEVER, powerbuilder cannot build a call string to invoke an invalid stored procedure - every other tool on the planet can, just not powerbuilder.
Reason for Invalids
sosu, September 01, 2009 - 6:05 pm UTC
Tom,
Not sure if you would answer to my question..but you are my last resort,if not I will give up.
There was a change in our production system which drove the application to break.While tracing back, we found there were invalid objects in Oracle.BUT there were zero invalids when the system went live.We have auditing turned on.Once I cleared the invalids, the application started working.
So the problem was with INVALIDS.
And I am trying to answer how they got INVALID and when?
Appreciate any help on this.
Thanks
September 01, 2009 - 6:07 pm UTC
... So the problem was with INVALIDS. ...
what was invalid please? invalidate objects would not "break" things.
define your terms, be more descriptive.
A reader, September 02, 2009 - 1:30 pm UTC
Thank you for answering.
System went live on Aug 19th
Change occurred on Aug 27th at 4 PM
Application problem was discovered on Aug 28th at 10AM
They were
1.invalid triggers in the actual Data schema and
2.invalid synonyms in the application schema(this only points to the data schema thru synonyms).Invalid synonyms were pointing to views,triggers & tables.
3.DBA_OBJECTS.last_ddl_time show only 2 objects for Aug 27th(when change occurred)
4.Remaining invalids have the actual creation time i.e.Aug 19th 2009 as last_ddl_time.
Questions-
If the application schema synonyms were created on Aug 19th,why and how did they become invalid by Aug 28th?
I confirmed that there were zero invalids when the system went live.
thanks
September 02, 2009 - 2:09 pm UTC
the changes you made on the 27th undoubtedly did it - to everything. Making a single change will change other things.
I don't care about when the system went live - you make DDL changes, that is like releasing a brand new system.
However, that said, why were invalid objects a problem??? they would immediately validate themselves upon use. The only time it would be a problem is is the changes you made, made it IMPOSSIBLE for them to validate.
ops$tkyte%ORA10GR2> create table t1 (x int, y int );
Table created.
ops$tkyte%ORA10GR2> create table t2 (x int, y int );
Table created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace trigger t1_trigger
2 before insert on t1 for each row
3 begin
4 dbms_output.put_line( 'inserting ' || :new.x || ', ' || :new.y );
5 end;
6 /
Trigger created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace trigger t2_trigger
2 before insert on t2 for each row
3 begin
4 dbms_output.put_line( 'inserting ' || :new.x || ', ' || :new.y );
5 end;
6 /
Trigger created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create synonym t1_syn for t1;
Synonym created.
ops$tkyte%ORA10GR2> create synonym t2_syn for t2;
Synonym created.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select object_name, object_type, status, last_ddl_time from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_TIME
------------------------------ ------------------- ------- --------------------
T1 TABLE VALID 02-sep-2009 13:38:26
T2 TABLE VALID 02-sep-2009 13:38:26
T1_TRIGGER TRIGGER VALID 02-sep-2009 13:38:26
T2_TRIGGER TRIGGER VALID 02-sep-2009 13:38:26
T1_SYN SYNONYM VALID 02-sep-2009 13:38:26
T2_SYN SYNONYM VALID 02-sep-2009 13:38:26
6 rows selected.
ops$tkyte%ORA10GR2> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> alter table t1 add z int;
Table altered.
ops$tkyte%ORA10GR2> alter table t2 drop column y;
Table altered.
<b>there are my two changes...</b>
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select object_name, object_type, status, last_ddl_time from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_TIME
------------------------------ ------------------- ------- --------------------
T1 TABLE VALID 02-sep-2009 13:38:28
T2 TABLE VALID 02-sep-2009 13:38:28
T1_TRIGGER TRIGGER INVALID 02-sep-2009 13:38:26
T2_TRIGGER TRIGGER INVALID 02-sep-2009 13:38:26
T1_SYN SYNONYM INVALID 02-sep-2009 13:38:26
T2_SYN SYNONYM INVALID 02-sep-2009 13:38:26
6 rows selected.
<b>and everything BUT the tables are invalid...</b>
ops$tkyte%ORA10GR2> exec dbms_lock.sleep(2);
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into t1 values ( 1, 1, 1 );
inserting 1, 1
1 row created.
<b>Obviously my trigger must not be invalid anymore...</b>
ops$tkyte%ORA10GR2> insert into t2 values ( 1 );
insert into t2 values ( 1 )
*
ERROR at line 1:
ORA-04098: trigger 'OPS$TKYTE.T2_TRIGGER' is invalid and failed re-validation
<b>that one is though, because we broke it, the code cannot compile</b>
ops$tkyte%ORA10GR2> select * from t1_syn;
X Y Z
---------- ---------- ----------
1 1 1
ops$tkyte%ORA10GR2> select * from t2_syn;
no rows selected
<b>appears my synonyms are OK now</b>
ops$tkyte%ORA10GR2> select object_name, object_type, status, last_ddl_time from user_objects;
OBJECT_NAME OBJECT_TYPE STATUS LAST_DDL_TIME
------------------------------ ------------------- ------- --------------------
T1 TABLE VALID 02-sep-2009 13:38:28
T2 TABLE VALID 02-sep-2009 13:38:28
T1_TRIGGER TRIGGER VALID 02-sep-2009 13:38:30
T2_TRIGGER TRIGGER INVALID 02-sep-2009 13:38:30
T1_SYN SYNONYM VALID 02-sep-2009 13:38:31
T2_SYN SYNONYM VALID 02-sep-2009 13:38:31
6 rows selected.
<b>everything that could compile - did so - all by itself</b>
so, again, unless your changes made it not possible to compile, I don't understand what the issue was - it would NOT break the application unless your changes broke the application.
A reader, September 02, 2009 - 3:16 pm UTC
Thanks for the imput.
I will work more on this.
The application stopped authenticating the logins.It just threw "Login Failure.Authentication Failed."
A reader, September 02, 2009 - 3:20 pm UTC
Forgot to mention...
I had problems compiling the invalids on the database server using utlrp script. It took 30 mins without any output.
I had to startup the database in restric mode and run the utlrp again ..then it finished in 1 min 20 secs.
I am wondering why the objects could not compile on their own upon use..but compiled with utlrp script.
Anyway, thanks for the input.
I will see what I can find.
Regards,
ora-06508
Jose Guajardo, December 24, 2009 - 11:42 am UTC
I have a pkg (p1) that calls another pkg (p2) where p1 run from a script in oracle the process works correctly when called from a vb or java program, tells me that p2 is not found, giving an error ORA-06508
December 31, 2009 - 2:41 pm UTC
show me how you call it from vb or java.
How to find when an object got invalidated
Saravanan R, April 30, 2010 - 6:57 pm UTC
Hi Tom,
I was going through the entire chain and got lots of info about invalid objects. But I want to know if there is any way to find out the timestamp when an object became invalid. The last_ddl_time doesn't show it, it reflects the time if the object is compiled.
Every month-end there in our EBS database, we get alerts that objects are invalid in the APPS schema, and was curious to find out which was the parent object in it. I feel knowing the time when an object becomes invalid would be helpful to find out what triggered it.
Please help
May 06, 2010 - 10:28 am UTC
there is not, you could set up a job if you like to "sample" the data - you know, copy into your own history table a list of relevant, invalid objects.
or, you could just do as I would do and ignore it, invalidate objects are very "normal".
invalid objects
Jyothsna, May 06, 2010 - 3:49 am UTC
an object with the status of invalid can't be a referenced object
Please guide me the above statment
Thanks
May 06, 2010 - 2:32 pm UTC
I'd erase it, because on the face of it, it is false.
But I don't have any context for it, so I'm not really sure what they are saying or trying to say.
So, ignore it.
ops$tkyte%ORA11GR2> create or replace procedure p1
2 as
3 begin
4 null;
5 end;
6 /
Procedure created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create or replace procedure p2
2 as
3 begin
4 p1;
5 end;
6 /
Procedure created.
ops$tkyte%ORA11GR2> create or replace procedure p1
2 as
3 begin
4 afdafda;
5 end;
6 /
Warning: Procedure created with compilation errors.
ops$tkyte%ORA11GR2> select referenced_name from user_dependencies where name = 'P2';
REFERENCED_NAME
----------------------------------------------------------------
P1
ops$tkyte%ORA11GR2> select object_name, status from user_objects where object_name = 'P1';
OBJECT_NAME STATUS
------------------------------ -------
P1 INVALID
if Invalid object executing by job agin and agin
Vipul Garg, June 01, 2010 - 6:15 am UTC
Hi Tom,
i create a maintainance package that will monitor the important object. if it found invalid then valid dynamicly.
but it's get in hangging mode when it try to compile a invalid object that is called by a job. The job keep execute this object. please suggest in this situation how can i compile the invalid object without hagging.
June 08, 2010 - 8:14 am UTC
you cannot compile an object that is being executed. It sounds like that is what you are trying to do.
Since the object will automagically compile itself when executed - what is the point of this job you are trying to create???
Have you tried instead to discover WHY it goes invalid and perhaps preclude that event from occurring?
Invalid Objects
Anand, July 05, 2010 - 4:39 am UTC
Tom,
can we write a trigger on the all_objects? Because whenever a package is altered and if it is goes invalid, the change will be updated here. So to avoid the, package state discarded, can I write a trigger on all_objects? My idea is when the pacakge is altered and it is invalid status. so from all_objects, after the updation of STATUS column, check the status and if it is INVALID write a dynamic query to compile the package. So that whenever the package is goes invalid status by the way all_objects gets updated, this trigger will fire and recompile the invalid package to valid status. Is my approach is correct? Your valuable comments please.
July 06, 2010 - 2:56 pm UTC
no triggers on things owned by SYS.
and besides, all_objects is a view, you don't have normal triggers on views, so it wouldn't work anyway.
And further, I don't see how that would affect the existing state of packages has been discarded????
it is because the package IS COMPILED that you get the ora-4068, it is NOT because it "isn't compiled". The act of compiling it is what causes the ora-4068
Note: with 11gr2 and edition based redefinition, you can avoid entirely the ora-4068 in production...
Script to Compile Invalid Objects
Rajeshwaran, Jeyabal, July 07, 2010 - 10:31 am UTC
Tom,
To solve this Issue of compiling Invalid objects, I developed a script its here. When i find some set of invalid objects in database, i will run this script the dependency hierarchy will be taken care by this script.
scott@10G> BEGIN
2 FOR r IN
3 (SELECT 'ALTER '
4 || DECODE(a.object_type, 'PACKAGE BODY', 'PACKAGE', 'TYPE BODY', 'TYPE', a.object_type)
5 || ' '
6 || a.object_name
7 || DECODE(a.object_type, 'JAVA CLASS', ' RESOLVE', ' COMPILE')
8 || DECODE(a.object_type, 'PACKAGE BODY', ' BODY', 'TYPE BODY', 'BODY') text
9 FROM user_objects a,
10 (SELECT MAX(LEVEL) dlevel,
11 object_id
12 FROM public_dependency
13 START WITH object_id IN
14 (SELECT object_id
15 FROM user_objects
16 WHERE status = 'INVALID'
17 AND object_type <> 'SYNONYM'
18 )
19 CONNECT BY object_id = PRIOR referenced_object_id
20 GROUP BY object_id
21 )b
22 WHERE a.object_id = b.object_id(+)
23 AND a.status = 'INVALID'
24 AND object_type <> 'SYNONYM'
25 ORDER BY b.dlevel DESC,
26 a.object_name ASC
27 )
28 LOOP
29 BEGIN
30 EXECUTE immediate r.text;
31 EXCEPTION
32 WHEN OTHERS THEN
33 dbms_output.put_line (' Exception '||SQLCODE||' - '||sqlerrm ||' - ' ||r.text);
34 END;
35 END LOOP;
36 END;
37 /
Exception -24344 - ORA-24344: success with compilation error - ALTER PACKAGE CURSOR_VARIABLE_API COMPILE
Exception -24344 - ORA-24344: success with compilation error - ALTER PROCEDURE PRC_CALLBACK COMPILE
PL/SQL procedure successfully completed.
http://rajeshwaranbtech.blogspot.com/2010/05/script-to-compile-invalid-objects.html
July 08, 2010 - 11:57 am UTC
32 WHEN OTHERS THEN
33 dbms_output.put_line (' Exception '||SQLCODE||' - '||sqlerrm ||' - '
||r.text);
34 END;
http://asktom.oracle.com/pls/asktom/asktom.search?p_string=%22i+hate+your+code%22 I don't like it for that, and I don't like it because it is utterly unnecessary.
You have utlrp.sql already if you really feel the need to do this for whatever reason.
and you do not really want to compile them in any order - there in fact might not be an order to compile them in.
And - you are compiling things that should not be compiled. If you compile a package body and the spec is invalid, the spec will automagically be compiled for you. You'll end up compiling things that are already compiled.
This is just an unnecessary step.
Reason for Invalid Objects
Abhisek, July 13, 2010 - 1:57 am UTC
Hi tom,
In case we get the invalid objects, can we know the reason from any view or table why it was invalid?
I have to recompile them but still I would love to know the reason why it was especially when the error handler is not present as per (good) practices.
July 19, 2010 - 11:00 am UTC
we do not track that, no.
... especially when the error handler is not present as per (good) practices. ...
I did not understand that train of thought at all. What does the code in the invalid object have to do with anything?
Invalid objects
Abhisek, July 19, 2010 - 12:48 pm UTC
Hi Tom,
Thanks for the repy. Actually I have observed sometimes that when the compilation of any packages fails for non-existence of column or table, sometimes it is marked as INVALID..
I was referring to those situations where OTHERS exception is not used to track unknown errors.
July 19, 2010 - 2:12 pm UTC
again, I have no idea why you are talking about when others - what does that have to do with code that cannot be compiled???
all code that cannot be recompiled is invalid code, I don't know what you are asking for here?
invalid objects
Abhisek, July 20, 2010 - 2:19 am UTC
Hi Tom,
I am sorry to mix two things ... I have a code something like this:
CREATE OR REPLACE procedure MONITORING.demo
is
begin
select name from demo1;
exception
when others then
raise;
end;
/
now the table demo1 is not present in the database so the procedure is compiled with compilation errors.. Procedure is further listed into Invalid objects list of TOAD..
Can we know what was the object because of which it failed.
I can look into USER_ERRORS but I dont find it too helpful. It says: PL/SQL: ORA-00942: table or view does not exist
I would like to know the name of table/view.
Hope I am clear this time.
July 23, 2010 - 6:53 am UTC
exception
when others then
raise;
end;
http://asktom.oracle.com/pls/asktom/asktom.search?p_string=%22i+hate+your+code%22 that is a worst practice when programing, please stop doing it or anything remotely similar to it.
If toad cannot point you to the exact line where the error occurred - that is, if toad doesn't actually in their GUI for coding pl/sql say "this error occurred right here" and put you on the line of code that failed and show you the exactly offending statement - then you need to look at SQLdeveloper.
Our error message will not have the table name in it, but you should be placed right on the error itself making it somewhat self evident what is wrong.
Followup
Abhisek, July 23, 2010 - 7:10 am UTC
Hi Tom,
My apology... I dont use OTHERS exceptions thanks to your posts earlier... That was just an example that I see in existing packages that I work with.
Normally even if in Toad when we compile as a script, it says like: Created with compilation errors, which can be found out by querying USER_ERRORS..
but here, since the objects are installed through a package on release, so we dont hv any GUI option for TOAD or SQL Developer..
Anyways I take your word that we dont record the objects that make others invalid, anywhere in data dictionary..
Public synonym invalidates dependent packages?
Lise, September 03, 2010 - 1:38 pm UTC
Hi,
I have a scenario, where I have a public synonym to a PL/SQL package PCK_COMMON residing in schema A.
Lots of packages are referencing this package, and these packages reside in lots of different schemas.
If I try to compile PCK_COMMON in my schema say schema B, all the packages referencing PCK_COMMON through the public synonym are invalidated and has to be recompiled. This is causing a headache, since we have a lot of schemas holding the packages calling PCK_COMMON. It should be possible to compile in the PCK_COMMON package even though there is a public synonym to it.
This was ok in 9.2.0.5, but not in 11.1.0.7
Any ideas would be great. In the meantime we will create private synonyms for PCK_COMMON for all the schemas holding code that references it.
Thanks
September 09, 2010 - 7:23 pm UTC
yet another reason public synonyms are *evil* and should be avoided.
the schemas that need to access it should just qualify the darn thing, reference owner.pkg.
but, are you sure that is what is happening?
ops$tkyte%ORA11GR2> drop user a cascade;
User dropped.
ops$tkyte%ORA11GR2> drop user b cascade;
User dropped.
ops$tkyte%ORA11GR2> drop user c cascade;
User dropped.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> grant dba to a identified by a;
Grant succeeded.
ops$tkyte%ORA11GR2> grant dba to b identified by b;
Grant succeeded.
ops$tkyte%ORA11GR2> grant dba to c identified by c;
Grant succeeded.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> connect a/a
Connected.
a%ORA11GR2> create or replace package the_pkg
2 as
3 g_data varchar2(25);
4 end;
5 /
Package created.
a%ORA11GR2> create or replace public synonym the_pkg for a.the_pkg
2 /
Synonym created.
a%ORA11GR2> grant execute on the_pkg to public
2 /
Grant succeeded.
a%ORA11GR2>
a%ORA11GR2> connect b/b
Connected.
b%ORA11GR2> create or replace procedure p
2 as
3 begin
4 dbms_output.put_line( the_pkg.g_data );
5 end;
6 /
Procedure created.
b%ORA11GR2> show errors
No errors.
b%ORA11GR2> pause
b%ORA11GR2> connect c/c
Connected.
c%ORA11GR2> select object_name, status from dba_objects where object_name = 'P' and owner='B';
OBJECT_NAME STATUS
------------------------------ -------
P VALID
c%ORA11GR2> create or replace package the_pkg
2 as
3 g_data2 varchar2(25);
4 end;
5 /
Package created.
c%ORA11GR2> select object_name, status from dba_objects where object_name = 'P' and owner='B';
OBJECT_NAME STATUS
------------------------------ -------
P VALID
kghuclientasp_04
Chris, October 26, 2010 - 8:57 am UTC
We recently upgraded our databases to 10.2.0.4 patchset 3 oneoff patchset 26, on windows -- working on a linux migration. Ever since then we have been getting ORA-error stack (00600[kghuclientasp_04], 00600[kghuclientasp_04], 00600[kghuclientasp_04]) showing up in our alert logs every few days, sometimes multiple times a day.
Metalink and everything else I have found says I have an invalid object somewhere.
One day I check
- there are none
The next day -- without any builds going to the database --
- there are invalid objects (packages, synonyms, and more).
For the time being we are running utlrp when the
ORA-error stack occurs and that seems to stem it off for a few days.
Is it possible that our modplsql application isn't recompiling these objects that go invalid and why are they going invalid if there are NO CHANGES being made to the objects in a database?
00600 search in metalink and metalink itself doesn't have any information/suggestions when it decides to complete a search except that I should apply patchset 3. 10.2.0.4 version we installed was patchset 3
October 26, 2010 - 8:02 pm UTC
please utilize support
ORA-ERRORS
A reader, March 31, 2011 - 4:20 am UTC
hi Tom
Where does Oracle keep these error messages?
ORA-04063: package body "PKG_NAME" has errors
ORA-06508: PL/SQL: could not find program unit being called
I searched in USER_ERRORS but didnt find this
April 12, 2011 - 10:03 am UTC
they are kept in files as part of the database installation, in $ORACLE_HOME/rdbms/mesg
Dynamic compilation
A reader, April 12, 2011 - 10:08 am UTC
Hi Tom,
Thanks for the answer. have one more doubt. I get this error when I try to run an invalid package. So what are the events fired to give the error message. Does oracle checks into the file you specified? or it creates the entries there?
Could you please explain the steps how is package executions are checked and then reported if there is any error? Can we track the same in some table??
April 13, 2011 - 9:43 am UTC
You are trying to compile and run an anonymous block, something like this:
ops$tkyte%ORA11GR2> begin
2 /* some code here perhaps */
3 pkg_name.p;
4 /* some more code here */
5 end;
6 /
begin
*
ERROR at line 1:
ORA-04063: package body "OPS$TKYTE.PKG_NAME" has errors
ORA-06508: PL/SQL: could not find program unit being called:
"OPS$TKYTE.PKG_NAME"
ORA-06512: at line 3
the error you are receiving is YOUR error, it is not associated with anything, it is coming from an anonymous (un-named, not stored) plsql block. We do not store that error (you can if you want to)
However, the errors for PKG_NAME would be stored:
ops$tkyte%ORA11GR2> select * from all_errors
2 where owner = 'OPS$TKYTE' and name = 'PKG_NAME' and type = 'PACKAGE BODY';
OWNER NAME TYPE
------------------------------ ------------------------------ ------------
SEQUENCE LINE POSITION
---------- ---------- ----------
TEXT
-------------------------------------------------------------------------------
ATTRIBUTE MESSAGE_NUMBER
--------- --------------
OPS$TKYTE PKG_NAME PACKAGE BODY
1 3 1
PLS-00305: previous use of 'P' (at line 3) conflicts with this use
ERROR 305
OPS$TKYTE PKG_NAME PACKAGE BODY
2 3 1
PL/SQL: Item ignored
ERROR 0
OPS$TKYTE PKG_NAME PACKAGE BODY
3 3 11
PLS-00328: A subprogram body must be defined for the forward declaration of P.
ERROR 328
OPS$TKYTE PKG_NAME PACKAGE BODY
4 3 12
PLS-00323: subprogram or cursor 'P' is declared in a package specification and
must be defined in the package body
ERROR 323
Invalid
Chad Slagle, October 13, 2011 - 6:25 pm UTC
I thought that the an invalid state signified more than an an object that needed to be compiled, but also an object that cannot be compiled because of a change, or is compile-able but has looping synonyms and cant be resolved consistently. The objects that can compile, will recompile themselves when used.
With that in mind, I use reports (in TOAD) of invalid objects in a schema to identify problems after migrating new code. I run a "compile all invalid objects" in TOAD, and that clears out any "invalid until recompiled" objects, which I consider a false positive.
I am always amazed at how much code gets stranded, or gets broken despite testing and configuration management rules.. All code that works should be compiled, and the remainder I put in a report for developers to investigate why they are invalid.
I have to agree that this can be quite a bit of work, but it keeps the environment in production cleaner and less buggy. The end result is worth the work, IMO.
ORA-04021 on top of ORA-06508
Al Ricafort, February 14, 2012 - 4:35 am UTC
Hi Tom,
Our system recently encountered an incident where our java program received an SQLException ORA-04021 on top of ORA-06508.
"nested exception is: java.sql.SQLException: ORA-04021: timeout occurred while waiting to lock object xxxx.xxxxx_PKG ORA-06508: PL/SQL: could not find program unit being called.."
I am trying to figure out how can a ORA-06508 cause a timeout? The package is there but was recompiled.
Is it because when this session tries to recompile this package because it was invalid someone is using it so it cannot get the lock?
I tried to simulate this but I cannot.
Thank you.
Object in SYS schema invalid
Onkar, August 08, 2013 - 10:38 am UTC
Tom,
Excellent information as usual. I have few objects in one of my database invalid under SYS schema. All of them are Java Class. Though they do not hamper normal functionality of the database but I am curious to know why such objects becomes invalid and what should I do to compile them back.
SQL> l
1* select object_name,object_type from dba_objects where owner='SYS' and status='INVALID'
SQL> /
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------- ------------------
oracle/xdb/XMLType JAVA CLASS
oracle/xdb/XMLTypeFactory JAVA CLASS
oracle/xdb/bean/XMLTypeBean JAVA CLASS
/fa47e42a_XDBDomImplementation JAVA CLASS
oracle/xdb/dom/XDBDocument JAVA CLASS
oracle/xdb/dom/XDBNode JAVA CLASS
oracle/xdb/dom/XDBDocumentType JAVA CLASS
oracle/xdb/dom/XDBElement JAVA CLASS
oracle/xdb/dom/XDBDocFragment JAVA CLASS
oracle/xdb/dom/XDBText JAVA CLASS
oracle/xdb/dom/XDBCharData JAVA CLASS
oracle/xdb/dom/XDBComment JAVA CLASS
oracle/xdb/dom/XDBCData JAVA CLASS
oracle/xdb/dom/XDBProcInst JAVA CLASS
oracle/xdb/dom/XDBAttribute JAVA CLASS
/6337ae3d_XDBEntityReference JAVA CLASS
oracle/xdb/dom/XDBNodeList JAVA CLASS
oracle/xdb/dom/XDBDOMException JAVA CLASS
oracle/xdb/dom/XDBNamedNodeMap JAVA CLASS
oracle/xdb/dom/XDBEntity JAVA CLASS
oracle/xdb/dom/XDBNotation JAVA CLASS
oracle/xdb/spi/XDBContext JAVA CLASS
/734d62fb_XDBContextFactory JAVA CLASS
oracle/xdb/spi/XDBResource JAVA CLASS
/8d00b602_XDBNamingEnumeration JAVA CLASS
25 rows selected.
SQL>
August 08, 2013 - 5:20 pm UTC
upgrades, patches, installing other bits of code that these things rely on, turning on a new feature - many things can do that.
you could compile them - but that could lead to cascading invalidations leaving more invalid :)
if you start using them - they'll automagically compile themselves - it is perfectly OK to have them this way.
Onkar, August 12, 2013 - 9:41 am UTC
Thanks Tom.
invalid package body
hai, March 22, 2014 - 5:30 pm UTC
Hi tom
In oracle ebs dbafter we compile package spec and body x
Its status was valid but
Package body y become invalid With no errors.
We try to run concurent request that call package x.
The concurent was stack didnt finish .
The package y didnt become valid automaticly as its should in theory.
We tried to recompile package body y but its stack.
Only after we kill all the sessions that use package body y directly or indirectly
We could recompile package body y .
After package y was valid we run the concurent and its completed normal.
1. Why package body y didnt become valid when we run concuren that indirectly execute package body y.
2. How can i recompile package body y without killing all the sessions that use package body y directly or indirectly.