Sunday, August 5, 2007

sequence of errors

I encountered this at one of my client's .. I usually get to see my inbox full of unread messages running for atleast 3 or 4 pages every time I get to check my account set up with this client because I usually get to read them only once in a week and one day I happened to see some mails with some ORA- error (4068 error to be exact) and that caught my attention.

what was happening was a few packages in Production were bombing with ORA-4068 error and there were back and forth emails between development and dba team on how to resolve it. The development team thought it was due to packages in INVALID state but they didnt know how does a package suddenly change the status to INVALID, they simply put the ball on DBA's court asking them to ensure & maintain the production objects with VALID status.
DBA team responded back they had to do DDL changes but replied back saying it may be due to partition maintenance operations which was all coded by DEV team..Eventually they were about to agree on creating a database job to check invalid objects every 15 minutes and automatically compile them..

what surprised me at the time I read all this long email thread was that no one cared to really dig in what the real cause of the problem but was ready to throw in suggestions to whatever problems they thought was creating the issue.

ORA-04068: existing state of packages has been discarded

is pretty simple and straight forward..clearly states your existing state of package has been discarded.. if you change a packag with global variables and some sessions has already stored the prev code, they had to flushed and reloaded because you changed the source code. Just simple as that..
I developed a test case and proved to DBAs and Development team that this can still happen if the status of packages are perfectly valid ..and actually the error was happening because the production releases were pushed in without bringing down client (web clients with connection pooling)..simply bringing down the clients before pushing new PLSQL code & then bringing up new connections would fix the issue without any single line of coding effort.

I didn't get any questions back from any team ..neither did I get any feedback on if they accepted my theory..but it was interesting that two teams were ready to solve an inexistent problem :)


Anonymous said...

Hi Venkat, I m really very happy and thankfull to your comment posted on this web page. I am facing the same problem. let me try it out if it helps me.
I know I am calling a package in which I have intialised two variable in spec and later this package calls another package in which again same named variables are getting assigened with same values.while debug it comes out of the second packges spec and does not go into body of second package.

Raghvendra 9885584591- hyd

Venkat said...

Raghvendra -
Thanks for your comment..I am not located in India & I do this blogging just to kill idle time in somewhat productive way. (so no phone numbers please..I am in no situation to call)

Regarding your technical question, I am sort of lost on what you are asking. YOu have a first package and then you have second package but the code doesn't reach package body?? Thats is something I cant understand much.

May be a better explanation of your situation would help.

- Venkat