oracle怎么删除schema,Script: oracle 删除用户(schema)下的所有对象

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

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;

打赏

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