drop all source
From: Wynette Richards <richards_at_cs.unm.edu>
Date: Thu, 09 Sep 1999 10:47:30 -0600
Message-ID: <37D7E4A2.E9414630_at_cs.unm.edu>
[Quoted] [Quoted] We periodically drop all our source code and rebuild it from scratch (via make files). We use the following procedure to drop all source:
Date: Thu, 09 Sep 1999 10:47:30 -0600
Message-ID: <37D7E4A2.E9414630_at_cs.unm.edu>
[Quoted] [Quoted] 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 - 18:47:30 CEST