Re: drop user hangs

"Rainer Herbst" <rherbst_@_rz.uni-potsdam.de> wrote in message news:bicqd3$rd2$1_at_zeppelin.rz.uni-potsdam.de...
> Hi, *!
>
> I run in trouble with a very simple script:
>
> drop user fodb cascade;
> create user fodb
> identified by "secret"
> ...
> ;
>
> Following behaviour:
> 1. user does not exists
> --> error in the first statement, second statement executed

Expected, and normal. Would need to be trapped if you're that concerned about it.

> 2. user exists, instance just started
> --> the drop user takes about 30 sec, but executes without error, the
> second statement executes without error

Seems also to be fine.

> 3. user was created after the instance started, but the user is
> definatly not logged in!
> --> the drop user statement hangs without any complains or error messages!

Hangs for ever? You're right that you can't drop a user if they are logged in, so how are you certain that they are not logged in? When you say it hangs, and produces no error message, have you nevertheless checked the alert log for any possible warning messages.

It occurs to me that since this is 9i, if you were very short of space in things like the SYSTEM rollback segment, and if somehow your session had been placed into RESUMABLE mode, then the drop of the user (which involves doing deletes from various data dictionary tables, and thus generating some undo/rollback) could run into out-of-space errors, which the 'resumable' setting would cause to hang instead. If so, the alert log will tell you.

But since I'd expect you to know whether you have switched on resumable, and you don't mention that you have, I guess I'm just clutching at straws here.

Redo is also generated by data dictionary table DML such as you're doing, so if you have specified archivelog, and not switched on ARCH, then you might again get a database hang instead of an error. But again, I'm getting desperate here. But also again, the alert log would show you if there was an inability to move on into the next redo log causing the issue.

Apart from that, I can't think of anything very much.

Regards
HJR
>
> Any clue how I could get the script running without restarting the
> instance every time?
> My environment: Oracle 9.2.0.3.0 64bit on Solaris 8.
> scripte executed via sqlplus on the same machine.
>
> Regards!
> Rainer Herbst
>
> --
> ------------------------------------------------
> Rainer Herbst Linux - Registered
> ZEIK User #319157
> Universität Potsdam Usual disclaimers applies!
> ------------------------------------------------
>
Received on Mon Aug 25 2003 - 15:16:29 CDT