Oracle: drop user xx 和 drop user xx cascade的区别

最新推荐文章于 2024-09-13 10:27:59 发布

iteye_1195

最新推荐文章于 2024-09-13 10:27:59 发布

阅读量668

点赞数

CC 4.0 BY-SA版权

分类专栏： Oracle 文章标签： Oracle SQL .net Blog

本文链接：https://blog.youkuaiyun.com/iteye_1195/article/details/82066598

Oracle 专栏收录该内容

10 篇文章

订阅专栏

本文详细介绍了Oracle中drop user与drop user cascade的区别。后者不仅删除用户，还会递归删除该用户的所有对象，包括表、索引及触发器等，并且会处理与其他用户模式中的相关对象依赖。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Oracle: drop user xx 和 drop user xx cascade的区别：

SQL> drop user xx;

drop user xx

ERROR 位于第1行:

ORA-01922: 必须指定 CASCADE 以删除 ‘xx’

SQL> drop user xx cascade;

用户已丢弃。

【解释】

drop user xx

Specify the user to be dropped. Oracle Database does not drop users whose schemas contain objects unless you specify CASCADE or unless you first explicitly drop the user's objects.

drop user xx cascade

Specify CASCADE to drop all objects in the user's schema before dropping the user. You must specify this clause to drop a user whose schema contains any objects.

使用cascade参数可以删除该用户的全部objects。

要说明的如下：

1. If the user's schema contains tables, then Oracle Database drops the tables and automatically drops any referential integrity constraints on tables in other schemas that refer to primary and unique keys on these tables.

如果用户的schema中有表，则在删除表的时候自动删除与该表相关的主键和外键。

2. If this clause results in tables being dropped, then the database also drops all domain indexes created on columns of those tables and invokes appropriate drop routines.
如果用户的schema中有表，则在删除表的时候自动删除与该表相关的索引。

3. Oracle Database invalidates, but does not drop, the following objects in other schemas:
删除用户时，下列在其他用户中的objects不会被删除，只会被置为无效。

1) Views or synonyms for objects in the dropped user's schema
视图，同义词

2) Stored procedures, functions, or packages that query objects in the dropped user's schema
存储过程，函数，包

4. Oracle Database does not drop materialized views in other schemas that are based on tables in the dropped user's schema. However, because the base tables no longer exist, the materialized views in the other schemas can no longer be refreshed.
其他用户建立的基于被删除用户的物化视图不会被删除，只是不能在刷新了。

5. Oracle Database drops all triggers in the user's schema.
用户模式下的所有触发器全部被删除。

6. Oracle Database does not drop roles created by the user.
被删除用户建立的其他用户不会被删除

摘自：http://blog.youkuaiyun.com/programpoet/archive/2009/04/24/4106171.aspx