Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Jeremy.

Asked: May 14, 2001 - 12:10 am UTC

Last updated: August 08, 2013 - 5:20 pm UTC

Version: 8.0.6

Viewed 100K+ times! This question is

You Asked

I've detected a few invalid objects in one of our databases. How do I make them 'VALID'?

and Tom said...

They will fix themselves as they are executed or accessed. I never worry about some invalid objects -- you'll almost always have some somewhere. You could create a full time job fixing them -- or just let the database do it.


In any case:

exec dbms_utility.compile_schema( 'SCOTT' )

would compile all of the invalid objects owned by SCOTT (note: must use UPPER case on the schema name).

If some objects are still invalid -- that means there is some error with them -- use the "show errors " command in sqlplus to diagnose.






Rating

  (103 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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


 

Tom Kyte
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?



Tom Kyte
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.



Tom Kyte
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


Tom Kyte
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,


Tom Kyte
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 

Tom Kyte
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.

Tom Kyte
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.

Tom Kyte
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.

 

Tom Kyte
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


Tom Kyte
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



Tom Kyte
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.


Tom Kyte
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.



Tom Kyte
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



Tom Kyte
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;

Tom Kyte
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.

Tom Kyte
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.

 

Tom Kyte
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 :-)

Tom Kyte
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 

Tom Kyte
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?

Tom Kyte
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!

Tom Kyte
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



Tom Kyte
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.

Tom Kyte
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!


Tom Kyte
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!

Tom Kyte
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




 

Tom Kyte
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.

Tom Kyte
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.

Tom Kyte
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 

Tom Kyte
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 

Tom Kyte
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

Tom Kyte
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

Tom Kyte
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.

Tom Kyte
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

Tom Kyte
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,


Tom Kyte
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 

Tom Kyte
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?



Tom Kyte
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

Tom Kyte
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.

Tom Kyte
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 

Tom Kyte
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.



Tom Kyte
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,



Tom Kyte
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,

Tom Kyte
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.....

Tom Kyte
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.....





Tom Kyte
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?

Tom Kyte
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,

Tom Kyte
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

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.

Tom Kyte
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?
Tom Kyte
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.  

Tom Kyte
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.
Tom Kyte
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.
Tom Kyte
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
Tom Kyte
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?
Tom Kyte
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;
/
Tom Kyte
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

Tom:

What do you mean it does not work?!

See your first response all the way in the top. You say do the compile_schema('SCOTT').

The second approach is described on oracle site here

http://www.oracle-base.com/articles/misc/RecompilingInvalidSchemaObjects.php

What is wrong with both approaches?
Tom Kyte
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.
Tom Kyte
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?
Tom Kyte
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
Tom Kyte
September 01, 2009 - 11:37 am UTC

there is a script, utlrp, supplied with the database that many people run after an upgrade.

http://docs.oracle.com/docs/cd/B19306_01/install.102/b25414/postinst.htm#sthref296

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 >

Tom Kyte
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


Tom Kyte
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
Tom Kyte
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


Tom Kyte
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
Tom Kyte
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
Tom Kyte
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.
Tom Kyte
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.
Tom Kyte
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


Tom Kyte
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.
Tom Kyte
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.
Tom Kyte
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.
Tom Kyte
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
Tom Kyte
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
Tom Kyte
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
Tom Kyte
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??
Tom Kyte
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>


Tom Kyte
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.







More to Explore

DBMS_UTILITY

More on PL/SQL routine DBMS_UTILITY here