How to resolve "Resouce Busy" error generated by Coldfunsion function

Essentially a table is locked while it is being queried again.
Read these two links:

http://stackoverflow.com/questions/3075738/ora-00054-resource-busy-and-acquire-with-nowait-specified

http://stackoverflow.com/questions/4842765/ora-00054-resource-busy-and-acquire-with-nowait-specified-or-timeout-expired

dgrafx:

    Many thanks for your response - much appreciated.

   I had seen both of the links you cited above, but was not quite sure how, or if they could be implemented in this application.

     For example, I understand the role of the "LOCK TABLE 'TABLE_NAME' NO WAIT" function, and have several questions:

    1.  Is it performed (or modified) on the Oracle server, or within the application?

    2.  How does one issue your own session lock prior to activation of the above function, and will it work for all users of the application?

    3.  Finally,  does it just simply remove the "Resource Busy" error, or can multiple users process their queries simultaneously against the locked table?

    Regards,

1. It is locked automatically
http://docs.oracle.com/cd/E11882_01/server.112/e41084/ap_locks001.htm#SQLRF55502

To better help you solve your issue I would need to understand more about your application.

Read this: https://community.oracle.com/thread/634676?tstart=0

I can tell you that a session lock in CF doesn't affect the DB.

dgrafx:

    Sorry for the delay!

    Your information certainly defines the issue, as well as the solution.  Also, it's helpful to hear that the locks in CF are not related to those in the DB.  But, I'm still not clear on how, or where the fix should be implemented.

   In terms of the application, the attached file (getSolicitCount.cfm), contains the two functions that actually generates the files (getSolicitCount, and getExcludedSolicitCount), Lines 15 - 32.  They are called in the CFC file I uploaded previously.

  The two files are then  'zipped" (Lines 38 - 41), and exported for access by the user.  My understanding is that this may be a possible cause of the error.  Each makes a call to the DB simultaneously.  

   This sure seems like the solution, though.

    Let me know if you need more information, and/or clarification of the application.

    Thank you!

