|
文档课题：drop user与drop user cascade的区别.
oracle数据库删除用户有dropuser username和drop user username cascade命令，它们有何区别呢？
官方文档：
Droppinga Database User: Example If user Sidney's schema contains no objects, thenyou can drop sidney by issuing the statement:
DROP USER sidney;
If Sidney's schema containsobjects, then you must use the CASCADE clause to drop sidney and the objects:
DROP USER sidney CASCADE;
当用户下没有数据库对象时，可以用dropuser username删除用户，但当该用户有对象时，就要用drop user username cascade删除用户.
测试如下：
数据库：oracle11.2.0.4 64位
系统：centos7.9 64位
1、drop user测试
SQL>create user liujun identified by liujun;
Usercreated.
SQL>select object_type,count(*) from all_objects where owner='LIUJUN' group byobject_type;
no rowsselected
SQL>drop user liujun;
Userdropped.
说明：用户LIUJUN无数据库对象，dropuser命令删除成功.
2、drop user cascade测试
SQL>create user liujun identified by liujun;
Usercreated.
SQL>grant connect,resource,unlimited tablespace to liujun;
Grantsucceeded.
SQL>conn liujun/liujun;
Connected.
SQL>select * from user_sys_privs;
USERNAME PRIVILEGE ADM
---------------------------------------------------------------------- ---
LIUJUN UNLIMITEDTABLESPACE NO
SQL>select * from session_privs;
PRIVILEGE
----------------------------------------
CREATESESSION
UNLIMITEDTABLESPACE
CREATETABLE
CREATECLUSTER
CREATESEQUENCE
CREATEPROCEDURE
CREATETRIGGER
CREATETYPE
CREATEOPERATOR
CREATEINDEXTYPE
10 rowsselected.
SQL> createtable test (id number(6,2),
2 name varchar2(20));
Tablecreated.
SQL>begin
2 for i in 1..1000 loop
3 insert into test values (i,'a' || i);
4 end loop;
5 end;
6 /
PL/SQLprocedure successfully completed.
SQL>commit;
Commitcomplete.
SQL>conn / as sysdba
Connected.
SQL>select object_type,count(*) from all_objects where owner='LIUJUN' group byobject_type;
OBJECT_TYPE COUNT(*)
-----------------------------
TABLE 1
SQL>drop user liujun;
drop userliujun
*
ERROR atline 1:
ORA-01922:CASCADE must be specified to drop 'LIUJUN'
SQL>drop user liujun cascade;
Userdropped.
说明：当用户LIUJUN有数据库对象时，删除该用户就需要使用drop user cascade. |
本文详细介绍了在Oracle数据库中，使用DROP USER和DROP USER CASCADE命令删除用户时的区别。当用户没有数据库对象时，可以直接使用DROP USER删除；若有对象，则必须使用DROP USER CASCADE，以确保连同所有对象一起删除。通过实例测试，展示了两种情况下的删除操作及其结果。
扫一扫
3943
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
