Results 1 to 10 of 10
  1. #1
    Join Date
    Dec 2003
    Posts
    8

    Unhappy Unanswered: Oracle hangs when inserting/deleting from certain tables

    This is the second that it has happened to me in two days. It seems to happen somewhat randomly. I am currently working in a group on a database project and we are using PHP to interface with Oracle. At first I thought that this may be occuring because a couple of the group members were using OCI_DEFAULT parameter in the the ociexecute function call. This would cause PHP to not automatically commit after the statement was executed, and the other members of the group were not manually forcing a commit afterwards, so I thought that this may cause a data dependency issue in some "ghost" sessions that php still had connected.

    I also get this error when I try to drop a table that is locked:
    ORA-00054: resource busy and acquire with NOWAIT specified

    I don't have admin access to the database, so I am not able to kill the sessions (unless there is a way to kill all the sessions that are logged in as your user).

    Any help would be greatly appreciated. Thank you in advance.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Picking a nit...
    Oracle can't/won't/doesn't "hang".

    However under the right (wrong?) conditions it will wait patiently for the 12th of never.

    I suspect that the stuck process is going into enqueue wait.
    A simple query will report who is holding the lock & who is waiting on it.

    As a DBA what I've done for my developers is to write a PL/SQL
    package which has the necessary privs to kill any session. However
    it will only kill a session owned by the same osuser & username who
    is invoking it. This way Johnny can kill his own session and Mary can
    kill her own sessions, but nobody can hurt another user.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2003
    Posts
    8
    Sorry, I guess "hang" isn't the proper terminology.

    Is the query that you speak of something that a non-admin user, such as myself, would be able to perform?

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is the query that you speak of something that a non-admin user, such as myself, would be able to perform?
    I honestly don't know, but it is easily tested.
    Ask me again in about 12 hours when I'll be back in the office.
    I'm at home & my holder-waiter.sql is on my production server.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2003
    Posts
    8
    Ok thanks, I appreciate your help.

    Is there something that may be causing this in PHP that you may know of so that I can avoid this if I can ever get access to my tables again?

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Is there something that may be causing this in PHP that you may know of so that I can avoid this if I can ever get access to my tables again?

    As a total 100% shot in the dark & a pure guesswork, increase INITRANS.
    The default value is 1 and undercertain condition this will prevent the 2nd
    session from starting a transaction against ANY row contain in that block.
    Since sessions are hashed into the INITRANS data structure,
    it is (slightly) better if the number of INITRANS is prime.
    On my OLTP system with 1500 - 1800 logged in sessions,
    all my "hot" tables have INITRANS set to 13. On a small/development
    DB I'd suggest a value of 3, 5 or 7. It does consume space in EACH
    and every datablock in that table/index.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
    id1, id2, lmode, request, type
    FROM V$LOCK
    WHERE (id1, id2, type) IN
    (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
    ORDER BY id1, request
    /

    HTH & YMMV
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Dec 2003
    Posts
    8
    I get a table or view does not exist error referring to v$lock.

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I get a table or view does not exist error referring to v$lock.
    Try specifying SYS.V$LOCK instead.
    If that does not work then the DBA must
    GRANT SELECT ON V_$LOCK TO <YOUR_USERNAME>;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Dec 2003
    Posts
    8
    Still doesn't work.

    Thanks for your help though.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •