Dropping all user tables/sequences in Oracle

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:

开发者_JS百科
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?

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;

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.

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;

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.

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;
/

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