Re: drop all source

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Thu, 09 Sep 1999 19:47:54 +0800
Message-ID: <37D79E6A.2BC5@yahoo.com>

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?
>
> Thanks,
> Wynette Richards
> richards_at_cs.unm.edu

You can use 'create or replace' but I'm curious as to why you would want to rebuild from scratch....It would suggest that you have more confidence in your flat files than whats in the database ...?

If that is the case, then have a security problem - you could easily resolve this by having some sort of mechanism like:

1. create a user XXX
2. grant it DBA (don't panic)
3. create all your procs in here
4. grant execute on them to whoever needs them
5. revoke DBA

Now you have an account that no one can log into, and thus no one can alter the procs (unless you have given someone 'create any proc')...

HTH
--

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Thu Sep 09 1999 - 06:47:54 CDT