Re: RE: package compilation hangs
- From: rjamya <rjamya@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 2 Sep 2004 07:17:05 -0400
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@xxxxxxx> 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@xxxxxxxxxxxxx&subject=unsubscribe
To read recent messages - //freelists.org/archives/oracle-l/09-2004
Other related posts:
