drop user hangs

Rainer Herbst

unread,

Aug 25, 2003, 7:02:57 AM8/25/03

to

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
2. user exists, instance just started
--> the drop user takes about 30 sec, but executes without error, the
second statement executes without error
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!

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

Brian Peasland

unread,

Aug 25, 2003, 12:11:55 PM8/25/03

to

> Following behaviour:
> 1. user does not exists
> --> error in the first statement, second statement executed

This is expected. Since the user did not exist, the DROP USER command
should raise an appropriate error message. You can't drop what is not
there.

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

What error is being raised on the CREATE USER statement? It's pretty
close to impossible to diagnose your problem without any further
information. Any advice would simply be a guess.

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

You'll have to find out what the DROP USER command is waiting on. In
another session, you'll have to query V$SESSION_WAIT to find out.

HTH,Brian

--
===================================================================

Brian Peasland
dba@remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.

"I can give it to you cheap, quick, and good. Now pick two out of
the three"

Daniel Morgan

unread,

Aug 25, 2003, 12:20:37 PM8/25/03

to

Rainer Herbst wrote:

I don't even have a clue, after reading your post three times, what you are
doing and what is not working as expected.

Surely you don't expect to be logged on as a new user after doing what you
indicate. At minimum you would need:

conn / as sysdba
drop user ...
create user ...
grant create session to user ...
conn x/y@z

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
http://www.outreach.washington.edu/extinfo/certprog/aoa/aoa_main.asp
damo...@x.washington.edu
(replace 'x' with a 'u' to reply)

Howard J. Rogers

unread,

Aug 25, 2003, 4:16:29 PM8/25/03

to

"Rainer Herbst" <rherbst_@_rz.uni-potsdam.de> wrote in message
news:bicqd3$rd2$1...@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

Rainer Herbst

unread,

Aug 26, 2003, 4:21:24 AM8/26/03

to

Howard J. Rogers schrieb:

> "Rainer Herbst" <rherbst_@_rz.uni-potsdam.de> wrote in message
> news:bicqd3$rd2$1...@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.

Yong Huang

unread,

Aug 26, 2003, 9:15:35 AM8/26/03

to

Rainer Herbst <rherbst_@_rz.uni-potsdam.de> wrote in message news:<bif59m$i3m$1...@zeppelin.rz.uni-potsdam.de>...

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

Can you show us p1,p2,p3 for this "null" event? If they change with
time, what do they change to? 9i has the bug that lumps some other
events into a null event. But we may be able to guess based on the
patterns of their parameters.

Unless I missed, did you trace your session doing DROP USER?

Yong Huang

Rainer Herbst

unread,

Aug 26, 2003, 11:03:38 AM8/26/03

to

Yong Huang schrieb:

1 select sid, event, p1, p2, p3 , seconds_in_wait
2* from v$session_wait where sid=14

SID EVENT
P1 P2
P3 SECONDS_IN_WAIT
----------
----------------------------------------------------------------
---------- ---------- ---------- ---------------
14 null event
1650815232 1
0 1500

I haven't seen any changes in p1, p2 and p3.

>
> Unless I missed, did you trace your session doing DROP USER?
>
> Yong Huang

All information about this session retrieved by oracletool.pl:

Session wait information.
Seq# Event Seconds waiting
35 null event 805

Session I/O information.
Block gets Consistent gets Physical reads Block changes Consistent changes
3 2,129 0
4 0

Open cursors.
SQL text
SELECT COUNT(*) FROM ALL_POLICIES V WHERE V.OBJECT_OWNER
SELECT COUNT(*) FROM USER_POLICIES V WHERE V.OBJECT_NAME

