Oracle删除当前用户的所有对象(表、视图、触发器、存储过程、函数)
Oracle删除当前用户的所有对象(表、视图、触发器、存储过程、函数)

1.

DECLARETYPE name_list IS TABLE OF VARCHAR2(40);TYPE type_list IS TABLE OF VARCHAR2(20);Tab_name name_list:=name_list();Tab_type type_list:=type_list();sql_str VARCHAR2(500);BEGINsql_str := 'select uo.object_name,uo.object_type from user_objects uo where uo.object_type not in(''INDEX'',''LOB'') order by uo.object_type desc';EXECUTE IMMEDIATE sql_str BULK COLLECT INTO tab_name,tab_type;FOR i IN Tab_name.FIRST.. Tab_name.LAST LOOP--if Tab_type(i)='TABLE' then  删除条件 truncate快速删除记录sql_str := 'DROP ' || Tab_type(i) || ' ' || Tab_name(i);end if;EXECUTE IMMEDIATE sql_str;END LOOP;END;

2.
cascade 参数表示级联删除这个用户的所有对象

drop   user   username   cascade

CREATE OR REPLACE PROCEDURE AutoBackupOracle4Day_bak_bakasTYPE name_list IS TABLE OF VARCHAR2(40);TYPE type_list IS TABLE OF VARCHAR2(20);Tab_name name_list:=name_list();Tab_type type_list:=type_list();sql_str VARCHAR2(500);sql_a VARCHAR2(500);sql_b VARCHAR2(500);BEGIN--获取用户下的所有对象  包括表，视图，存储过程 等等--sql_str := 'select uo.object_name,uo.object_type from user_objects@GZEPBMOTOR43.US.ORACLE.COM uo where uo.object_type not in(''INDEX'',''LOB'') order by uo.object_type desc';--获取用户下的表sql_str :='select a.table_name,a.tablespace_name from user_tables@GZEPBMOTOR43.US.ORACLE.COM a';EXECUTE IMMEDIATE sql_str BULK COLLECT INTO tab_name,tab_type;FOR i IN Tab_name.FIRST.. Tab_name.LAST LOOP--if Tab_name(i)='T_BDM_ENVIROMENTSTANDARD' thensql_a := 'truncate ' || Tab_type(i) || ' ' || Tab_name(i);sql_b:= 'insert into  ' || Tab_name(i) || '  select * from  "' ||Tab_name(i)||'"@GZEPBMOTOR43.US.ORACLE.COM';--dbms_output.put_line( sql_str);--dbms_output.put_line( sql_a);--设置打印的最大限值dbms_output.enable(50000);dbms_output.put_line('EXECUTE IMMEDIATE ''truncate table ' ||Tab_name(i)||''';');dbms_output.put_line('insert into  ' || Tab_name(i) || '  select * from  ' ||Tab_name(i)||'@GZEPBMOTOR43.US.ORACLE.COM;');--declare--bad_credit exception;--begin--删除数据--EXECUTE IMMEDIATE 'truncate table'|| ' ' || Tab_name(i);--执行加入--EXECUTE IMMEDIATE 'insert into  ' || Tab_name(i) || '  select * from  ' ||Tab_name(i)||'@GZEPBMOTOR43.US.ORACLE.COM';--raise bad_credit;--end if;--exception --  when bad_credit then   --end;--EXECUTE IMMEDIATE sql_b;END LOOP;commit;END;

我的异常网推荐解决方案：oracle存储过程,http://www.myexception.cn/oracle-develop/177537.html