Oracle-L: RE: What is the result of this

From: Stephen Lee <Stephen.Lee_at_DTAG.Com>
Date: Wed, 16 Apr 2003 07:43:39 -0800
Message-ID: <F001.00582960.20030416074339@fatcity.com>

Well, here is what was tested.

create or replace procedure go as
begin

execute immediate 'drop index AUTH_CSTATUS_CTYPE_NCCID_IDX'; exception

when others then

      if sqlcode = -54 then
         go;
      else
         dbms_output.put_line(SQLERRM||' '||SQLCODE);
      end if;

end;
/

Session #1 updates the table but doesn't commit. Session #2 execs the procedure. Checking memory usage, session #2 does have a steady increase -- maybe 2 or 3 Mb per minute. This idea came out of the quest to modify things in a busy database without taking the database down because of the "resource busy ..." error. For what it's worth, Oracle has made some improvements in 9.x for this. We've been able to make updates to packages and procedures now with the database and application active that we previously could not. And adding a column to a busy table seems to go OK now with things staying up and active. But dropping an index still appears to require a slice of time where the drop activity needs to have a clear shot at the table.

Given the growth of memory usage -- not particularly huge, but significant
-- of the above procedure, one might have reservations about trying it. In
the past I have tried dealing with the "resource busy" error with stuff like:

create a text file with
vi
enter a line like
drop this or that object;
yy
p
2yy
p
4yy
p
...
...
64yy
p
p
p
p
p
etc.
etc.

then create a shell script with a loop where you check for the existence of the object, and if it exists "@" the above text file. This requires a little time to parse every line, so I would think the probability of having the table all to yourself for a quickie is reduced. I thought maybe with a stored procedure that just whizzes 'round and 'round at top speed (the CPU usage for this is about 99%), one might have a little better chance of snagging the table (assuming that there is a time, however small, that the table is lock free).

Feel free to try the procedure in *YOUR* ultra-busy production database, and let me know how it works!

Hey Moe! It woiks! Nyuk Nyuk Nyuk.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Stephen Lee
INET: Stephen.Lee_at_DTAG.Com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Wed Apr 16 2003 - 10:43:39 CDT