注意:此文档是本人写的
create or replace procedure proc_drop_all_tables
as
cursor view_cur is select view_name from user_views ;
cursor tab_cur is select table_name from user_tables;
v_sql varchar2(200);
cursor seq_cur is select sequence_name,min_value,
increment_by from user_sequences;
begin
for l_view_cur in view_cur loop
v_sql := 'drop view ' || l_view_cur.view_name ;
execute immediate v_sql;
end loop;
for l_tab_cur in tab_cur loop
v_sql := 'drop table ' || l_tab_cur.table_name || ' cascade constraint';
execute immediate v_sql;
--dbms_output.put_line(v_sql);
end loop;
for l_seq_cur in seq_cur loop
v_sql := 'drop sequence ' || l_seq_cur.sequence_name;
--dbms_output.put_line(v_sql);
execute immediate v_sql;
v_sql := 'create sequence ' || l_seq_cur.sequence_name || ' start with ' ||
l_seq_cur.min_value || ' increment by ' || l_seq_cur.increment_by ||
' nomaxvalue cache 10 ';
--dbms_output.put_line(v_sql);
execute immediate v_sql;
end loop;
execute immediate 'purge recyclebin';
end;
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/50509/viewspace-174254/ ，如需转载，请注明出处，否则将追究法律责任。
