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.

Answer 3

Just run these two statements and then run all the results:

select 'drop table ' || table_name || ';' from user_tables;
select 'drop sequence ' || sequence_name || ';' from user_sequences;

Answer 4

It looks like your example error message is getting an error on drop_all_user_tables but the example you gave is for drop_all_cdi_tables. Does the drop_all_user_tables code look different?

Also you have calls to dbms_sql but don't seem to be using it do any parsing.

Answer 5

In addition to the solution presented by OMG Ponies, if you have sequences with blank spaces, you need to enhance the PLSQL a bit:

BEGIN
  FOR i IN (SELECT sequence_name FROM user_sequences)
    Loop
      EXECUTE IMMEDIATE('"DROP SEQUENCE ' || user || '"."' || i.sequence_name || '"');
    End Loop;
End;
/

Answer 6

For some reason OMG Ponies solution gave an error "SQL command not properly ended" on PLSQL. In case someone else comes across the same problem, here is how I was able to delete all the tables in the current schema.

DECLARE
  table_name VARCHAR2(30);
  CURSOR usertables IS SELECT * FROM user_tables WHERE table_name NOT LIKE 'BIN$%';
BEGIN
  FOR i IN usertables
  LOOP
  EXECUTE IMMEDIATE 'drop table ' || i.table_name || ' cascade constraints';
  END LOOP;
END;
/

Credits: Snippler