Objects being accessed by SID 14.
Object name Object type Owner
ALL_POLICIES SYNONYM PUBLIC
DBMS_OUTPUT SYNONYM PUBLIC
DBMS_RLS CURSOR PUBLIC
SYS NON-EXISTENT PUBLIC
SYSTEM CURSOR PUBLIC
USER_POLICIES SYNONYM PUBLIC
ALL_OBJECTS VIEW SYS
ALL_POLICIES VIEW SYS
ALL_REPCOLUMN CURSOR SYS
ALL_REPGENOBJECTS CURSOR SYS
ALL_REPOBJECT CURSOR SYS
ALL_SNAPSHOTS VIEW SYS
ALL_SYNONYMS VIEW SYS
ALL_TABLES VIEW SYS
ALL_TAB_COLUMNS VIEW SYS
ALL_VIEWS VIEW SYS
ATTRCOL$ TABLE SYS
AUDIT$ TABLE SYS
CCOL$ TABLE SYS
CDC_CHANGE_TABLES$ TABLE SYS
CDC_SUBSCRIBERS$ TABLE SYS
CDEF$ TABLE SYS
COL$ TABLE SYS
DBA_ANALYZE_OBJECTS CURSOR SYS
DBA_INDEXES VIEW SYS
DBA_IND_PARTITIONS CURSOR SYS
DBA_JOBS VIEW SYS
DBA_OBJECTS VIEW SYS
DBA_POLICIES VIEW SYS
DBA_REGISTERED_SNAPSHOTS CURSOR SYS
DBA_REGISTRY CURSOR SYS
DBA_REPFLAVOR_OBJECTS CURSOR SYS
DBA_REPGROUP_PRIVILEGES VIEW SYS
DBA_REPOBJECT CURSOR SYS
DBA_REPSITES CURSOR SYS
DBA_SNAPSHOTS VIEW SYS
DBA_SNAPSHOT_LOGS VIEW SYS
DBA_SYNONYMS VIEW SYS
DBA_TAB_PARTITIONS CURSOR SYS
DBA_TYPES CURSOR SYS
DBA_USERS VIEW SYS
DBMS_ALERT PACKAGE SYS
DBMS_ALERT_INFO TABLE SYS
DBMS_APPLICATION_INFO PACKAGE SYS
DBMS_ASYNCRPC_PUSH CURSOR SYS
DBMS_CDCAPI_LIB CURSOR SYS
DBMS_CDC_PUBLISH PACKAGE SYS
DBMS_CDC_UTILITY PACKAGE SYS
DBMS_DDL PACKAGE SYS
DBMS_DEFERGEN CURSOR SYS
DBMS_DEFERGEN_UTIL CURSOR SYS
DBMS_DEFER_IMPORT_INTERNAL PACKAGE SYS
DBMS_DEFER_QUERY_UTL CURSOR SYS
DBMS_DEFER_REPCAT CURSOR SYS
DBMS_DEFER_SYS CURSOR SYS
DBMS_DEFER_SYS_PART1 CURSOR SYS
DBMS_FLASHBACK CURSOR SYS
DBMS_IJOB PACKAGE SYS
DBMS_INTERNAL_TRIGGER PACKAGE SYS
DBMS_IREFRESH PACKAGE SYS
DBMS_ISNAPSHOT PACKAGE SYS
DBMS_JOB PACKAGE SYS
DBMS_LOB CURSOR SYS
DBMS_LOCK PACKAGE SYS
DBMS_LOCK_ALLOCATED CURSOR SYS
DBMS_LOCK_ID CURSOR SYS
DBMS_LOGSTDBY CURSOR SYS
DBMS_OUTPUT PACKAGE SYS
DBMS_PIPE PACKAGE SYS
DBMS_REFRESH CURSOR SYS
DBMS_REGISTRY CURSOR SYS
DBMS_REGISTRY_SYS PACKAGE SYS
DBMS_REPCAT_ADMIN PACKAGE SYS
DBMS_REPCAT_CACHE CURSOR SYS
DBMS_REPCAT_COMMON_UTL PACKAGE SYS
DBMS_REPCAT_DECL PACKAGE SYS
DBMS_REPCAT_FLA CURSOR SYS
DBMS_REPCAT_FLA_UTL CURSOR SYS
DBMS_REPCAT_INTERNAL PACKAGE SYS
DBMS_REPCAT_INTERNAL_PKG_LIB CURSOR SYS
DBMS_REPCAT_OBJ_UTL CURSOR SYS
DBMS_REPCAT_RGT CURSOR SYS
DBMS_REPCAT_RGT_CHK CURSOR SYS
DBMS_REPCAT_RGT_CUST CURSOR SYS
DBMS_REPCAT_RGT_UTL PACKAGE SYS
DBMS_REPCAT_SNA_UTL PACKAGE SYS
DBMS_REPCAT_SQL_UTL CURSOR SYS
DBMS_REPCAT_UTL PACKAGE SYS
DBMS_REPUTIL PACKAGE SYS
DBMS_REPUTIL2 CURSOR SYS
DBMS_RLS CURSOR SYS
DBMS_SESSION PACKAGE SYS
DBMS_SNAPSHOT CURSOR SYS
DBMS_SNAP_INTERNAL CURSOR SYS
DBMS_SQL PACKAGE SYS
DBMS_STANDARD PACKAGE SYS
DBMS_SYSTEM PACKAGE SYS
DBMS_SYS_ERROR CURSOR SYS
DBMS_SYS_SQL PACKAGE SYS
DBMS_UTILITY PACKAGE SYS
DEFPROPAGATOR VIEW SYS
DEPENDENCY$ TABLE SYS
DUAL TABLE SYS
DUC$ TABLE SYS
GENERATOR$_S CURSOR SYS
GET_DEP_DT1 CURSOR SYS
GET_DEP_DT2 CURSOR SYS
GV$ENABLEDPRIVS VIEW SYS
ICOL$ TABLE SYS
IND$ TABLE SYS
JOB$ TABLE SYS
MLOG$ TABLE SYS
MLOG_REFCOL$ TABLE SYS
OBJ$ TABLE SYS
OBJAUTH$ TABLE SYS
OPQTYPE$ TABLE SYS
PLITBLM PACKAGE SYS
PROFILE$ TABLE SYS
PROFNAME$ TABLE SYS
REGISTRY$ TABLE SYS
REG_SNAP$ TABLE SYS
REPCAT$_CDEF CURSOR SYS
REPCAT_GENERATED VIEW SYS
REPCAT_REPOBJECT CURSOR SYS
REPCAT_REPOBJECT_BASE CURSOR SYS
RGCHILD$ TABLE SYS
RGROUP$ TABLE SYS
RGROUPSEQ CURSOR SYS
RLS$ TABLE SYS
SEG$ TABLE SYS
SESSION_CONTEXT CURSOR SYS
SLOG$ TABLE SYS
SNAP$ TABLE SYS
SNAP_COLMAP$ TABLE SYS
SNAP_OBJCOL$ TABLE SYS
SNAP_REFOP$ TABLE SYS
SNAP_REFTIME$ TABLE SYS
SNAP_SITE$ TABLE SYS
STANDARD PACKAGE SYS
SYN$ TABLE SYS
SYS NON-EXISTENT SYS
SYSAUTH$ TABLE SYS
SYSTEM CURSOR SYS
SYSTEM_PRIVILEGE_MAP TABLE SYS
SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE SYS
TAB$ TABLE SYS
TS$ TABLE SYS
TYPE$ TABLE SYS
TYPED_VIEW$ TABLE SYS
USER$ TABLE SYS
USER_ASTATUS_MAP TABLE SYS
USER_POLICIES VIEW SYS
UTL_RAW CURSOR SYS
V$ACTIVE_INSTANCES CURSOR SYS
V$ENABLEDPRIVS VIEW SYS
V$INSTANCE VIEW SYS
V$PARAMETER VIEW SYS
VIEW$ TABLE SYS
X$KGLOB TABLE SYS
X$KZSPR TABLE SYS
X$KZSRO TABLE SYS
_DBA_REPL_NESTED_TABLE_NAMES VIEW SYS
DEF$_AQCALL TABLE SYSTEM
DEF$_DESTINATION TABLE SYSTEM
DEF$_PROPAGATOR TABLE SYSTEM
REPCAT$_AUDIT_ATTRIBUTE TABLE SYSTEM
REPCAT$_AUDIT_COLUMN TABLE SYSTEM
REPCAT$_COLUMN_GROUP TABLE SYSTEM
REPCAT$_CONFLICT TABLE SYSTEM
REPCAT$_DDL TABLE SYSTEM
REPCAT$_EXTENSION TABLE SYSTEM
REPCAT$_FLAVORS TABLE SYSTEM
REPCAT$_FLAVOR_OBJECTS TABLE SYSTEM
REPCAT$_GENERATED TABLE SYSTEM
REPCAT$_GROUPED_COLUMN TABLE SYSTEM
REPCAT$_KEY_COLUMNS TABLE SYSTEM
REPCAT$_OBJECT_TYPES TABLE SYSTEM
REPCAT$_PARAMETER_COLUMN TABLE SYSTEM
REPCAT$_PRIORITY CURSOR SYSTEM
REPCAT$_PRIORITY_GROUP TABLE SYSTEM
REPCAT$_REFRESH_TEMPLATES TABLE SYSTEM
REPCAT$_REFRESH_TEMPLATES_S CURSOR SYSTEM
REPCAT$_REPCAT TABLE SYSTEM
REPCAT$_REPCATLOG TABLE SYSTEM
REPCAT$_REPCOLUMN TABLE SYSTEM
REPCAT$_REPGROUP_PRIVS TABLE SYSTEM
REPCAT$_REPOBJECT TABLE SYSTEM
REPCAT$_REPPROP TABLE SYSTEM
REPCAT$_REPPROP_KEY CURSOR SYSTEM
REPCAT$_REPSCHEMA TABLE SYSTEM
REPCAT$_RESOLUTION TABLE SYSTEM
REPCAT$_RESOLUTION_METHOD TABLE SYSTEM
REPCAT$_RESOLUTION_STATISTICS CURSOR SYSTEM
REPCAT$_RESOL_STATS_CONTROL CURSOR SYSTEM
REPCAT$_SITES_NEW TABLE SYSTEM
REPCAT$_SITE_OBJECTS TABLE SYSTEM
REPCAT$_TEMPLATE_OBJECTS TABLE SYSTEM
REPCAT$_TEMPLATE_OBJECTS_S CURSOR SYSTEM
REPCAT$_TEMPLATE_PARMS TABLE SYSTEM
REPCAT$_TEMPLATE_PARMS_S CURSOR SYSTEM
REPCAT$_TEMPLATE_SITES TABLE SYSTEM
REPCAT$_TEMPLATE_SITES_S CURSOR SYSTEM
REPCAT$_TEMPLATE_TYPES CURSOR SYSTEM
REPCAT$_USER_AUTHORIZATIONS CURSOR SYSTEM
REPCAT$_USER_AUTHORIZATIONS_S CURSOR SYSTEM
REPCAT$_USER_PARM_VALUES CURSOR SYSTEM
REPCAT$_USER_PARM_VALUES_S CURSOR SYSTEM
REPCATLOGTRIG TRIGGER SYSTEM
SYS NON-EXISTENT SYSTEM
ALL_POLICIES NON-EXISTENT XDB
DBMS_OUTPUT CURSOR XDB
DBMS_RLS CURSOR XDB
DBMS_XDBZ PACKAGE XDB
DBMS_XDBZ0 PACKAGE XDB
SECURITY_LIB CURSOR XDB
USER_POLICIES NON-EXISTENT XDB

