| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: drop user hangs
Yong Huang schrieb:
> Rainer Herbst <rherbst_@_rz.uni-potsdam.de> wrote in message news:<bifsrn$oou$1_at_zeppelin.rz.uni-potsdam.de>...
>
>>Yong Huang schrieb: >> >>>Rainer Herbst <rherbst_@_rz.uni-potsdam.de> wrote in message news:<bif59m$i3m$1_at_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. >> >> 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.
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
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
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
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 UPDATE (cr=3 r=0 w=0 time=18518 us)
1 INDEX UNIQUE SCAN OBJ#(44) (cr=1 r=0 w=0 time=69 us)(object
id 44)
select name,password,datats#,tempts#,type#,defrole,resource$, ptime, exptime,
ltime, astatus, lcount, decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',
defschclass)
from
user$ where user#=:1
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
0
Fetch 1 0.01 0.00 0 2 0
0
total 3 0.01 0.00 0 2 0
0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS CLUSTER OBJ#(22) (cr=2 r=0 w=0 time=326 us)
1 INDEX UNIQUE SCAN OBJ#(11) (cr=1 r=0 w=0 time=51 us)(object
id 11)
select value$
from
props$ where name='DEFAULT_TEMP_TABLESPACE'
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
0
Fetch 1 0.00 0.00 0 3 0
1
total 3 0.00 0.00 0 3 0
1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS FULL PROPS$ (cr=3 r=0 w=0 time=321 us)
********************************************************************************
select ts#
from
ts$ where name=:1
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
0
Fetch 1 0.00 0.00 0 2 0
1
total 3 0.00 0.00 0 2 0
1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID TS$ (cr=2 r=0 w=0 time=158 us)
1 INDEX UNIQUE SCAN I_TS1 (cr=1 r=0 w=0 time=53 us)(object id 43)
********************************************************************************
select 1
from
sys.streams$_prepare_ddl p where ((p.global_flag = 1 and :1 is null)
or
(p.global_flag = 0 and p.usrid = :2)) and rownum = 1
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
0
Fetch 1 0.00 0.00 0 3 0
0
total 3 0.00 0.00 0 3 0
0
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
0 COUNT STOPKEY (cr=3 r=0 w=0 time=273 us)
0 TABLE ACCESS FULL STREAMS$_PREPARE_DDL (cr=3 r=0 w=0 time=268 us)
********************************************************************************
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,sysevent);
END IF;
END;
call count cpu elapsed disk query current
rows
Parse 2 0.01 0.00 0 0 0
0
Execute 2 0.00 0.00 0 0 0
2
Fetch 0 0.00 0.00 0 0 0
0
total 4 0.01 0.00 0 0 0
2
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
insert into user$(user#,name,password,ctime,ptime,datats#,tempts#,type#,
defrole,resource$,ltime,astatus,lcount,defschclass)
values
(:1,:2,:3,SYSDATE,DECODE(to_char(:4, 'YYYY-MM-DD'), '0000-00-00',
to_date(NULL), :4),:5,:6,:7,:8,:9,DECODE(to_char(:10, 'YYYY-MM-DD'),
'0000-00-00', to_date(NULL), :10),:11,:12,:13)
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 1 4
1
Fetch 0 0.00 0.00 0 0 0
0
total 2 0.00 0.00 0 1 4
1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 1)
drop user test_user
call count cpu elapsed disk query current
rows
Parse 1 0.00 0.00 0 0 0
0
Execute 0 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: 1
Optimizer goal: CHOOSE
Parsing user id: 5
select user#
from
sys.user$ where name = 'OUTLN'
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
0
Fetch 1 0.00 0.00 0 2 0
1
total 3 0.00 0.00 0 2 0
1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: SYS (recursive depth: 2)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID OBJ#(22) (cr=2 r=0 w=0 time=101 us)
1 INDEX UNIQUE SCAN OBJ#(44) (cr=1 r=0 w=0 time=51 us)(object
id 44)
BEGIN
IF (xdb.DBMS_XDBZ.is_hierarchy_enabled(sys.dictionary_obj_owner,
sys.dictionary_obj_name)) THEN
xdb.XDB_PITRIG_PKG.pitrig_truncate(sys.dictionary_obj_owner, sys.dictionary_obj_name);
END IF;
EXCEPTION
WHEN OTHERS THEN
null;
call count cpu elapsed disk query current
rows
Parse 1 0.00 0.00 0 0 0
0
Execute 1 0.02 0.00 0 0 0
1
Fetch 0 0.00 0.00 0 0 0
0
total 2 0.02 0.00 0 0 0
1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 35 (recursive depth: 1)
SELECT COUNT(*)
FROM
ALL_POLICIES V WHERE V.OBJECT_OWNER = :b1 AND V.OBJECT_NAME = :b2 AND
POLICY_NAME LIKE '%xdbrls%'
call count cpu elapsed disk query current
rows
Parse 1 0.01 0.00 0 0 0
0
Execute 1 0.00 0.00 0 0 0
0
Fetch 1 0.00 0.00 0 0 0
1
total 3 0.01 0.00 0 0 0
1
Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 35 (recursive depth: 2)
SELECT COUNT(*)
FROM
USER_POLICIES V WHERE V.OBJECT_NAME = :b1 AND POLICY_NAME LIKE
'%xdbrls%'
-- ------------------------------------------------ Rainer Herbst Linux - Registered ZEIK User #319157 Universität Potsdam Usual disclaimers applies! ------------------------------------------------Received on Wed Aug 27 2003 - 02:51:55 CDT
 |
 |