Re: drop all source

Wynette Richards wrote:

> 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?

I'm not convinced that you even need to go thorugh all of this. As long as you have "create or replace" in all of your source code there really isn't any need to drop the procedures/packages before you do a re-compile unless you are so tight on space in your system tablespace that you have no room for a compile. I think if you just allow the procedures to be replaced you'll end up using less time overall.

-

-

-

-

-

-

-