Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> 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