No one could logon to productio database for a while

I am using 9.2.0.5 on HP UNIX. I have an after logon trigger which executes quickly - all it does it set some stored outline parameters (trigger is provided below).

While this logon trigger is being executed, if someone else tries to connect, will Oracle wait for logon trigger to be completed before they can connect or will Oracle execute another "instance" of logon trigger? There is only one logon trigger in database. For example if I put a sleep in logon trigger for 5 minutes, will no one will be able to logon during this 5 minutes, I do not think so but want o check.

Today for some reason, no one could logon to my production database including sys.
When sys tried to logon from sqlplus, session was just hung.

We have no of process set to 1200 and there were only 383 oracle shadow processes,
But then I killed a time-consuming oracle shadow process, I see a message from this trigger in my alert.log:

ORA-1013: user requested cancellation of operation, *** Error in login trigger.

Not sure whether this error message came from this long running killed process or some other process which was causing execution of logon trigger.

Aftre killng a process, people could connect. I logged on to system. My trigger is valid and now everything works. All I did was killed an Oracle shadow process which somehow will not let any one logon. This sounds like some Oracle quirk/bug. I am not sure whether logon trigger is the culprit or something else, but genrally if logon trigger has some issue it can hose everyone (but not sys). I have no clue how to find root cause f the problem. I have already looked in alert.log and trace files

   Below is trigger. It is a simple trigger, it calls a function and function simply sets use_stored_outlines for some users.

CREATE OR REPLACE TRIGGER my_logon AFTER LOGON ON database BEGIN
    wmuser.my_outlnfun;
exception
when others then

    sys.dbms_system.ksdwrt(2, SUBSTR (SQLERRM, 1, 100) || ' *** Error in logon trigger');
END;
/

create or replace procedure my_outlnfun is begin

if (user = 'JJPROS' or user = 'READGGPROS' or user = 'GGOSPP'') then

       execute immediate 'alter session set use_stored_outlines=my_custom';

end if;
end; Received on Fri Sep 22 2006 - 13:06:07 CDT