drop all source

We periodically drop all our source code and rebuild it from scratch (via make files). We use the following procedure to drop all source:

   create or replace procedure drop_all_source    is

      cur integer;
      rows integer;
      drop_command varchar2(300);

      cursor source_drop_cur is
         select distinct name, type
         from            user_source
         where  type != 'PACKAGE BODY' and 
                name != 'DROP_ALL_SOURCE';

   begin
      dbms_output.enable(1000000);
      cur := dbms_sql.open_cursor;

      for record in source_drop_cur loop
         drop_command := 'drop ' || record.type || ' ' || record.name;
         dbms_sql.parse( cur, drop_command, dbms_sql.native );
         rows := dbms_sql.execute( cur );
         dbms_output.put_line( drop_command );
      end loop;

      dbms_sql.close_cursor(cur);

   end;

(We exclude package bodies in the select because it is sufficient to drop the package header -- the body will follow.)

This works ok. But it takes a long time to run (15 or 20 minutes) since we have a lot of source code. (It takes about as long to drop the source code as to rebuild it from scratch.) I'm sure it is slow becauseeach time a package is dropped Oracle has to update its dependency tree for that package.

Is there an easier/quicker way to drop *all* the source code for a user?

Thanks,
Wynette Richards
richards_at_cs.unm.edu Received on Thu Sep 09 1999 - 11:47:30 CDT