作为我们构建过程和不断发展的数据库的一部分,我试图创建一个脚本,将删除用户的所有表和序列。我不想重新创建用户,因为这将需要更多的权限,而不是允许。
我的脚本创建一个过程来删除表/序列,执行过程,然后删除过程。我正在执行的文件从sqlplus:
drop.sql:
create or replace procedure drop_all_cdi_tables
is
cur integer;
begin
cur:= dbms_sql.OPEN_CURSOR();
for t in (select table_name from user_tables) loop
execute immediate 'drop table ' ||t.table_name|| ' cascade constraints';
end loop;
dbms_sql.close_cursor(cur);
cur:= dbms_sql.OPEN_CURSOR();
for t in (select sequence_name from user_sequences) loop
execute immediate 'drop sequence ' ||t.sequence_name;
end loop;
dbms_sql.close_cursor(cur);
end;
/
execute drop_all_cdi_tables;
/
drop procedure drop_all_cdi_tables;
/
不幸的是,删除过程会导致问题。似乎引起竞争条件,并且在执行之前该过程被丢弃。
例如。:
SQL*Plus: Release 11.1.0.7.0 - Production on Tue Mar 30 18:45:42 2010
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Procedure created.
PL/SQL procedure successfully completed.
Procedure created.
Procedure dropped.
drop procedure drop_all_user_tables
*
ERROR at line 1:
ORA-04043: object DROP_ALL_USER_TABLES does not exist
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64
With the Partitioning, OLAP, Data Mining and Real Application Testing options
任何想法如何得到这个工作?