Session statistics for SID '14'. Only non-zero values displayed.
Parameter name Value Class
bytes received via SQL*Net from client 2,881 User
bytes sent via SQL*Net to client 2,850 User
CPU used by this session 7 User
logons cumulative 1 User
logons current 1 User
opened cursors cumulative 109 User
opened cursors current 53 User
recursive calls 3,382 User
recursive cpu usage 74 User
session connect time 1,061,908,281 User
session logical reads 2,132 User
session pga memory 951,840 User
session pga memory max 1,017,376 User
session uga memory 667,344 User
session uga memory max 667,344 User
SQL*Net roundtrips to/from client 16 User
user calls 20 User
user commits 1 User
redo entries 2 Redo
redo size 556 Redo
enqueue conversions 8 Enqueue
enqueue releases 62 Enqueue
enqueue requests 79 Enqueue
commit cleanouts 1 Cache
commit cleanouts successfully completed 1 Cache
consistent gets 2,129 Cache
consistent gets - examination 316 Cache
db block changes 4 Cache
db block gets 3 Cache
free buffer requested 1 Cache
redo synch time 5 Cache
redo synch writes 1 Cache
shared hash latch upgrades - no wait 218 Cache
switch current to new buffer 1 Cache
calls to get snapshot scn: kcmgss 403 Parallel server
cluster key scan block gets 136 SQL
cluster key scans 74 SQL
execute count 312 SQL
parse count (hard) 33 SQL
parse count (total) 132 SQL
parse time cpu 44 SQL
parse time elapsed 45 SQL
rows fetched via callback 17 SQL
sorts (memory) 123 SQL
sorts (rows) 259 SQL
table fetch by rowid 451 SQL
table scan blocks gotten 789 SQL
table scan rows gotten 64,630 SQL
table scans (short tables) 36 SQL
workarea executions - optimal 122 SQL
buffer is not pinned count 1,819
buffer is pinned count 274
calls to kcmgas 2 Debug
CPU used when call started 7 Debug
cursor authentications 10 Debug
deferred (CURRENT) block cleanout applications 1 Debug
index fetch by key 96 Debug
index scans kdiixs1 218 Debug
messages sent 1 Debug
no work - consistent read gets 1,492 Debug
process last non-idle time 1,061,908,281 Debug

