Re: drop user hangs

From: Rainer Herbst <rherbst__at__rz.uni-potsdam.de>
Date: Tue, 26 Aug 2003 10:21:24 +0200
Message-ID: <bif59m$i3m$1@zeppelin.rz.uni-potsdam.de>

Howard J. Rogers schrieb:
> "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.
>

No errors or warnings in the alert log. V$SESSION_WAIT shows the 'null event' for this session.

> 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.
>

Session set explicitly to DISABLE RESUMABLE, but the same behaviour.

There might be a problem because of the SYSTEM tablespace is filled to 98%, but
- the datafile is AUTOEXTEND ON and
- the database runs in UNDO_MANAGEMENT = AUTO. In my understanding, I do not have to bother about the rollback segments as far as there is enough space on the disk and the datafiles are not on their max extends limit? Again, if any problems occure, there should be a message in the alert log?

> 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.
>

ARCH is on and working, DB is in ARCHIVELOG mode. No entry in the alert log.

> 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!
>>------------------------------------------------
>>

>
>
>

-- 
------------------------------------------------
Rainer Herbst         Linux - Registered
ZEIK                  User #319157
Universität Potsdam   Usual disclaimers applies!
------------------------------------------------

Received on Tue Aug 26 2003 - 03:21:24 CDT