Skip to Main Content

Breadcrumb

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

Question and Answer

Connor McDonald

Thanks for the question, Manoj.

Asked: December 12, 2018 - 4:48 pm UTC

Answered by: Connor McDonald - Last updated: January 13, 2019 - 6:06 am UTC

Category: PL/SQL - Version: 11.2.0.4

Viewed 1000+ times

Whilst you are here, check out some content from the AskTom team: Complex materialized views and fast refresh

You Asked

Hi,

In our DB, few packages became invalid. And when we verified it we saw that there are no errors related to them. I was expecting sessions to run this package without any error, however when it was executed for first time, we got ORA-04061 error. I could see that this fails occasionally and works fine most of the time.

In order to fix this permanently, I tried compiling these packages, also tried dropping and creating them again. After doing these, immediately the status of package becomes valid, but changes to invalid soon and started to cause the same issue.

This package has global variables, it also has a DB link to remote DB. Also, there were no structure changes to any of the objects used in the package in recent time.

There are other packages with same code version in different DB and some of them uses the same DB link, but those are working fine without any error.

Also, its only the package body which becomes invalid and package spec stays valid.

Could you please advice on resolving this permanently?

Thanks in Advance

and we said...

*Something* is making that package go invalid. Let's start with this:

SQL> create or replace
  2  package PKG is
  3   procedure P;
  4  end;
  5  /

Package created.

SQL>
SQL> create or replace
  2  package body PKG is
  3   global_var int;
  4   procedure P is
  5     x emp%rowtype;
  6   begin
  7     select * into x from emp where rownum = 1;
  8   end;
  9  end;
 10  /

Package body created.

SQL>
SQL> select object_type, status
  2  from   user_objects
  3  where  object_name = 'PKG';

OBJECT_TYPE             STATUS
----------------------- -------
PACKAGE                 VALID
PACKAGE BODY            VALID

2 rows selected.

SQL>
SQL> alter table emp drop column deptno;

Table altered.

SQL>
SQL> select object_type, status
  2  from   user_objects
  3  where  object_name = 'PKG';

OBJECT_TYPE             STATUS
----------------------- -------
PACKAGE                 VALID
PACKAGE BODY            INVALID

2 rows selected.

SQL>
SQL> exec pkg.p;

PL/SQL procedure successfully completed.

SQL>
SQL> select object_type, status
  2  from   user_objects
  3  where  object_name = 'PKG';

OBJECT_TYPE             STATUS
----------------------- -------
PACKAGE                 VALID
PACKAGE BODY            VALID

2 rows selected.



You can see that DDL on EMP (which PKG depends on) made it invalid. The next time we ran it, we automatically tried to recompile it, found that it was ok, and hence we ran it. But the key thing here is that we recompiled it (either automatically or manually).

From another session's perspective, you *recompiled* that package. For all it knows, you might have loaded a totally new version of the code, one that might not even *have* any global variables in it. It could have been anything - that is why we have the discard the state, because it doesn't know what has been going on the other session.

An easy way to handle this on a more permanent basis, is move the global variables to their own package. Then they are insulated from changes.

In session 2 I did:

--
-- after I first created the package
--
SQL> exec pkg.p

PL/SQL procedure successfully completed.

--
-- after the drop column *and* after session 1 had recompiled it
--
SQL> exec pkg.p
BEGIN pkg.p; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "MCDONAC.PKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "MCDONAC.PKG"
ORA-06508: PL/SQL: could not find program unit being called: "MCDONAC.PKG"
ORA-06512: at line 1



The solution I prefer (if you *must* have globals) is to put the globals in a separate package. eg

SQL> create or replace
  2  package PKG_G is
  3    global_var int;
  4  end;
  5  /

Package created.


would be a "partner" package to PKG. Then when you do things to PKG, PKG_G will remain unimpacted.

and you rated our response

  (6 ratings)

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

Reviews

Package recreation didnt work

December 14, 2018 - 3:22 pm UTC

Reviewer: Manoj Kumar from India

Hi,

Thank you for the explanation.
However I have a doubt.

I had tried to drop and recreate this package. I am curious why that didnt work.

Could you please help me to understand this?

Also, I am positive that no DDL happened on the objects used in these packages in recent time, other than unlocking stats, rebuilding those tables and gathering stats from them.

Could you please share your thoughts?

Thanks,
Connor McDonald

Followup  

December 17, 2018 - 2:28 am UTC

Also, I am positive that no DDL happened on the objects


Does DBA_OBJECTS.LAST_DDL_TIME agree with that assertion?

Don't forget that ORA-4068/4061 is when a *different* session has compiled the package (either manually or automatically by a subsequent execution) and then *your* session tries to re-use it

Any other option?

December 17, 2018 - 4:48 pm UTC

Reviewer: Manoj Kumar from India

Hi,

DBA_Objects shows even today's date as last_ddl_time, I am not sure if any DDL happened on them, I believe someone might have rebuild any table which could also change this. But yea, I cant confirm that through last_ddl_time.

Is there any other option, than modifying the code?
I know it is not logical, but if we kill all DB sessions/stop new sessions and compile the existing code and then start allowing new sessions, will it work in that case?
Connor McDonald

Followup  

December 18, 2018 - 2:04 am UTC

If you have a combination of global variables in packages that reference objects (eg tables) that are having DDL on them...then you are going head into ora-4068 territory.

The potential resolutions are