Daniel Morgan

unread,

Aug 26, 2003, 11:17:14 AM8/26/03

to

Rainer Herbst wrote:

> Howard J. Rogers schrieb:
> > "Rainer Herbst" <rherbst_@_rz.uni-potsdam.de> wrote in message
> > news:bicqd3$rd2$1...@zeppelin.rz.uni-potsdam.de...
> >
>

> There might be a problem because of the SYSTEM tablespace is filled to
> 98%,

> <snipped>

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

Why? There should be nothing in the SYSTEM tablespace other than the catalog and
stored code from procdures, functions, packages, and triggers: Rarely more than a
few hundred MB.

Run the following:

SELECT table_name
FROM dba_tables
WHERE owner NOT IN ('SYS', 'SYSTEM','WMSYS', 'OUTLN')
AND tablespace_name = 'SYSTEM';

SELECT index_name
FROM dba_indexes
WHERE owner NOT IN ('SYS', 'SYSTEM','WMSYS', 'OUTLN')
AND tablespace_name = 'SYSTEM';

There shouldn't be anything.

Yong Huang

unread,

Aug 26, 2003, 6:11:32 PM8/26/03

to

Rainer Herbst <rherbst_@_rz.uni-potsdam.de> wrote in message news:<bifsrn$oou$1...@zeppelin.rz.uni-potsdam.de>...

