关于drop user的cascade选项解惑

在数据库中，有时候需要删除用户，大多数时候都需要使用cascade选项，有些时候却不需要，想知道在这个简单的命令之后数据库倒底在干什么，
这时候给一些指定的操作加上trace就跟把那层纱布揭开一样，可以很清楚看到数据库倒底在干些什么。
10046,sql_trace等都是一些不错的选择。
首先来复现一下这个问题。‘
创建一个新用户，然后马上删除。
SQL> create user jeanron identified by jeanron;
User created.

SQL> drop user jeanron;
User dropped.

###########################
可以看到删除是不需要cascade选项的。如果想加cascade选项，会不会有问题呢。
SQL> create user jeanron identified by jeanron;
User created.

SQL> drop user jeanron cascade;
User dropped.

###########################
可以看到是没有问题的，下面来复现 ORA-01922 这个错误。
SQL> create user jeanron identified by jeanron;
User created.

SQL> grant connect,resource to jeanron;
Grant succeeded.

SQL> conn jeanron/jeanron
Connected.
SQL> create table tt as select *from cat;
Table created.
SQL> conn / as sysdba
Connected.
SQL> drop user jeanron;
drop user jeanron
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'JEANRON'

############################
给这条命令做一个sql_trace，或者10046事件
如果在做sql_trace，10046事件的时候出现下面的情况。
SQL> alter session set sql_trace=true;
ERROR:
ORA-01031: insufficient privileges

SQL> alter session set events '10046 trace name context forever,level 12';
ERROR:
ORA-01031: insufficient privileges

只需要赋予alter session的权限就可以了。
SQL> grant alter session to jeanron;
Grant succeeded.

SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.

SQL> drop user jeanron;
drop user jeanron
*
ERROR at line 1:
ORA-01922: CASCADE must be specified to drop 'JEANRON'

SQL> alter session set events '10046 trace name context off';
Session altered.

在trace目录下找到最新的trace日志，使用tkprof来格式化一下。
[ora11g@rac1 trace]$ tkprof TEST01_ora_5888.trc
output = b.log
TKPROF: Release 11.2.0.3.0 - Development on Wed Apr 23 07:42:26 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

可以看到格式化之后的日志文件内容就比较清晰了。可以在开头就看到如下的一段，命令开始了。
SQL ID: 6vu28ambaxjjh Plan Hash: 0
drop user jeanron

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        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0


Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net break/reset to client                   2        0.00          0.00
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       13.27         13.27
********************************************************************************

.....
如下的部分就是一些相应的递归sql。
select user#,password,datats#,tempts#,type#,defrole,resource$, ptime,
  decode(defschclass,NULL,'DEFAULT_CONSUMER_GROUP',defschclass),spare1,spare4,
  ext_username,spare2 
from
 user$ 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 mode: CHOOSE
Parsing user id: SYS   (recursive depth: 1)
Number of plan statistics captured: 1


Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID USER$ (cr=2 pr=0 pw=0 time=152 us cost=1 size=101 card=1)
         1          1          1   INDEX UNIQUE SCAN I_USER1 (cr=1 pr=0 pw=0 time=98 us cost=0 size=0 card=1)(object id 46)


********************************************************************************

可以看到在最后面有这样一条sql的信息。
SQL ID: 8mj6vafkkhyr4 Plan Hash: 310931108

