删除某用户下所有对象.docVIP

--删除某个用户下的对象?? set?heading?off;?? set?feedback?off;?? spool?c:\dropobj.sql;?? ??prompt?--Drop?constraint?? ?select?alter?table?||table_name||?drop?constraint?||constraint_name||?;?from?user_constraints?where?constraint_type=R;?? ?prompt?--Drop?tables?? ?select?drop?table?||table_name?||;?from?user_tables;??? ??? ?prompt?--Drop?view?? ?select?drop?view??||view_name||;?from?user_views;?? ??? ?prompt?--Drop?sequence?? ?select?drop?sequence??||sequence_name||;?from?user_sequences;??? ??? ?prompt?--Drop?function?? ?select?drop?function??||object_name||;??from?user_objects??where?object_type=FUNCTION;?? ?? ?prompt?--Drop?procedure?? ?select?drop?procedure?||object_name||;?from?user_objects??where?object_type=PROCEDURE;?? ??? ?prompt?--Drop?package?? ?prompt?--Drop?package?body?? ?select?drop?package?||?object_name||;?from?user_objects??where?object_type=PACKAGE;?? ?? ?prompt?--Drop?database?link?? ?select?drop?database?link?||?object_name||;?from?user_objects??where?object_type=DATABASE?LINK;?? spool?off;?? set?heading?on;?? set?feedback?on;?? @@c:\dropobj.sql;?? host?del?c:\dropobj.sql;?? 注释： 1.上面这个语句，在pl/sql里面是放在命令里面执行的。 2.set heading off; 意思就是关闭表头。如果不关闭，写入dropobj.sql文件中就会带有结果集的表头如： DROPTABLE||TABLE_NAME||; ------------------------------------------ drop table TEACHER; 实际上我们需要的是“drop table TEACHER;”，“DROPTABLE||TABLE_NAME||; ”就是表头。 3.set feedback off; 意思就是关闭回显。如果不关闭，写入dropobj.sql文件中就会带有返回结果集的大小等信息，如：137 rows selected 4.spool c:\dropobj.sql; 把结果集写入这个文件。spool off; 结束写入。 5.@@c:\dropobj.sql; 执行这个sql 6.host del c:\dropobj.sql; 删除主机上这文件。 7.CONSTRAINT_TYPE 就是键的类型： Sql代码 C?(check?constraint?on?a?table)??? P?(primary?key)??? U?(unique?key)?? R?(referential?integrity)?? V?(with?check?option,?on?a?view)?? O?(with?read?only,?on?a?view)?? C (check constraint on a table) P (primary key) U (unique key) R (referential integrity) V (with check option, on a view) O (with read only, on a view) 8.当执行drop package ………… 这句时，package body会被同时删除。