OK. This looks like the "useless" event "SQL*Net message to client".
You need to set sql_trace true for the DROP USER command to find out
more. Looks like your oracletool.pl doesn't do that job.

Yong Huang

Joel Garry

unread,

Aug 26, 2003, 6:39:38 PM8/26/03

to

Rainer Herbst <rherbst_@_rz.uni-potsdam.de> wrote in message news:<bif59m$i3m$1...@zeppelin.rz.uni-potsdam.de>...

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

Well, ORA-155x is supposed to be long-gone with undo, but I'm
wondering if something is so messed up it hangs and can't tell you
about it. That could be an error that is supposed to come back to
you, rather than the alert log.

Are you sure you aren't running into a maxsize parameter on your
autoextend ts? I would be worried about a 98% filled system ts just
on general principle, but then again I'm old-fashioned.

jg
--
@home.com is bogus.
http://www.zone-h.org/en/news/read/id=3287/

Daniel Morgan

unread,

Aug 26, 2003, 8:01:43 PM8/26/03

to

Joel Garry wrote:

UNDO decreases the incidence of ORA-01555 but does not eliminate it. And most certainly can not solve the problem
of badly written code such as incremental commits inside a loop.

Rainer Herbst

unread,

Aug 27, 2003, 3:51:55 AM8/27/03

to

very simple test script:

alter session set sql_trace = true;

create user test_user identified by "secret";

drop user test_user;

exit;

The drop user statement hangs again.

Here is the output formated with tkprof:

TKPROF: Release 9.2.0.3.0 - Production on Mi Aug 27 09:12:49 2003

Trace file: knobel_ora_9993.trc
Sort options: default

********************************************************************************
count = number of times OCI procedure was executed
cpu = cpu time in seconds executing
elapsed = elapsed time in seconds executing
disk = number of physical reads of buffers from disk
query = number of buffers gotten for consistent read
current = number of buffers gotten in current mode (usually for update)
rows = number of rows processed by the fetch or execute call
********************************************************************************

alter session set sql_trace = true

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 0 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 1 0.00 0.00 0 0 0
0

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 5
********************************************************************************

create user test_user ide

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.01 0.02 0 0 3
0
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 2 0.01 0.02 0 0 3
0

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 5
********************************************************************************

BEGIN
/* NOP UNLESS A TABLE OBJECT */
IF dictionary_obj_type =
'TABLE' AND sys.dbms_cdc_publish.active > 0
THEN

sys.dbms_cdc_publish.change_table_trigger(dictionary_obj_owner,
dictionary_obj_name,'LOCK');
END IF;
END;

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
1
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 2 0.00 0.00 0 0 0
1

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
********************************************************************************

update user$ set user#=:2
where
name=:1

call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.01 0.01 0 3 22
1
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 2 0.01 0.01 0 3 22
1