Re: Dropping user with logged in sessions

On 6 fév, 20:44, "joel garry" <joel-ga..._at_home.com> wrote:
> On Feb 6, 8:34 am, DA Morgan <damor..._at_psoug.org> wrote:
>
> > hasta..._at_hotmail.com wrote:
>
> > > Is there an upper bound for the time needed
> > > to actually kill a session at the oracle level ?
>
> > Depends on how much work needs to be rolled back.
>
> I dunno, I've seen them sit there for much longer than that. In older
> versions, it could be attributed to a busy SMON, so one could say
> using the OS process kill was better because it would use PMON which
> was usually very fast. As far as I can tell, there is no upper
> bound. The alter system kill times out after 60 seconds, but the
> session is left to finish whatever call it is making. That could be
> forever, especially if it is stuck in some poor PL code.
>

Joel and Gareth,

I did a quick test (under Oracle 10g) :

Session A inserts a row in a table, then enters an infinite pl/sql loop.

Session SYS kills session A (without immediate keyword), then tries to truncate the table and drop the user A.

The first time I tried it, the loop ended immediatly after session kill. However, neither did the truncate nor the drop user work. Both failed with ORA-00054 resource busy. I had to wait a few minutes.

I repeated the test three times afterwards, and in all cases the truncate DID work immediatly after killing the session.

I tripled-checked the sessions log, and am pretty sure I didnt do a mistake in my first test run.

I still need to repeat the test on O8 / O9, but would any of you have a reproducible test case, by any chance ? It would be much appreciated.

• Raoul