重庆思庄Oracle、Redhat认证学习论坛

标题: drop user与drop user cascade的区别 [打印本页]

作者: denglj 时间: 2022-11-7 13:51
标题: drop user与drop user cascade的区别

文档课题：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、Redhat认证学习论坛 (http://bbs.cqsztech.com/)