java怎么删除数据库用户_PLSQL - 删除用户的所有数据库对象

谢谢 Martin Brambley 和 Vijayan Srinivasan ！

但是Vijayan Srinivasan的版本不正确，因为“TYPE”类型的依赖对象有时会在丢弃它们时产生错误：

ORA-02303：不能删除或替换类型或表依赖的类型

我的版本从Schema中删除所有对象以及其他：

drop程序和功能(期待'DROP_ALL_SCHEMA_OBJECTS')

删除所有作业和dbms_jobs

删除所有db_links

不删除嵌套表，因为不支持嵌套表的DROPing

CREATE OR REPLACE

procedure DROP_ALL_SCHEMA_OBJECTS AS

PRAGMA AUTONOMOUS_TRANSACTION;

cursor c_get_objects is

select uo.object_type object_type_2,'"'||uo.object_name||'"'||decode(uo.object_type,'TABLE' ,' cascade constraints',null) obj_name2

FROM USER_OBJECTS uo

where uo.object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW', 'FUNCTION', 'PROCEDURE')

and not (uo.object_type = 'TABLE' and exists (select 1 from user_nested_tables unt where uo.object_name = unt.table_name))

and not (uo.object_type = 'PROCEDURE' and uo.object_name = 'DROP_ALL_SCHEMA_OBJECTS')

order by uo.object_type;

cursor c_get_objects_type is

select object_type, '"'||object_name||'"' obj_name

from user_objects

where object_type in ('TYPE');

cursor c_get_dblinks is

select '"'||db_link||'"' obj_name

from user_db_links;

cursor c_get_jobs is

select '"'||object_name||'"' obj_name

from user_objects

where object_type = 'JOB';

cursor c_get_dbms_jobs is

select job obj_number_id

from user_jobs

where schema_user != 'SYSMAN';

BEGIN

begin

for object_rec in c_get_objects loop

execute immediate ('drop '||object_rec.object_type_2||' ' ||object_rec.obj_name2);

end loop;

for object_rec in c_get_objects_type loop

begin

execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);

end;

end loop;

for object_rec in c_get_dblinks loop

execute immediate ('drop database link '||object_rec.obj_name);

end loop;

for object_rec in c_get_jobs loop

DBMS_SCHEDULER.DROP_JOB(job_name => object_rec.obj_name);

end loop;

commit;

for object_rec in c_get_dbms_jobs loop

dbms_job.remove(object_rec.obj_number_id);

end loop;

commit;

end;

END DROP_ALL_SCHEMA_OBJECTS;

/

execute DROP_ALL_SCHEMA_OBJECTS;

drop procedure DROP_ALL_SCHEMA_OBJECTS;

exit;