本文详细介绍了在Oracle数据库中如何正确地删除用户及其相关对象。包括使用drop user命令的基本语法及cascade选项的功能，解释了不同情况下使用cascade选项的影响，如自动删除相关联的表、触发器等。
<p>SQL> delete user itp2;<br>delete user itp2<br> *<br>第 1 行出现错误:<br>ORA-00903: 表名无效</p>
<p>SQL> drop user itp2;<br>drop user itp2<br>*<br>第 1 行出现错误:<br>ORA-01940: 无法删除当前已连接的用户</p>
<p>SQL> drop user itp2;<br>用户已删除。<br>SQL> drop user itp;<br>drop user itp<br>*<br>第 1 行出现错误:<br>ORA-01922: 必须指定 CASCADE 以删除 'ITP'</p>
<p>SQL> drop user itp cascade;<br>用户已删除。<br>SQL> <br>drop user ； 仅仅是删除用户，<br>drop user ×× cascade ；会删除此用户名下的所有表和视图。</p>
<p>user<br>Specify the user to be dropped. Oracle Database does not drop users whose schemas <br>contain objects unless you specify CASCADE or unless you first explicitly drop the <br>user's objects.</p>
<p>CASCADE <br>Specify CASCADE to drop all objects in the user's schema before dropping the user. You <br>must specify this clause to drop a user whose schema contains any objects. </p>
<p>使用cascade参数可以删除该用户的全部objects。要说明的如下：</p>
<p>1 If the user's schema contains tables, then Oracle Database drops the tables and <br>automatically drops any referential integrity constraints on tables in other schemas <br>that refer to primary and unique keys on these tables. </p>
<p>如果用户的schema中有表，则在删除表的时候自动删除与该表相关的主键和外键。</p>
<p>2 If this clause results in tables being dropped, then the database also drops all <br>domain indexes created on columns of those tables and invokes appropriate drop <br>routines. <br>如果用户的schema中有表，则在删除表的时候自动删除与该表相关的索引。</p>
<p><br>3 Oracle Database invalidates, but does not drop, the following objects in other <br>schemas:<br>删除用户时，下列在其他用户中的objects不会被删除，只会被置为无效</p>
<p>1 Views or synonyms for objects in the dropped user's schema<br>视图，同义词</p>
<p>2 Stored procedures, functions, or packages that query objects in the dropped <br>user's schema<br>存储过程，函数，包</p>
<p>4 Oracle Database does not drop materialized views in other schemas that are based <br>on tables in the dropped user's schema. However, because the base tables no <br>longer exist, the materialized views in the other schemas can no longer be <br>refreshed.<br>其他用户建立的基于被删除用户的物化视图不会被删除，只是不能在刷新了。</p>
<p>5 Oracle Database drops all triggers in the user's schema.<br>用户模式下的所有触发器全部被删除</p>
<p>6 Oracle Database does not drop roles created by the user. <br>被删除用户建立的其他用户不会被删除</p>
<p><br>本文来自优快云博客，转载请标明出处：<a href="http://blog.youkuaiyun.com/programpoet/archive/2009/04/24/4106171.aspx">http://blog.youkuaiyun.com/programpoet/archive/2009/04/24/4106171.aspx</a></p>
<p>SQL> drop user itp2;<br>drop user itp2<br>*<br>第 1 行出现错误:<br>ORA-01940: 无法删除当前已连接的用户</p>
<p>SQL> drop user itp2;<br>用户已删除。<br>SQL> drop user itp;<br>drop user itp<br>*<br>第 1 行出现错误:<br>ORA-01922: 必须指定 CASCADE 以删除 'ITP'</p>
<p>SQL> drop user itp cascade;<br>用户已删除。<br>SQL> <br>drop user ； 仅仅是删除用户，<br>drop user ×× cascade ；会删除此用户名下的所有表和视图。</p>
<p>user<br>Specify the user to be dropped. Oracle Database does not drop users whose schemas <br>contain objects unless you specify CASCADE or unless you first explicitly drop the <br>user's objects.</p>
<p>CASCADE <br>Specify CASCADE to drop all objects in the user's schema before dropping the user. You <br>must specify this clause to drop a user whose schema contains any objects. </p>
<p>使用cascade参数可以删除该用户的全部objects。要说明的如下：</p>
<p>1 If the user's schema contains tables, then Oracle Database drops the tables and <br>automatically drops any referential integrity constraints on tables in other schemas <br>that refer to primary and unique keys on these tables. </p>
<p>如果用户的schema中有表，则在删除表的时候自动删除与该表相关的主键和外键。</p>
<p>2 If this clause results in tables being dropped, then the database also drops all <br>domain indexes created on columns of those tables and invokes appropriate drop <br>routines. <br>如果用户的schema中有表，则在删除表的时候自动删除与该表相关的索引。</p>
<p><br>3 Oracle Database invalidates, but does not drop, the following objects in other <br>schemas:<br>删除用户时，下列在其他用户中的objects不会被删除，只会被置为无效</p>
<p>1 Views or synonyms for objects in the dropped user's schema<br>视图，同义词</p>
<p>2 Stored procedures, functions, or packages that query objects in the dropped <br>user's schema<br>存储过程，函数，包</p>
<p>4 Oracle Database does not drop materialized views in other schemas that are based <br>on tables in the dropped user's schema. However, because the base tables no <br>longer exist, the materialized views in the other schemas can no longer be <br>refreshed.<br>其他用户建立的基于被删除用户的物化视图不会被删除，只是不能在刷新了。</p>
<p>5 Oracle Database drops all triggers in the user's schema.<br>用户模式下的所有触发器全部被删除</p>
<p>6 Oracle Database does not drop roles created by the user. <br>被删除用户建立的其他用户不会被删除</p>
<p><br>本文来自优快云博客，转载请标明出处：<a href="http://blog.youkuaiyun.com/programpoet/archive/2009/04/24/4106171.aspx">http://blog.youkuaiyun.com/programpoet/archive/2009/04/24/4106171.aspx</a></p>
扫一扫
498
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