select null 
from
 obj$ where owner#=:1 and type#!=10 union all select null from link$ where 
  owner#=:1 union all select null from streams$_capture_process where 
  capture_userid=:1 union all select null from streams$_apply_process where 
  apply_userid=:1 union all select null from wri$_sqlset_definitions s,user$ 
  u where s.owner = u.name   and u.user#=:1 union all select null from 
  wri$_adv_tasks where owner#=: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         41          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.00       0.00          0         41          0           1


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


Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  UNION-ALL  (cr=41 pr=0 pw=0 time=2554 us)
         1          1          1   INDEX FAST FULL SCAN I_OBJ1 (cr=41 pr=0 pw=0 time=2510 us cost=12 size=10367 card=1481)(object id 36)
         0          0          0   INDEX RANGE SCAN I_LINK1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=13 card=1)(object id 138)
         0          0          0   TABLE ACCESS FULL STREAMS$_CAPTURE_PROCESS (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
         0          0          0   TABLE ACCESS FULL STREAMS$_APPLY_PROCESS (cr=0 pr=0 pw=0 time=0 us cost=2 size=13 card=1)
         0          0          0   NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us)
         0          0          0    NESTED LOOPS  (cr=0 pr=0 pw=0 time=0 us cost=0 size=34 card=1)
         0          0          0     INDEX FULL SCAN WRI$_SQLSET_DEFINITIONS_IDX_01 (cr=0 pr=0 pw=0 time=0 us cost=0 size=17 card=1)(object id 6242)
         0          0          0     INDEX UNIQUE SCAN I_USER1 (cr=0 pr=0 pw=0 time=0 us cost=0 size=0 card=1)(object id 46)
         0          0          0    TABLE ACCESS BY INDEX ROWID USER$ (cr=0 pr=0 pw=0 time=0 us cost=0 size=17 card=1)
         0          0          0   INDEX RANGE SCAN WRI$_ADV_TASKS_IDX_02 (cr=0 pr=0 pw=0 time=0 us cost=1 size=38 card=19)(object id 5811)

自己好好琢磨了下，这里面还这有些文章。它会从回收站，各数据字典基表中查询是否有相应的Object和db link,stream对象。
为了方便查看起见，我把sql整理了一下，可以用如下的方式。如果有返回结果，说明需要cascade选项。
column userid new_value userid format 999999;
select user# userid from user$ where name='A';  
select 'drop user need cascade option'
    from
     obj$ where owner#=&userid and type#!=10
    union all 
    select 'drop user need cascade option' from link$ where 
      owner#=&userid
    union all 
    select 'drop user need cascade option' from streams$_capture_process where 
      capture_userid=&userid
    union all 
    select 'drop user need cascade option' from streams$_apply_process where 
      apply_userid=&userid 
    union all 
    select 'drop user need cascade option' from wri$_sqlset_definitions s,user$ 
      u where s.owner = u.name   and u.user#=&userid 
    union all 
    select 'drop user need cascade option' from 
      wri$_adv_tasks where owner#=&userid
    /

N
-

如果返回now rows selected 就表明不需要cascade.
SQL> select 'drop user need cascade option'
  2      from
  3       obj$ where owner#=&userid and type#!=10
  4      union all 
  5      select 'drop user need cascade option' from link$ where 
  6        owner#=&userid
  7      union all 
  8      select 'drop user need cascade option' from streams$_capture_process where 
  9        capture_userid=&userid
 10      union all 
 11      select 'drop user need cascade option' from streams$_apply_process where 
 12        apply_userid=&userid 
 13      union all 
 14      select 'drop user need cascade option' from wri$_sqlset_definitions s,user$ 
 15        u where s.owner = u.name   and u.user#=&userid 
 16      union all 
 17      select 'drop user need cascade option' from 
 18        wri$_adv_tasks where owner#=&userid
 19  /
old   3:      obj$ where owner#=&userid and type#!=10
new   3:      obj$ where owner#=        53 and type#!=10
old   6:       owner#=&userid
new   6:       owner#=        53
old   9:       capture_userid=&userid
new   9:       capture_userid=        53
old  12:       apply_userid=&userid
new  12:       apply_userid=        53
old  15:       u where s.owner = u.name   and u.user#=&userid
new  15:       u where s.owner = u.name   and u.user#=        53
old  18:       wri$_adv_tasks where owner#=&userid
new  18:       wri$_adv_tasks where owner#=        53


no rows selected

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23718752/viewspace-1148168/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23718752/viewspace-1148168/