PLSQL - Drop all database objects of a user

Question

I'm trying to use a procedure (no parameters) to drop all of the user-created database objects located within the schema from where the procedure is launched, but I'm really not sure on how to go about this. Here's what I have so far, but I think I'm going about this the wrong way.

create or replace procedure CLEAN_SCHEMA is
cursor schema_cur is
select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
from user_objects;
schema_rec schema_cur%rowtype;
begin
select 'drop '||object_type||' '|| object_name||  DECODE(OBJECT_TYPE,'TABLE',' CASCADE CONSTRAINTS;',';')
into schema_rec
from user_objects;
end;
/

Answer 1

Unless the user has hard to reapply permissions, its probably easier to just drop the user and recreate them.

Answer 2

What you've got is a good start.

Here is the rest:

• You have a cursor AND a select statement. You only need the cursor.
• Your next step is to call the drop statement using dynamic PLSQL. I'd use the EXECUTE IMMEDIATE statement. Its more elegant and preformance friendly to just select the name of the thing you're dropping and submit it as a bind variable to EXECUTE IMMEDIATE.
• In order to drop the objects of the schema calling the method and not the schema owning the method you have to use "AUTHID CURRENT_USER". See the Oracle documentation for more info.
• Other things to drop: packages, functions, procedures (the system will likely hang then timeout if you try to drop this method while its running), Java classes, triggers, views, types

Lastly, this is obviously a very dangerous method so you may want to consider putting it in a script instead of a stored procedure so it isn't left in the database for anyone to run.

Answer 3

You're close - as someone else has noted you need an "EXECUTE IMMEDIATE" for the statement. You should consider:

• Instead of creating a procedure to do this, run this as an anonymous PL/SQL block so you don't have the issue of trying to drop a procedure that is running.

• Add a test for object type of TABLE and for that case modify the drop statement to include the cascade option to handle tables that are "parents" of other tables via foreign key constraints. Remember that you'll probably be generating the cursor list in an order that doesn't consider dependencies that will block the drop.

• Also on the subject of dependencies, it is probably best to drop tables first (add a DECODE in your cursor that assigns a lower numeric value to this object type and order the cursor select by this value). If you have Oracle objects of type TYPE that are used as column types in a table definition the table must be dropped first.

• If you use Oracle Advanced Queuing the objects related to this MUST be dropped with the AQ package API calls. Although you can drop the Oracle-generated tables for queue support with a regular DROP TABLE, you will find yourself in the catch-22 position of then not being able to drop the related queues nor add them back. Up to version 10g at least you couldn't even drop the containing schema without putting the database in a special mode when this situation existed

Answer 4

declare
  cursor ix is
    select *
      from user_objects
     where object_type in ('TABLE', 'VIEW', 'FUNCTION', 'SEQUENCE');
begin
 for x in ix loop
   execute immediate('drop '||x.object_type||' '||x.object_name);
 end loop;
end;

Answer 5

create or replace
FUNCTION                DROP_ALL_SCHEMA_OBJECTS RETURN NUMBER AS
PRAGMA AUTONOMOUS_TRANSACTION;
cursor c_get_objects is
  select object_type,'"'||object_name||'"'||decode(object_type,'TABLE' ,' cascade constraints',null) obj_name
  from user_objects
  where object_type in ('TABLE','VIEW','PACKAGE','SEQUENCE','SYNONYM', 'MATERIALIZED VIEW')
  order by object_type;
cursor c_get_objects_type is
  select object_type, '"'||object_name||'"' obj_name
  from user_objects
  where object_type in ('TYPE');
BEGIN
  begin
    for object_rec in c_get_objects loop
      execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
    end loop;
    for object_rec in c_get_objects_type loop
      begin
        execute immediate ('drop '||object_rec.object_type||' ' ||object_rec.obj_name);
      end;
    end loop;
  end;
  RETURN 0;
END DROP_ALL_SCHEMA_OBJECTS;

Create the above function (autonomous so DDL can be called via a function) then you can just:

select DROP_ALL_SCHEMA_OBJECTS from dual;

when you want to drop all your objects, make sure you dont try to drop the proc your running (i dont care about the procs thats why i dont have procs or functions in the object_type list)

if you want to drop everything you need an anonymous block

but i needed to be able to do this from a tool that only allowed ansi sql (not plsql) hence a stored proc.

Enjoy.