Oracle PL/SQL: Dropping All Objects In An Oracle Schema

This blog post provides a simple piece of PL/SQL which will delete every object owned by the currently logged in user.

Here is the PL/SQL block;


declare
  v_ItemCount integer;
begin
  SELECT count(*)
    INTO v_ItemCount
    FROM ALL_OBJECTS AO
   WHERE AO.OWNER = USER
     AND AO.OBJECT_TYPE NOT IN ('INDEX')
     AND AO.OBJECT_NAME NOT LIKE 'BIN$%';
  while (v_ItemCount > 0) loop
    for v_Cmd in (SELECT 'drop ' || AO.OBJECT_TYPE || ' ' || AO.OBJECT_NAME ||
                         DECODE(AO.OBJECT_TYPE,
                                'TABLE',
                                ' CASCADE CONSTRAINTS',
                                '') as DROPCMD
                    FROM ALL_OBJECTS AO
                   WHERE AO.OWNER = USER
                     AND AO.OBJECT_TYPE NOT IN ('INDEX')
                     AND AO.OBJECT_NAME NOT LIKE 'BIN$%') loop
      begin
        execute immediate v_Cmd.dropcmd;
      exception
        when others then
          null; -- ignore errors
      end;
    end loop;
    SELECT count(*)
      INTO v_ItemCount
      FROM ALL_OBJECTS AO
     WHERE AO.OWNER = USER
       AND AO.OBJECT_TYPE NOT IN ('INDEX')
       AND AO.OBJECT_NAME NOT LIKE 'BIN$%';
  end loop;


  execute immediate 'purge recyclebin';
end;

The SQL was written against Oracle 10g (hence the "purge recyclebin" at the bottom and the exclusion of objects already in the recycle bin from the "drop" loop). You can monitor progress (and check to see if the code has stalled) by running the count SQL in another editor attached to the same schema.

Clearly a piece of SQL you should be very careful with!