drop user与drop user cascade的区别

denglj

文档课题：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.