关于drop user的cascade选项解惑(52天)

在数据库中,有时候需要删除用户,大多数时候都需要使用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

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-04-24

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏数据库新发现

如何使用USE_CONCAT提示

USE_CONCAT提示强迫优化器扩展查询中的每一个OR谓词为独立的查询块. 最后合并所有查询块的结果,返回结果集给用户。

1152
来自专栏杨建荣的学习笔记

归档和非归档模式下ORA-01145错误的解决方法(87天)

总结了一下,在归档和非归档的场景下,ora-01145这个错误可能有如下三种情况: 1.off line tablespace --在非归档模式下尝试ofli...

3076
来自专栏数据和云

补偏救弊 | 关于一致性读与语句性能关系的一大误区

作者简介 黄玮(Fuyuncat) 资深 Oracle DBA,致力于数据库底层技术的研究,其作品获得广大同行的高度评价。 个人网站 www.HelloDB...

2574
来自专栏杨建荣的学习笔记

执行计划的偏差导致的性能问题(r3笔记第12天)

在生产环境中有一条sql语句,查看执行计划来看,效果还是可以接受的。 sql语句类似下面的样子,可以看到里面还使用了比较纠结的外连接。从执行计划来说,默认是走n...

2694
来自专栏Jerry的SAP技术分享

如何用ABAP代码读取CDS view association的数据

我有如下一个CDS view, 这个view的数据来自CRMD_ORDERADM_H, 定义了一个名称为_statushelp的association, 指向了...

3356
来自专栏乐沙弥的世界

使用 EXPLAIN PLAN 获取SQL语句执行计划

     SQL查询语句的性能从一定程度上影响整个数据库的性能。很多情况下,数据库性能的低下差不多都是不良SQL语句所引起。而SQL语句的执行 计划则决定了S...

1075
来自专栏杨建荣的学习笔记

记一次数据库重启后归档急剧增加的问题(98天)

在本地的环境中测试外部表的性能,由于空间有限,不一会儿归档的空间就爆了。然后文件貌似出现了系统级的问题,刚刚rm掉的归档日志文件。隔了几秒钟再ls,就出现了。怎...

3254
来自专栏乐沙弥的世界

Oracle ROWID 方式访问数据库

    和ROWNUM一样,ROWID是一个伪列,即是一个非用户定义的列,而又实际存储于数据库之中。每一个表都有一个ROWID列,一个ROWID值用于 唯一确定...

762
来自专栏数据库新发现

在Mac上通过Docker部署Oracle Database 12.2版本

Oracle 已经宣布支持了Docker部署,这也让我们在Mac上部署 Oracle 数据库有了多一个选择,这是我的第一个Docker应用,非常简便快速的就完...

1176
来自专栏杨建荣的学习笔记

物化视图相关的性能改进 (r7笔记第58天)

今天早上开发的一个同事找到我说他早上做了一个统计查询,但是感觉速度很慢,已经过了一个小时了还没有反应。想让我看看是什么情况。 我通过v$session查到有一个...

3305

扫码关注云+社区