Re: RE: package compilation hangs

Mark,

Here is a personal experience ... painful but learning none the less.

Yours truly has some custom code in db level log on trigger that captures log on information and based on certain criteria enabled 10046^8 trace in user sessions or reject certain session. This is all table driven and it is an IOT. One day we found that there is corruption in the table, so decided to rebuild the table.

Now any competent DBA would do this on development box first, so I did the same thing to make sure there will be no issues. Worked perfectly, happily I did the same thing on production. OOOOOps ... the log on trigger got invalid. Reports submitted started backing up, all monitors went from green to yellow to orange to red and alarms sounded. Really, our monitors do all that.

Even dba's have had trouble getting in as 'sqlplus /nolog" and "connect / as sysdba". This alone could take more than 7 minutes. Luckily I had one sys session open. All attempts to compile the trigger failed. Finally my boss decided to give use community a warning and quickly bounce the db. He went to the bosses to explain and get authorization. I was feeling really silly.

Finally in the only sys window open, ran the command ... select sid,serial# from v$session where command = 61;

I think 61 is alter trigger ... found one session trying to recompile the same trigger, but since it had no privileges, hung. Quickly types the alter trigger ... compile command, yelled over to fellow DBA, gave him the os pid for the session and had him kill -9 it. and at the same time I hit enter. It worked, trigger became valid, things started working again. I wiped sweat off my face as the monitors went from red to orange to yellow to green in under 10 minutes. Called my boss and told him that bouncing won't be necessary. Got up, went outside sat down on a bench for 5 minutes and then got in my boss'es office.

Rest, you don't want to know, but boss appreciated how I fixed the issue.. But I also learnt a hard lesson on what not to do.

Cheers
Raj

On Wed, 1 Sep 2004 10:15:17 -0400 , Powell, Mark D <mark.powell_at_eds.com> wrote:
> Did you cancel your first compile attempt before you tried just compiling
> the body?
>
> Being that it is now the next day, what did you do to resolve this? We
> would have probably restricted the system, killed all user sessions,
> compiled, and then unrestricted the system if we could not just clear out
> the using sessions.
>

---
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe 
To read recent messages - http://freelists.org/archives/oracle-l/09-2004