dgrafx:

   Not having access to the Oracle Enterprise tools has been a challenge for me, as well as convincing the DBAs that the problem is database related, as opposed to application related.

     Is there s good third party set of tools (e.g. open source) that  I can use to gather the statistics I need to either confirm (or rule out) the database as the issue (although I'm convinced the problem is there).

    Thanks!

dgrafx:

   By the way, below is the stored procedure you mentioned (ADDIDLISTS).  

   The error is generated on Line 6.

------------------------- BEGIN STORED PROCEDURE ---------------------------------------

    "create or replace procedure       AddIdLists (id_no in clob) is
  id_insert_val varchar2(8000);
  id_pos int := 1;
  id_pos1 int := 1;
begin
   EXECUTE IMMEDIATE ' truncate table tempIdlists';
   
  if dbms_lob.instr(id_no, ',', id_pos) = 0 then
    execute immediate ' insert into tempIdlists(vals) values(:v)' using to_char(id_pos);
  else
    while id_pos < dbms_lob.getlength(id_no) loop
      id_pos1 := dbms_lob.instr(id_no, ',', id_pos + 1);
      if id_pos1 = 0 then -- last value in list
        id_pos1 := dbms_lob.getlength(id_no) + 1;
      end if;
      id_insert_val := dbms_lob.substr(id_no, id_pos1 - id_pos, id_pos);
      id_pos := id_pos1 + 1;
      execute immediate ' insert into tempIdlists(vals) values(:v)' using id_insert_val;
    end loop;
    commit;
  end if;

end AddIdLists;

  -----------------------  END STORED PROCEDURE ---------------------------------------

Here is a general link: https://www.google.com/#newwindow=1&q=open+source+oracle+monitoring+tools

I'm not an Oracle guy myself.

Try deleting all rows instead of truncating the table
DELETE FROM tempIdlists
Are you trying to rollback under certain circumstances?
Because you cannot rollback truncate but can rollback delete

dgrafx:

    Nope - no rollbacks as far as this particular feature.

    Will try the DELETE function, as well as one of those tools on the Oracle side!

    Thanks!

dgrafx:

    A quick update!

   Tried the delete option, and the errors increased significantly, so set it back to truncate.

   Evaluating a couple of monitoring tools which provide great information, but not sure what I'm looking for in terms of identifying  table" locks", and the cause.  

    Let me know if there's a specific feature and/or function I can focus on to determine what may be causing the "Resource Busy" error.

   Thanks!

OK I don't know so try this

begin
   EXECUTE IMMEDIATE ' truncate table tempIdlists';
end

begin
  if dbms_lob.instr(id_no, ',', id_pos) = 0 then
    execute immediate ' insert into tempIdlists(vals) values(:v)' using to_char(id_pos);
  else
    while id_pos < dbms_lob.getlength(id_no) loop
      id_pos1 := dbms_lob.instr(id_no, ',', id_pos + 1);
      if id_pos1 = 0 then -- last value in list
        id_pos1 := dbms_lob.getlength(id_no) + 1;
      end if;
      id_insert_val := dbms_lob.substr(id_no, id_pos1 - id_pos, id_pos);
      id_pos := id_pos1 + 1;
      execute immediate ' insert into tempIdlists(vals) values(:v)' using id_insert_val;
    end loop;
    commit;
  end if;

dgrafx:

    I'm running your version of the ADDIDLISTS stored procedure, and  haven't detected any additional issues with my system, thus far.

   However, several other applications use thisdatabase, and since last Thursday, the server had to be re-booted three time due to dozens of runaway (or orphaned) sessions.  The result is a '503' error, and no one can access the database until it's re-booted.

    Needless to say, the DBA has been catapulted into the limelight,  but still maintains that the applications are the issue.  The problem with that argument is that this application (and others) have been running fine for years, and haven't changed in the past year.

   At any rate, I'm gather what appears to be significant information from the r serverusing one of the programs from your list.  I'll upload a file with the results, and and perhaps you can spot something out of place.

   Thanks!

------------------------------------------------------------------------

agx:

  Thanks for your response - much appreciated.

  It is indeed a tad frustrating trying to make your case to a DBA who simply makes assumptions without any analysis of the database.  As a minimum, I would think that he'd take the steps to rule out the database as the issue.  Nothing so far.

   Answers to your questions follow:

   1.  I attempted to check the tables, and get an "access denied".  The DBA just contends that the DB is not the issue.  I haven't seen anything (e.g. reports, statistics) to support that theory.  Perhaps tomorrow.

  2.  Yes - the table "tempIdlists" is both temporary, and global.

    Thanks for your input.

EDIT: Added question

The DBA just contends that the DB is not the issue.

Without saying whether or not they investigated the system tables to discover the chain of events and possible causes of the blocking?

Yes - the table "tempIdlists" is both temporary, and global.

Not to beat a dead horse, but are you 100% positive (and does it PRESERVE ROWS or DELETE ROWS on commit) ?  I thought GTT data was essentially specific to each session, so to have blocking problems sounds .. odd.  I could see it happening with a regular table though.  

Out of curiosity,  what does the "execute immediate" buy you with the insert statements? I don't know that it makes any difference, I just thought those types of statements could be done without exec, even in a proc.

AND if it was working fine for a year ...

WHAT changed with the DB if anything? Service packs? anything?

It sounds like you'll need to bring your case to someone higher up that can force the DB to run the proper tests to determine the cause.

agx:

 As far as I can tell, the rows appear to be DELETED upon issuing a "COMMIT" .    The table needs to  clear existing data prior to the next user's  request.  I'm certain about the table being global (used by other functionality throughout the application), however, "temporary" may be debatable.  

   The table (tempidlists) resides "permanently" in the DB, as opposed to being created dynamically.  I don't see anywhere in the code where it is dropped and/or re-created, so you may be on to something.

   My understanding is that the "execute immediate" command is more suited for not only building very complex dynamic SQL, but also executing it right away. Users have numerous options to choose from, and output is often huge.  But to your point, it could likely be performed without the use of the "execute immediate" statement.  Are you thinking that using this statement is a potential issue?

dgrafx:

  I'm sure it's one, or more of the above (changes on DB side).  

   We are not recipients of the information, and pretty much anytime one of our programs (especially this one)  becomes unstable, the application is always the culprit.  Absolutely nothing was changed on the application..  It would also be helpful if the error were more consistent.  Until today, the error hadn't occurred since August 25th.

    Those affected mostly are outside of our building, but the voices are getting louder (irate users).  

    By the way, the program I was running to gather statistics on the database (MyOra) has not been very effective, at this point, primarily because the error is not consistent, and other issues with the dDB.

    Thank you both very much - appreciate your thoughts, and the dialog.

    Regards

another experiment

this article speaks of privledges and a tad different syntax
http://stackoverflow.com/questions/21995951/truncate-and-insert-within-procedure-dont-work-together

don't see anywhere in the code where it is dropped and/or re-created

Well that would apply to GTT's too. They're only created once.  Coming from SQL Server I remember finding it odd too, but it's the way Oracle does it.

As far as I can tell, the rows appear to be DELETED upon issuing a "COMMIT"

Hm... if it is truly is a GTT, seems like it would be set to PRESERVE. Otherwise, what's the point of the DELETE/TRUNCATE? Because (I believe) the records would automatically disappear at the end of the transaction and/or commit's in AddIdLists.  

Are you thinking that using this statement is a potential issue

Not sure.  I got the impression it was designed more for dynamic sql, or situations were you couldn't represent certain statements in pl/sql. Since that's not the case in your proc, I was curious if there was a specific reason it was used here - and if removing it made any difference.

dgrafx, and agx:

     I'm very sorry for the delay.  Had a class all of last week (Informatica) which dominated much of my focus - but now an excuse!

    Just wanted to make a comment before closing this out.

    I'll follow up on your suggestion of moving the question to the Oracle folks, but know that your comments, and analysis have been very helpful in exploring considerations, as well as things I can rule out.

     Thanks again - much appreciated!

NP ...
Thanks for the points!
I hope you find a solution.