2014-02-25 11:23:43 (Tue);
いつぞやのWeb拾い物。
ものすごく有用なのでメモさせて頂きました。
コマンドプロンプトより実行
sqlplus -S usr/password@tnsname < drop_all_user_objects.sql
drop_all_user_objects.sql
-- set serveroutput on
declare
procedure drop_all_user_objects_of_type(p_object_type USER_OBJECTS.OBJECT_TYPE%type) as
v_object_type USER_OBJECTS.OBJECT_TYPE%type := upper(p_object_type);
cursor c1 is
select OBJECT_NAME from USER_OBJECTS
where OBJECT_TYPE = v_object_type;
v_row c1%rowtype;
v_sql varchar2(100);
begin
open c1;
loop
fetch c1 into v_row;
exit when c1%notfound;
-- DROP FUNCTION name;
-- DROP PROCEDURE name;
-- DROP PACKAGE name;
-- DROP MATERIALIZED VIEW name;
-- DROP TABLE name CASCADE CONSTRAINTS PURGE;
-- DROP SYNONYM name FORCE;
-- DROP SEQUENCE name;
-- DROP TYPE name FORCE;
v_sql := 'DROP ' || v_object_type || ' ' || v_row.object_name;
case v_object_type
when 'TABLE' then v_sql := v_sql || ' CASCADE CONSTRAINTS PURGE';
when 'SYNONYM' then v_sql := v_sql || ' FORCE';
when 'TYPE' then v_sql := v_sql || ' FORCE';
else null;
end case;
-- dbms_output.put_line(v_sql);
execute immediate v_sql;
end loop;
close c1;
end drop_all_user_objects_of_type;
begin
drop_all_user_objects_of_type('FUNCTION');
drop_all_user_objects_of_type('PROCEDURE');
drop_all_user_objects_of_type('PACKAGE');
drop_all_user_objects_of_type('SYNONYM');
drop_all_user_objects_of_type('SEQUENCE');
drop_all_user_objects_of_type('TYPE');
drop_all_user_objects_of_type('MATERIALIZED VIEW');
drop_all_user_objects_of_type('TABLE');
end;
/
最終更新:2014年02月25日 11:23