- don't do the DDL
- modify the code to separate the globals out
- modify the code to catch a ora-4068 and then re-initialize variables as required

You might see some references on the net to "Pragma Serially_reusable" - be really really careful about what you read.

Removing global variable didnt help

December 20, 2018 - 8:08 pm UTC

Reviewer: Manoj Kumar from India

Hi,

I have modified the package and removed global variable and declared them as local variables in the procedure which was using the variable. Compiled the new code in DB, but still same error.
Package body becomes valid, changes to invalid and getting same error in logs occasionally.

There are few DB jobs which are calling this and these called from application also.

I think I can catch the exception as you mentioned in the job and may be rerun the package in exception which should (ideally) execute fine. However, making this change in application is difficult.

Also, there are few tables which has last_ddl_time as today's date. But those tables were truncated which caused the change in last_ddl_time and those are not used in package which is having issue.

Is there anything am missing?
Connor McDonald

Followup  

January 07, 2019 - 7:00 am UTC

*Something* is making those packages invalid.

Look at capturing ALL ddl, eg using something like:

https://asktom.oracle.com/pls/asktom/asktom.search?tag=capturing-ddl-changes-on-a-table

and see what DDL is doing it

rerunning your "first demo"

January 09, 2019 - 12:53 pm UTC

Reviewer: Rajeshwaran, Jeyabal

Team,

was running the above first demo provided in this page and dont see
<quote>
You can see that DDL on EMP (which PKG depends on) made it invalid. The next time we ran it, we automatically tried to recompile it, found that it was ok, and hence we ran it. But the key thing here is that we recompiled it (either automatically or manually).
</quote>
this happening in 12.2 database - within a single session, we could get into ORA-04068?

demo@ORA12C> drop table emp  purge;

Table dropped.

demo@ORA12C> create table emp as select * from scott.emp;

Table created.

demo@ORA12C> create or replace package mypkg
  2  as
  3     procedure p;
  4  end;
  5  /

Package created.

demo@ORA12C> create or replace package body mypkg
  2  as
  3     global_var int;
  4     procedure p as
  5             r emp%rowtype;
  6     begin
  7             select * into r from emp where rownum = 1;
  8     end;
  9  end;
 10  /

Package body created.

demo@ORA12C> select object_name,object_type,status
  2  from user_objects
  3  where object_name ='MYPKG';

OBJECT_NAME                    OBJECT_TYPE             STATUS
------------------------------ ----------------------- -------
MYPKG                          PACKAGE                 VALID
MYPKG                          PACKAGE BODY            VALID

demo@ORA12C> exec mypkg.p;

PL/SQL procedure successfully completed.

demo@ORA12C> alter table emp drop column deptno ;

Table altered.

demo@ORA12C> select object_name,object_type,status
  2  from user_objects
  3  where object_name ='MYPKG';

OBJECT_NAME                    OBJECT_TYPE             STATUS
------------------------------ ----------------------- -------
MYPKG                          PACKAGE                 VALID
MYPKG                          PACKAGE BODY            INVALID

demo@ORA12C> exec mypkg.p;
BEGIN mypkg.p; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package body "DEMO.MYPKG" has been invalidated
ORA-04065: not executed, altered or dropped package body "DEMO.MYPKG"
ORA-06508: PL/SQL: could not find program unit being called: "DEMO.MYPKG"
ORA-06512: at line 1


demo@ORA12C>

but without global variables - rerunning the above demo after the DDL returned no errors.
Connor McDonald

Followup  

January 10, 2019 - 1:25 am UTC

Sorry, the ora-4068 was not the point of my first demo - it was merely to show that that we automatically set the state to invalid, and will try to automatically recompile on next invocation.

The resolution to ora-4068 remains unchanged, either:

- move globals to their own package
- catch the ora-4068 in code and re-instantiate package state

January 10, 2019 - 10:59 am UTC

Reviewer: A reader

Suppose I have a table called "emp" and i alter this table to add new 2 column so that where emp is used in other object also become invalid but before alter the table. I want to check if this object is aleady in use or if i alter the object so it's become another object invalid?
Connor McDonald

Followup  

January 11, 2019 - 6:09 am UTC

"so that where emp is used in other object also become invalid but before alter the table"

I don't understand what you're asking here, sorry.

You'll need to elaborate more

the light touch

January 11, 2019 - 7:05 am UTC

Reviewer: Racer I.

Hi,

Maybe : select * from dba_dependencies where referenced_name = 'EMP'

But this just says what might be affected (and you might have to cascade) not what will be.
There's also types of dependencies leading to invalidations that don't show up in here.

It doesn't come up often, but if it does finding the root cause is always difficult. Would be really cool if Oracle could proactively log the reason whenever it invalidates something. With something like a stack trace :

package A because of view B because of modification of table C by user D at timestamp E using DDL F

Not just for the setting of the INVALID state but of whatever may cause ORA-04061, which would be more of a "needs recompiling" state not an INVALID one.

I speculate that the ORA-04061 is based on an active notification for recompile-changes that goes to all sessions known to hold state for package A at the moment. In which case logging it once (not once per session) would be sufficient and could be left off completely if there are no such sessions at the moment. That should keep this logging to a reasonable minimum.

regards
Connor McDonald

Followup  

January 13, 2019 - 6:06 am UTC

Would be really cool if Oracle could proactively log the reason whenever it invalidates something. With something like a stack trace

Agreed - maybe log it on the ideas page?

https://community.oracle.com/community/groundbreakers/database/database-ideas

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.