Oracle Database Discussions

Hello,

I am in Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Prod.

I am trying to drop user cascade and the statement hangs with wait event 'db file scattered read' on a sys query.

I have put a trace on the session where I am trying to do this, and rerun the drop command.
alter session set events '1940 trace name ERRORSTACK level 3';
alter session set events '10046 trace name context forever, level 12';

The relevant section from the tkprof is below:

select o.name,o.type#,o.obj#,o.remoteowner,o.linkname,o.namespace, o.subname
from
obj$ o, tab$ t where o.owner#=:1 and o.type# !=11 and o.type# !=13 and
o.type# !=14 and o.type# !=10 and o.type# !=33 and o.type# !=32 and o.type#
!=8 and o.type# !=9 and o.type# != 7 and o.type# != 38 and o.type# != 22
and o.type# != 28 and o.type# != 29 and o.type# != 30 and o.type# != 55 and
o.obj#=t.obj#(+) and (t.property is null or (bitand(t.property,8192)!=8192
and bitand(t.property,512)!=512 and bitand(t.flags,536870912)!=536870912))
order by o.type# desc


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 115 0.01 0.02 0 0 0 0
Fetch 7501 75.39 825.13 1385179 2455703 0 7500
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7617 75.40 825.15 1385179 2455703 0 7500

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

Rows Row Source Operation
------- ---------------------------------------------------
7500 SORT ORDER BY (cr=2449755 pr=1381735 pw=0 time=822704263 us)
162677 FILTER (cr=2449755 pr=1381735 pw=0 time=818684218 us)
162677 HASH JOIN OUTER (cr=2449755 pr=1381735 pw=0 time=818328045 us)
164036 TABLE ACCESS BY INDEX ROWID OBJ$ (cr=34382 pr=6 pw=0 time=737122 us)
310201 INDEX RANGE SCAN I_OBJ2 (cr=4830 pr=4 pw=0 time=126082 us)(object id 37)
14444378 TABLE ACCESS FULL TAB$ (cr=2421321 pr=1385173 pw=0 time=770578194 us)


I have tried to gather the dictionary statistics and troubleshoot with Metalink ID 1291804.1.
However, none of these helped.

Please assist.

Regards,
Horia Berca