Dropping all user tables/sequences in Oracle

Question

As part of our build process and evolving database, I'm trying to create a script which will remove all of the tables and sequences for a user. I don't want to do recreate the user as this will require more permissions than allowed.

My script creates a procedure to drop the tables/sequences, executes the procedure, and then drops the procedure. I'm executing the file from 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; / 

Unfortunately, dropping the procedure causes a problem. There seems to cause a race condition and the procedure is dropped before it executes.
E.g.:

  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 

Any ideas on how to get this working?

Answer 1

If you're not intending on keeping the stored procedure, I'd use an anonymous PLSQL block:

BEGIN    --Bye Sequences!   FOR i IN (SELECT us.sequence_name               FROM USER_SEQUENCES us) LOOP     EXECUTE IMMEDIATE 'drop sequence '|| i.sequence_name ||'';   END LOOP;    --Bye Tables!   FOR i IN (SELECT ut.table_name               FROM USER_TABLES ut) LOOP     EXECUTE IMMEDIATE 'drop table '|| i.table_name ||' CASCADE CONSTRAINTS ';   END LOOP;  END; 

Answer 2

For an SQL statement, the semi-colon at the end will execute the statement. The / will execute the previous statement. As such, you end lines of

drop procedure drop_all_cdi_tables; / 

will drop the procedure, then try to drop it again.

If you look at your output, you'll see 'PROCEDURE CREATED', then executed, then 'PROCEDURE CREATED' again as it re-executes the last statement (EXECUTE is a SQL*Plus command, not a statement so isn't buffered) then "PROCEDURE DROPPED" and then it tries (and fails) to drop it the second time.

PS. I agree with Dougman on the odd DBMS_SQL calls.