Xref: alice comp.databases.oracle.misc:40656 comp.databases.oracle.server:65143 comp.databases.oracle.tools:31651
Path: alice!news-feed.fnsi.net!enews.sgi.com!newsfeed.berkeley.edu!howland.erols.net!news-out.digex.net.MISMATCH!dca1-hub1.news.digex.net!intermedia!lynx.unm.edu!not-for-mail
From: Wynette Richards <richards@cs.unm.edu>
Newsgroups: comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools
Subject: drop all source
Date: Thu, 09 Sep 1999 10:47:30 -0600
Organization: University of New Mexico, Albuquerque
Lines: 46
Message-ID: <37D7E4A2.E9414630@cs.unm.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Mailer: Mozilla 4.51 [en] (WinNT; I)
X-Accept-Language: en

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@cs.unm.edu