ANBOB™

有时需要清除schema里的所有对象，但又没有dba权限,不可以drop user xxx cascade; re-create user;可以写个plsql，循环删除所有对象，注意有些对象存在依赖关系，如外键、index  和table..,这里建个procedure，方便在开发库一堆有schema中运行。

<pre>

conn system/oracle

create or replace procedure purge_schema(p_sch varchar2 default user)
AUTHID CURRENT_USER
/*
author: anbob.com
date: 2012/12/3
desc: drop all object in the schema,but can’t drop sys,sysman,system owner,defult logon user,also specify schema run as dba.

*/
is
v_str varchar2(2000);

procedure p_do ( p_v varchar2)is
begin
dbms_output.put_line(p_v);
execute immediate v_str;
end;

begin
if(UPPER(p_sch) like ‘SYS%’)
then
Raise_Application_Error(-20000, p_sch||”’s objects do”not allow drop!’);
end if;

— disable foreign  key
for c in(select table_name,constraint_name from all_constraints where owner=p_sch and constraint_type=’R’) loop

v_str :=’alter table ‘||p_sch||’.’||c.table_name||’ drop constraint ‘||c.constraint_name;
p_do (v_str);
end loop;

— drop objects
for i in (select object_type,object_name from all_objects where object_type not in(‘INDEX’,’LOB’) AND owner=p_sch)loop
v_str :=’drop ‘||i.object_type||’ ‘||p_sch||’.’||i.object_name;
p_do (v_str);
end loop;

end;

grant execute on purge_schema to public;

–login sqlplus
conn anbob/anbob

set serveroutput on;
alter session set recyclebin=off;

exec system.purge_schema;

</pre>

打赏

微信扫一扫，打赏作者吧～