Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: Developer locks data base (I think)

    Setup: 9i Forms on 9i vanilla (NOT in the AS environment, using the OC4J dodad to support testing) [If this doesn't make sense would somebody plese inform me. Even though I am stupid I hate to appear so.]

    I have something weird (I think) going on. Almost everytime I have to make a table mod or recreate a sequence I need exit Developer to do get data base operations to complete. I think it only happens after I have run a form. I can run the form from within Developer and directly via browser but it seems to affect me the same way. I do a "select * from v$lock where request > 0" and it comes back with no rows.
    Does any one have any idea what might be going on?
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    I don't know what is going on, but I would start with V$SESSION_WAIT to see what the database operations are waiting for, and try to track it down from there.

  3. #3
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Additional Setup: running on W2K

    v$session_wait had a lot. I understood very little of it. Eventually my process timed out. This is the image after the time out. Below I will give the image after I stop my Developer session. sorry about the data format.

    SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3
    ----- ---------- -------------------- -------------------- ---------- -------- ---------- ---- -----
    P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
    -------- --------- --------------- -------------------
    10 649 null event 1413697536 54435000 1 00000001 0
    00 -1 0 WAITED KNOWN TIME

    1 20803 pmon timer duration 300 0000012C 0 00 0
    00 0 0 WAITING

    2 24091 rdbms ipc message timeout 300 0000012C 0 00 0
    00 0 0 WAITING

    3 1196 rdbms ipc message timeout 300 0000012C 0 00 0
    00 0 0 WAITING

    6 39 rdbms ipc message timeout 180000 0002BF20 0 00 0
    00 0 1177 WAITING

    7 12151 rdbms ipc message timeout 500 000001F4 0 00 0
    00 0 7 WAITING

    4 48445 rdbms ipc message timeout 300 0000012C 0 00 0
    00 0 3 WAITING

    5 594 smon timer sleep time 300 0000012C failed 0 00 0
    00 0 288 WAITING

    9 431 SQL*Net message from driver id 1413697536 54435000 #bytes 1 00000001
    client
    00 0 1603 WAITING

    17 183 SQL*Net message from driver id 1413697536 54435000 #bytes 1 00000001
    client
    00 0 1462 WAITING

    8 4059 wakeup time manager 0 00 0 00 0
    00 0 12 WAITING




    SID SEQ# EVENT P1TEXT P1 P1RAW P2TEXT P2 P2RAW P3TEXT P3
    ----- ---------- -------------------- -------------------- ---------- -------- ---------- ---- -----
    P3RAW WAIT_TIME SECONDS_IN_WAIT STATE
    -------- --------- --------------- -------------------
    17 196 null event 1413697536 54435000 1 00000001 0
    00 -1 0 WAITED KNOWN TIME

    1 20939 pmon timer duration 300 0000012C 0 00 0
    00 0 0 WAITING

    2 24249 rdbms ipc message timeout 300 0000012C 0 00 0
    00 0 4 WAITING

    3 1627 rdbms ipc message timeout 300 0000012C 0 00 0
    00 0 0 WAITING

    6 39 rdbms ipc message timeout 180000 0002BF20 0 00 0
    00 0 1585 WAITING

    7 12231 rdbms ipc message timeout 500 000001F4 0 00 0
    00 0 4 WAITING

    4 48767 rdbms ipc message timeout 300 0000012C 0 00 0
    00 0 0 WAITING

    5 596 smon timer sleep time 300 0000012C failed 0 00 0
    00 0 82 WAITING

    10 652 SQL*Net message from driver id 1413697536 54435000 #bytes 1 00000001
    client
    00 0 408 WAITING

    8 4085 wakeup time manager 0 00 0 00 0
    00 0 8 WAITING


    What I see is one less SQL*Net wait(er). Not being very experience in the DBA realm some direction and/or explanation (references to doc pages etc.) would be appreciated.
    NOTE: Please disregard the label "Senior Member".

  4. #4
    Join Date
    Jan 2004
    Posts
    370
    You'll first need to identify the session id of the waiting session and check v$session_wait while the session is waiting (ie before it times out) - that is the row you are interested in.

    In this case the fields you probably want to check are

    event, p1text, p1, p2text, p2, p3text, p3

    Check the values in p1, p2, p3 as appropriate (not all wait events use all 3 parameters) in the wait events section in the Server Reference Manual - it describes them all.

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Thanks
    NOTE: Please disregard the label "Senior Member".

  6. #6
    Join Date
    Jan 2004
    Location
    North Haven, CT
    Posts
    110
    I did experience developer locked database tables a couple years ago.
    We use Developer 6i on windows and oracle 9i release 1 on linux.
    I looked up metalink and found out that was an oracle bug and i am sure it has been fixed in the new releases of oracle.

  7. #7
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I have 9i forms Version 2. Other then 10g (not released yet) I think there is nothing newer. I might be that I am developing without a full AS implementation. Anyway, following Skywriters lead I joined v$session and v$session_wait. Here is the result:

    SCHEMANAME PROGRAM EVENT P1TEXT P1 P2TEXT
    ------------------------------ --------------- -------------------- -------------------- ----------
    P2 P3TEXT P3 WAIT_TIME SECONDS_IN_WAIT
    ---------- ---------------------------------------------------------------- ---- --------- ---------
    STATE ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK#
    ------------------- ------------- -------------- ---------------
    MDM_DATA ifweb90.exe null event 1413697536
    1 0 -1 9
    WAITED KNOWN TIME -1 0 0

    MDM_DATA ifweb90.exe null event 1413697536
    1 0 -1 9
    WAITED KNOWN TIME -1 0 0

    MDM_DATA sqlplusw.exe null event 1413697536
    1 0 -1 0
    WAITED KNOWN TIME -1 0 0

    MDM_DATA sqlplusw.exe library cache pin handle address 1723490988 pin address
    1728351272 100*mode+namespace 301 0 0
    WAITING -1 0 0

    MDM_DATA ifbld90.exe SQL*Net message from driver id 1413697536 #bytes
    client
    1 0 0 324
    WAITING -1 0 0

    MDM_DATA ifweb90.exe SQL*Net message from driver id 1413697536 #bytes
    client
    1 0 0 9
    WAITING -1 0 0

    MDM_DATA sqlplusw.exe SQL*Net message from driver id 1413697536 #bytes
    client
    1 0 0 58
    WAITING -1 0 0

    The library cache pin is the only guy that goes away after the access attempt timesout. I was thinking that I could just disconnect Developer versus restarting it to get the sql access to go through, I just tried disconnecting and now the Developer process is hanging also.
    NOTE: Please disregard the label "Senior Member".

  8. #8
    Join Date
    Jan 2004
    Posts
    370
    Todd,

    What version of Oracle are you running?

    Check out the Patch set Release Notes for 9.2.0.2 (they are for Solaris but I think it is a generic bug).

    http://oracle-docs.dartmouth.edu/dba....2.htm#2166890

    lynden.zhang is right - there is a bug where Forms doesn't release library cache pins, but it is a server bug, not a Forms one.

    Bug 2166890 Fixed: 9202

    Hangs / Spins
    PL/SQL
    This problem is introduced in 9.0.
    PLSQL clients can cause library cache pins to be held for a long period of time resulting in hang scenarios. This affects Forms / Developer type clients.

  9. #9
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    Re: Developer locks data base (I think)

    Originally posted by Todd Barkus
    I do a "select * from v$lock where request > 0" and it comes back with no rows.
    Maybe developer is making a "select for update" (used in general for pessimistic locking, don't know about your application) or simply an insert/update/delete without committing ?

    If so, the table gets "locked" and you can't perform DDL on it; to diagnose it you must look for rows in v$lock with LMODE > 0 (this gets the locks you have) instead of REQUEST>0 (this gets the locks you want and that you are waiting for).

    Quick example:

    SQL> create table t (x number);

    Table created.

    SQL> insert into t (x) values (1);

    1 row created.

    SQL> select DATA_OBJECT_ID from dba_objects where object_name='T';

    DATA_OBJECT_ID
    --------------
    29927

    SQL> select * from v$lock where type='TM' and id1 = 29927 and lmode > 0;

    ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
    -------- -------- ---------- ------ ---------- ---------- ---------- ---------- ---------- ----------
    6874E058 6874E06C 10 TM 29927 0 3 0 7 0

    SQL> commit;

    Commit complete.

    SQL> select * from v$lock where type='TM' and id1 = 29927 and lmode > 0;

    no rows selected

    If you issue DDL on the table while the lock is held (from another session obviously), you get:

    SQL> alter table t modify (y number);
    alter table t modify (y number)
    *
    ERROR at line 1:
    ORA-00054: resource busy and acquire with NOWAIT specified

    Can't explain the same about sequences, they should not exhibit the same behaviour (they are not 'locked' after getting a value from them).

    PS Please note that v%lock.sid in the query above will get the session that locks the table - so that you can kill it, as it deserves.

    HTH
    Al

  10. #10
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Thanks everyone. You are the greatest.

    Al- I wish my Italian was as good as your English. Have you lived your whole life in Italy?
    NOTE: Please disregard the label "Senior Member".

  11. #11
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130
    Originally posted by Todd Barkus
    Al- I wish my Italian was as good as your English. Have you lived your whole life in Italy?
    Thanks, that's too kind of you - i'm quite sure that i have a lot of room for improvement anyway ;-)

    I've always lived in Italy, yes - but if you are heavily interested in technology, you read a lot in English and so, as a by-product, you improve the language.

    BTW was my suggestion helpful ? I would really like to know.

    bye
    Al

  12. #12
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Haven't had a chance to try your suggestions. I am sure I will before the day is out. I will keep you informed.
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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