Home » RDBMS Server » Performance Tuning » Locking modes (10g)
Locking modes [message #643951] Fri, 23 October 2015 04:26 Go to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
I have a serious problem of locking , i have created a form based on two or more table, while multiple users are inserting the records at the same time , sessions are getting blocked.Please advice what information is required to solve this problem.What information is required on this post to resolve this issue.

Re: Locking modes [message #643952 is a reply to message #643951] Fri, 23 October 2015 04:52 Go to previous messageGo to next message
Littlefoot
Messages: 21805
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you saying that I'm inserting my record, you are inserting yours, and we (b)lock each other? That's not how Oracle works, so - there must be something else. Could you explain it once again, but this time providing much more details who is doing what?
Re: Locking modes [message #643953 is a reply to message #643952] Fri, 23 October 2015 04:57 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
The obvious starting point is to see what SQL the blocked sessions are running (check v$session and v$sql). That'll show what object has locks on it.
Re: Locking modes [message #643955 is a reply to message #643953] Fri, 23 October 2015 09:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68618
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

... and in addition, v$lock to get more information about those locks.

Re: Locking modes [message #643964 is a reply to message #643955] Fri, 23 October 2015 21:15 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks littlefoot,cookiemonster and michael, the information available under v$lock is as follows, actually there is one transaction screen, used by multiple users based on combination of two fields like date and unit , if one user x is updating data and user y is either querying the same combination , this locks are occuring. i suspected the forms earlier but there is nothing in it.


ADDR,KADDR,SID,TYPE,ID1,ID2,LMODE,REQUEST,CTIME,BLOCK
000007FFB11F86A0,000007FFB11F86C0,272,XR,4,0,1,0,6668653,0
000007FFB11F8738,000007FFB11F8758,272,CF,0,0,2,0,6668650,0
000007FFB11F8868,000007FFB11F8888,272,RS,25,1,2,0,6668647,0
000007FFB11F8998,000007FFB11F89B8,273,RT,1,0,6,0,6668647,0
000007FFB11F8AC8,000007FFB11F8AE8,271,TS,3,1,3,0,6668637,0
000007FFB11F8BF8,000007FFB11F8C18,275,MR,1,0,4,0,6668641,0
000007FFB11F8C90,000007FFB11F8CB0,275,MR,2,0,4,0,6668641,0
000007FFB11F8D28,000007FFB11F8D48,275,MR,3,0,4,0,6668641,0
000007FFB11F8DC0,000007FFB11F8DE0,275,MR,4,0,4,0,6668641,0
000007FFB11F8E58,000007FFB11F8E78,275,MR,5,0,4,0,6668641,0
000007FFB11F8EF0,000007FFB11F8F10,275,MR,6,0,4,0,6668641,0
000007FFB11F8F88,000007FFB11F8FA8,275,MR,7,0,4,0,6668641,0
000007FFB11F9038,000007FFB11F9058,275,MR,8,0,4,0,6668641,0
000007FFB11F90D0,000007FFB11F90F0,275,MR,9,0,4,0,6668641,0
000007FFB11F9168,000007FFB11F9188,275,MR,10,0,4,0,6668641,0
000007FFB11F9200,000007FFB11F9220,275,MR,11,0,4,0,6668641,0
000007FFB11F9298,000007FFB11F92B8,275,MR,12,0,4,0,6668641,0
000007FFB11F9330,000007FFB11F9350,275,MR,13,0,4,0,6668641,0
000007FFB11F93C8,000007FFB11F93E8,275,MR,14,0,4,0,6668641,0
000007FFB11F9460,000007FFB11F9480,275,MR,15,0,4,0,6668641,0
000007FFB11F94F8,000007FFB11F9518,275,MR,16,0,4,0,6668641,0
000007FFB11F9590,000007FFB11F95B0,275,MR,17,0,4,0,6668641,0
000007FFB11F9628,000007FFB11F9648,275,MR,18,0,4,0,6668641,0
000007FFB11F96C0,000007FFB11F96E0,275,MR,19,0,4,0,6668641,0
000007FFB11F9758,000007FFB11F9778,275,MR,20,0,4,0,6668641,0
000007FFB11F97F0,000007FFB11F9810,275,MR,21,0,4,0,6668641,0
000007FFB11F9888,000007FFB11F98A8,275,MR,22,0,4,0,6668641,0
000007FFB11F9920,000007FFB11F9940,275,MR,23,0,4,0,6668641,0
000007FFB11F99D0,000007FFB11F99F0,275,MR,24,0,4,0,6668641,0
000007FFB11F9A68,000007FFB11F9A88,275,MR,25,0,4,0,6668641,0
000007FFB11F9B00,000007FFB11F9B20,275,MR,26,0,4,0,6668641,0
000007FFB11F9B98,000007FFB11F9BB8,275,MR,27,0,4,0,6668641,0
000007FFB11F9C30,000007FFB11F9C50,275,MR,28,0,4,0,6668641,0
000007FFB11F9CC8,000007FFB11F9CE8,275,MR,29,0,4,0,6668641,0
000007FFB11F9D60,000007FFB11F9D80,275,MR,30,0,4,0,6668641,0
000007FFB11F9DF8,000007FFB11F9E18,275,MR,31,0,4,0,6668641,0
000007FFB11F9E90,000007FFB11F9EB0,275,MR,201,0,4,0,6668641,0
000007FFB2BDBA00,000007FFB2BDBA28,147,TM,54593,0,2,0,0,0
000007FFB2BDBB00,000007FFB2BDBB28,147,TM,54698,0,2,0,0,0
000007FFB2BDBC00,000007FFB2BDBC28,147,TM,54754,0,2,0,0,0
000007FFB2BDBD00,000007FFB2BDBD28,147,TM,54760,0,2,0,0,0
000007FFB2BDBE00,000007FFB2BDBE28,147,TM,54796,0,2,0,0,0
000007FFB2BDBF00,000007FFB2BDBF28,147,TM,54806,0,2,0,0,0
000007FFB2BDC000,000007FFB2BDC028,147,TM,56029,0,2,0,0,0
000007FFB2BDC100,000007FFB2BDC128,147,TM,56565,0,2,0,0,0
000007FFB2BDC218,000007FFB2BDC240,147,TM,57385,0,2,0,0,0
000007FFB2BDC318,000007FFB2BDC340,147,TM,58768,0,2,0,0,0
000007FFB2BDC418,000007FFB2BDC440,147,TM,58907,0,2,0,0,0
000007FFB2BDC518,000007FFB2BDC540,147,TM,59460,0,2,0,0,0
000007FFB2BDC618,000007FFB2BDC640,147,TM,59849,0,2,0,0,0
000007FFB2BDC718,000007FFB2BDC740,147,TM,60154,0,2,0,0,0
000007FFB2BDC818,000007FFB2BDC840,147,TM,60215,0,2,0,0,0
000007FFB2BDC918,000007FFB2BDC940,147,TM,60235,0,3,0,0,0
000007FFB2BDCA30,000007FFB2BDCA58,147,TM,60252,0,3,0,0,0
000007FFB2BDCB30,000007FFB2BDCB58,147,TM,60258,0,2,0,0,0
000007FFB2BDCC30,000007FFB2BDCC58,147,TM,60262,0,2,0,0,0
000007FFB2BDCD30,000007FFB2BDCD58,147,TM,60266,0,2,0,0,0
000007FFB2BDCE30,000007FFB2BDCE58,147,TM,60269,0,2,0,0,0
000007FFB2BDCF30,000007FFB2BDCF58,147,TM,60273,0,2,0,0,0
000007FFB2BDD030,000007FFB2BDD058,147,TM,60455,0,2,0,0,0
000007FFB2BDD130,000007FFB2BDD158,147,TM,60460,0,2,0,0,0
000007FFB2BDD248,000007FFB2BDD270,147,TM,60257,0,3,0,0,0
000007FFB2BDD348,000007FFB2BDD370,147,TM,59177,0,3,0,0,0
000007FFB2C4D488,000007FFB2C4D4C0,147,TX,917520,229680,6,0,0,0


output from v$session

SADDR,SID,SERIAL#,AUDSID,PADDR,USER#,USERNAME,COMMAND,OWNERID,TADDR,LOCKWAIT,STATUS,SERVER,SCHEMA#,SCHEMANAME,OSUSER,PROCESS,MACHINE,TERMINAL,PROGRAM,TYPE,SQL_ADDRESS,SQL_HASH_VALUE,SQL_ID,SQL_CHILD_NUMBER,PREV_SQL_ADDR,PREV_HASH_VALUE,PREV_SQL_ID,PREV_CHILD_NUMBER,PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID,PLSQL_OBJECT_ID,PLSQL_SUBPROGRAM_ID,MODULE,MODULE_HASH,ACTION,ACTION_HASH,CLIENT_INFO,FIXED_TABLE_SEQUENCE,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,LOGON_TIME,LAST_CALL_ET,PDML_ENABLED,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,RESOURCE_CONSUMER_GROUP,PDML_STATUS,PDDL_STATUS,PQ_STATUS,CURRENT_QUEUE_DURATION,CLIENT_IDENTIFIER,BLOCKING_SESSION_STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION,SEQ#,EVENT#,EVENT,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW,P3TEXT,P3,P3RAW,WAIT_CLASS_ID,WAIT_CLASS#,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE,SERVICE_NAME,SQL_TRACE,SQL_TRACE_WAITS,SQL_TRACE_BINDS
000007FFB1099B80,21,10023,852823,000007FFB105E560,64,RAKLIVE,0,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,Time,1360:4060,RAK\TAAC,TAAC,TASync.exe,USER,00,0,,,000007FF22A2F9E0,1588169388,fwffv21gam2pc,0,,,,,,4029777240,,0,,11787332,-1,0,0,0,10/18/2015 9:03:41 AM,38,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,60683,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,38,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB10B5D08,42,16552,879218,000007FFB100CC60,64,RAKLIVE,3,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,hanif,6104:1684,RAK\HANIF-PAINTING,HANIF-PAINTING,,USER,000007FF22D92BB0,2714157574,86zy856hwdhh6,65,000007FF22D92BB0,2714157574,86zy856hwdhh6,65,,,,,,0,,0,ORION,11763450,55806,14,61115,0,10/23/2015 11:12:19 AM,49122,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,28710,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,49122,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB10EE018,84,12829,824004,000007FFB1029D48,68,HRPORTAL,0,2147483644,,,INACTIVE,DEDICATED,68,HRPORTAL,callrec,1772:1760,RAK\CALLRECORD,CALLRECORD,Toad.exe,USER,00,0,,,000007FF224E6420,1358752758,16751498gtuzq,0,,,,,TOAD 10.6.1.3,1034355835,,0,,8521978,16,1,60377,0,10/5/2015 11:59:05 AM,1620684,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,273,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,1620684,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB1118D18,116,63362,880502,000007FFB100B3E8,64,RAKLIVE,3,2147483644,000007FFB2C860C8,,INACTIVE,DEDICATED,64,RAKLIVE,a.yousry,4092:2024,RAK\YOUSRY-PROD,YOUSRY-PROD,,USER,00000000F969D550,3820445193,0x70nkbjvfrh9,1,00000000F969D550,3820445193,0x70nkbjvfrh9,1,,,,,,0,,0,ORION,11787352,75500,11,232742,0,10/24/2015 6:01:24 AM,6,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,11878,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,6,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB111F820,121,45596,876960,000007FFB1043D40,64,RAKLIVE,3,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,a.adnan,4156:3372,RAK\ABED-MATERIAL,ABED-MATERIAL,,USER,000007FF22D92BB0,2714157574,86zy856hwdhh6,87,000007FF22D92BB0,2714157574,86zy856hwdhh6,87,,,,,,0,,0,ORION,11588662,55563,25,22760,0,10/22/2015 10:18:19 AM,144426,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,16674,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,144426,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB112B8C8,130,5708,880512,000007FFB100ED00,64,RAKLIVE,0,2147483644,000007FFB2C9B560,,INACTIVE,DEDICATED,64,RAKLIVE,avinash,436:552,RAK\AVINASH_STORE,AVINASH_STORE,,USER,00,0,,,000007FF21D28498,200844701,7tpg49s5zj9cx,5,,,,,,0,,0,ORION,11786728,-1,0,0,0,10/24/2015 6:07:40 AM,325,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,898,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,325,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB1134EA0,137,40329,880524,000007FFB1012E40,64,RAKLIVE,3,2147483644,000007FFB2CAA110,,INACTIVE,DEDICATED,64,RAKLIVE,r.borromeo,3112:504,RAK\RUSTY-MPS,RUSTY-MPS,,USER,000007FF20A3C078,1095721890,72n8ms10nysx2,1,000007FF20A3C078,1095721890,72n8ms10nysx2,1,,,,,,0,,0,ORION,11787351,60885,24,207115,0,10/24/2015 6:12:21 AM,18,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,2894,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,18,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB113E478,144,5425,880499,000007FFB1009B70,64,RAKLIVE,0,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,hanif,5216:4260,RAK\HANIF-PAINTING,HANIF-PAINTING,,USER,00,0,,,000007FF20AFF100,1352235489,fzwgq5t89kyg1,2,,,,,,0,,0,,11785156,58374,23,254569,0,10/24/2015 5:59:53 AM,794,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,7671,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,794,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB11424B0,147,4892,880507,000007FFB100C438,64,RAKLIVE,0,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,r.sankar,7460:7200,RAK\RAVI-FABRI,RAVI-FABRI,,USER,00,0,,,000007FF223A0BC8,1198464677,dbkdvut3qy7p5,0,,,,,,0,,0,ORION,11787350,60257,18,284331,0,10/24/2015 6:05:41 AM,21,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,10694,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,21,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB115BB68,166,11091,880514,000007FFB100FD50,64,RAKLIVE,0,2147483644,000007FFB2C943D8,,INACTIVE,DEDICATED,64,RAKLIVE,avinash,3796:3800,RAK\AVINASH_STORE,AVINASH_STORE,,USER,00,0,,,000007FF21D28498,200844701,7tpg49s5zj9cx,5,,,,,,0,,0,ORION,11786825,-1,0,0,0,10/24/2015 6:07:50 AM,317,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,898,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,317,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB1161108,170,19819,880518,000007FFB10115C8,5,SYSTEM,3,2147483644,,,ACTIVE,DEDICATED,5,SYSTEM,arif,6064:4908,RAK\ARIF-IT,ARIF-IT,,USER,000007FF203CB1E8,3881741610,by8t23zmpxb9a,0,00000001078CF978,306498082,dwcwtn8949kj2,0,,,,,TOAD 9.0.1.8,2108215408,,0,,11787364,3,1,10888,0,10/24/2015 6:09:41 AM,0,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NOT IN WAIT,,,117,257,SQL*Net more data to client,driver id,1413697536,0000000054435000,#bytes,1997,00000000000007CD,,0,00,2000153315,7,Network,-1,0,WAITED SHORT TIME,rakdb,DISABLED,FALSE,FALSE
000007FFB116BC48,178,63416,880510,000007FFB100D488,64,RAKLIVE,0,2147483644,000007FFB2CB8CC0,,INACTIVE,DEDICATED,64,RAKLIVE,avinash,2160:2196,RAK\AVINASH_STORE,AVINASH_STORE,,USER,00,0,,,000007FF21D28498,200844701,7tpg49s5zj9cx,5,,,,,,0,,0,ORION,11786573,55807,22,32295,0,10/24/2015 6:07:20 AM,345,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,900,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,345,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB116FC80,181,62379,879765,000007FFB100BC10,64,RAKLIVE,3,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,hanif,4552:4744,RAK\HANIF-PAINTING,HANIF-PAINTING,,USER,000007FF22D92BB0,2714157574,86zy856hwdhh6,50,000007FF22D92BB0,2714157574,86zy856hwdhh6,50,,,,,,0,,0,ORION,11785151,-1,0,0,0,10/23/2015 6:44:04 PM,799,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,8207,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,799,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB1175220,185,60572,866497,000007FFB1016758,64,RAKLIVE,0,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,orionrak,3544:216,RAK\CLIENT-PC,CLIENT-PC,C:\Documents and Settings\orionrak\Desktop\ORION - Administrator,USER,00,0,,,000007FF228D2130,3930020797,23kyku3p3yqxx,0,,,,,C:\Documents and Settings\orionrak\Desktop\ORION,1193612126,,0,,11787353,55807,21,39812,0,10/20/2015 11:01:19 AM,3,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,9284,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,3,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB117BD28,190,65488,876677,000007FFB105AC48,64,RAKLIVE,0,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,sajid,6336:5932,RAK\ADC,ADC,,USER,00,0,,,000007FF22E0EA98,2721236461,fsdyfykj35jgd,1,,,,,,0,,0,ORION,11465866,59028,13,238876,0,10/22/2015 8:29:02 AM,164224,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,4015,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,164224,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB1187DD0,199,53236,880511,000007FFB100DCB0,64,RAKLIVE,0,2147483644,000007FFB2C3F178,,INACTIVE,DEDICATED,64,RAKLIVE,avinash,2900:2744,RAK\AVINASH_STORE,AVINASH_STORE,,USER,00,0,,,000007FF21D28498,200844701,7tpg49s5zj9cx,5,,,,,,0,,0,ORION,11786648,-1,0,0,0,10/24/2015 6:07:31 AM,336,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,898,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,336,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB1189338,200,14180,880494,000007FFB1007AD0,64,RAKLIVE,0,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,s.naseem,3444:3424,RAK\NASEEM-STORE,NASEEM-STORE,,USER,00,0,,,000007FF21D05A70,3360787591,cgjqjv7453347,2,,,,,,0,,0,ORION,11787036,55596,14,55623,0,10/24/2015 5:58:49 AM,147,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,3218,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,147,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB118BE08,202,57958,880513,000007FFB100A398,64,RAKLIVE,0,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,arif,6064:4908,RAK\ARIF-IT,ARIF-IT,,USER,00,0,,,000007FF22F624D8,3304355925,3grm9x72g8y2p,0,,,,,TOAD 9.0.1.8,2108215408,,0,,11786740,321,1,2554,0,10/24/2015 6:07:48 AM,320,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,503,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,320,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB118FE40,205,63534,879687,000007FFB1008B20,64,RAKLIVE,0,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,r.sankar,7656:1520,RAK\RAVI-FABRI,RAVI-FABRI,,USER,00,0,,,000007FF1FA06E20,181990855,8chvqjs5djxf7,0,,,,,,0,,0,ORION,11786414,60886,22,248505,0,10/23/2015 5:33:30 PM,432,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,6346,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,432,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB119E9B8,216,8848,880519,000007FFB1011DF0,64,RAKLIVE,0,2147483644,000007FFB2CB09F8,,INACTIVE,DEDICATED,64,RAKLIVE,l.ramores,2832:2564,RAK\LEVY-MAIN,LEVY-MAIN,,USER,00,0,,,000007FF21AF1BB8,1207419554,8xwv59t3zghp2,0,,,,,,0,,0,ORION,11787353,59204,13,240828,0,10/24/2015 6:09:46 AM,0,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,9502,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,0,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB119FF20,217,20604,0,000007FFB100E4D8,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,2460,ORION,ORION,ORACLE.EXE (J000),USER,00,0,,0,00,0,,0,,,,,,0,,0,,11787234,-1,0,0,0,10/24/2015 6:12:18 AM,54,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,UNKNOWN,,,1,232,jobq slave wait,,0,00,,0,00,,0,00,2723168908,6,Idle,0,54,WAITING,SYS$USERS,DISABLED,FALSE,FALSE
000007FFB11AEA98,228,7085,880490,000007FFB10082F8,64,RAKLIVE,3,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,m.kamaldeen,3804:2776,RAK\KAMAL-PAINTING,KAMAL-PAINTING,,USER,000007FF22D92BB0,2714157574,86zy856hwdhh6,50,000007FF22D92BB0,2714157574,86zy856hwdhh6,50,,,,,,0,,0,ORION,11786740,60473,13,304061,0,10/24/2015 5:57:40 AM,319,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,5834,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,319,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB11B2AD0,231,59088,880515,000007FFB1010DA0,64,RAKLIVE,0,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,m.kamaldeen,1920:3828,RAK\KAMAL-PAINTING,KAMAL-PAINTING,,USER,00,0,,,000007FF20AFF100,1352235489,fzwgq5t89kyg1,2,,,,,,0,,0,,11786941,57872,22,274113,0,10/24/2015 6:07:54 AM,192,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,6057,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,192,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB11B95D8,236,61954,879704,000007FFB100ABC0,64,RAKLIVE,0,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,r.sankar,7036:8040,RAK\RAVI-FABRI,RAVI-FABRI,,USER,00,0,,,000007FF1FAC5410,3128361857,2u2m2jfx7f0w1,0,,,,,,0,,0,,11787234,56689,22,45390,0,10/23/2015 5:48:16 PM,60,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,9112,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,60,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB11BAB40,237,46876,880521,000007FFB1012618,64,RAKLIVE,0,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,m.porras,1748:4520,RAK\MARK-PROD,MARK-PROD,,USER,00,0,,,000007FF218FDC20,185450481,axydqdc5hvgzj,0,,,,,,0,,0,ORION,11787345,60934,9,253559,0,10/24/2015 6:11:11 AM,28,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,7587,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,28,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB11BEB78,240,3679,871912,000007FFB1036108,64,RAKLIVE,0,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,m.araj,4496:4728,RAK\ADNAN-PLANNING,ADNAN-PLANNING,,USER,00,0,,,000007FF228347C0,2647875976,4d4hbcyfx6sc8,1,,,,,,0,,0,,11299090,60381,22,239285,0,10/20/2015 11:04:16 AM,232016,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,4048,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,232016,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB11C00E0,241,58162,880493,000007FFB1009348,64,RAKLIVE,0,2147483644,000007FFB2C30E68,,INACTIVE,DEDICATED,64,RAKLIVE,s.naseem,4016:4020,RAK\NASEEM-STORE,NASEEM-STORE,,USER,00,0,,,000007FF21D28498,200844701,7tpg49s5zj9cx,5,,,,,,0,,0,ORION,11785060,55807,22,32294,0,10/24/2015 5:58:34 AM,867,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,897,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,867,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB11C1648,242,50109,853275,000007FFB1050928,64,RAKLIVE,0,2147483644,,,INACTIVE,DEDICATED,64,RAKLIVE,m.araj,2620:3088,RAK\ADNAN-PLANNING,ADNAN-PLANNING,,USER,00,0,,,000007FF1FA06E20,181990855,8chvqjs5djxf7,3,,,,,,0,,0,ORION,11665401,60790,22,247815,0,10/18/2015 12:09:35 PM,138789,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,41942,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,138789,WAITING,rakdb,DISABLED,FALSE,FALSE
000007FFB11CEC58,252,4,0,000007FFB10072A8,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,2272,ORION,ORION,ORACLE.EXE (q000),BACKGROUND,000007FF21E60898,3393152264,4gd6b1r53yt88,0,00,0,,0,,,,,,0,,0,,79,-1,0,0,0,8/8/2015 1:46:04 AM,6668829,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,47882,270,Streams AQ: waiting for time management or cleanup tasks,,0,00,,0,00,,0,00,2723168908,6,Idle,0,2711,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE
000007FFB11D9798,260,9,0,000007FFB100F528,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,4984,ORION,ORION,ORACLE.EXE (QMNC),BACKGROUND,00,0,,0,00,0,,0,,,,,,0,,0,,0,-1,0,0,0,8/8/2015 1:45:54 AM,6668838,NO,NONE,NONE,NO,,DISABLED,DISABLED,DISABLED,0,,NO HOLDER,,,6,274,Streams AQ: qmn coordinator idle wait,,0,00,,0,00,,0,00,2723168908,6,Idle,0,6668823,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE
000007FFB11E1808,266,6,0,000007FFB1010578,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,2280,ORION,ORION,ORACLE.EXE (q001),BACKGROUND,00,0,,0,00,0,,0,,,,,,0,,0,,79,-1,0,0,0,8/8/2015 1:46:04 AM,6668829,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,UNKNOWN,,,24,275,Streams AQ: qmn slave idle wait,,0,00,,0,00,,0,00,2723168908,6,Idle,0,516794,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE
000007FFB11E2D70,267,1,0,000007FFB1005A30,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,4520,ORION,ORION,ORACLE.EXE (MMNL),BACKGROUND,00,0,,0,00,0,,0,,,,,,0,,0,,0,-1,0,0,0,8/8/2015 1:45:28 AM,6668871,NO,NONE,NONE,NO,,DISABLED,DISABLED,DISABLED,0,,NO HOLDER,,,1395,3,rdbms ipc message,timeout,100,0000000000000064,,0,00,,0,00,2723168908,6,Idle,0,111390,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE
000007FFB11E42D8,268,1,0,000007FFB1005208,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,4516,ORION,ORION,ORACLE.EXE (MMON),BACKGROUND,000007FF21E60898,3393152264,4gd6b1r53yt88,0,00,0,,0,,,9807,6,,0,,0,,53,8850,3,2662,0,8/8/2015 1:45:28 AM,6668871,NO,NONE,NONE,NO,,DISABLED,DISABLED,DISABLED,0,,NO HOLDER,,,14487,3,rdbms ipc message,timeout,300,000000000000012C,,0,00,,0,00,2723168908,6,Idle,0,85,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE
000007FFB11E5840,269,1,0,000007FFB10049E0,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,4512,ORION,ORION,ORACLE.EXE (CJQ0),BACKGROUND,00,0,,0,00,0,,0,,,,,,0,,0,,11787345,-1,1,11290,0,8/8/2015 1:45:28 AM,6668871,NO,NONE,NONE,NO,,DISABLED,DISABLED,DISABLED,0,,NO HOLDER,,,60066,3,rdbms ipc message,timeout,500,00000000000001F4,,0,00,,0,00,2723168908,6,Idle,0,767,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE
000007FFB11E6DA8,270,1,0,000007FFB10041B8,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,4508,ORION,ORION,ORACLE.EXE (RECO),BACKGROUND,00,0,,0,00,0,,0,,,,,,0,,0,,0,-1,0,0,0,8/8/2015 1:45:28 AM,6668871,NO,NONE,NONE,NO,,DISABLED,DISABLED,DISABLED,0,,NO HOLDER,,,3995,3,rdbms ipc message,timeout,180000,000000000002BF20,,0,00,,0,00,2723168908,6,Idle,0,62581,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE
000007FFB11E8310,271,1,0,000007FFB1003990,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,4504,ORION,ORION,ORACLE.EXE (SMON),BACKGROUND,00,0,,0,00,0,,0,,,,,,0,,0,,0,0,2,105,0,8/8/2015 1:45:28 AM,6668871,NO,NONE,NONE,NO,,DISABLED,DISABLED,DISABLED,0,,NO HOLDER,,,30497,191,smon timer,sleep time,300,000000000000012C,failed,0,00,,0,00,2723168908,6,Idle,0,362,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE
000007FFB11E9878,272,1,0,000007FFB1003168,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,4500,ORION,ORION,ORACLE.EXE (CKPT),BACKGROUND,00,0,,0,00,0,,0,,,,,,0,,0,,0,-1,0,0,0,8/8/2015 1:45:28 AM,6668871,NO,NONE,NONE,NO,,DISABLED,DISABLED,DISABLED,0,,NO HOLDER,,,6997,3,rdbms ipc message,timeout,300,000000000000012C,,0,00,,0,00,2723168908,6,Idle,0,0,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE
000007FFB11EADE0,273,1,0,000007FFB1002940,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,4496,ORION,ORION,ORACLE.EXE (LGWR),BACKGROUND,00,0,,0,00,0,,0,,,,,,0,,0,,0,-1,0,0,0,8/8/2015 1:45:28 AM,6668871,NO,NONE,NONE,NO,,DISABLED,DISABLED,DISABLED,0,,NO HOLDER,,,3379,3,rdbms ipc message,timeout,300,000000000000012C,,0,00,,0,00,2723168908,6,Idle,0,3,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE
000007FFB11EC348,274,1,0,000007FFB1002118,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,4492,ORION,ORION,ORACLE.EXE (DBW1),BACKGROUND,00,0,,0,00,0,,0,,,,,,0,,0,,0,-1,0,0,0,8/8/2015 1:45:28 AM,6668871,NO,NONE,NONE,NO,,DISABLED,DISABLED,DISABLED,0,,NO HOLDER,,,57315,3,rdbms ipc message,timeout,300,000000000000012C,,0,00,,0,00,2723168908,6,Idle,0,12,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE
000007FFB11ED8B0,275,1,0,000007FFB10018F0,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,4488,ORION,ORION,ORACLE.EXE (DBW0),BACKGROUND,00,0,,0,00,0,,0,,,,,,0,,0,,0,-1,0,0,0,8/8/2015 1:45:28 AM,6668871,NO,NONE,NONE,NO,,DISABLED,DISABLED,DISABLED,0,,NO HOLDER,,,1208,3,rdbms ipc message,timeout,300,000000000000012C,,0,00,,0,00,2723168908,6,Idle,0,18,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE
000007FFB11EEE18,276,1,0,000007FFB10010C8,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,4468,ORION,ORION,ORACLE.EXE (MMAN),BACKGROUND,00,0,,0,00,0,,0,,,,,,0,,0,,0,-1,0,0,0,8/8/2015 1:45:27 AM,6668871,NO,NONE,NONE,NO,,DISABLED,DISABLED,DISABLED,0,,NO HOLDER,,,4974,3,rdbms ipc message,timeout,300,000000000000012C,,0,00,,0,00,2723168908,6,Idle,0,4121,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE
000007FFB11F0380,277,1,0,000007FFB10008A0,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,4464,ORION,ORION,ORACLE.EXE (PSP0),BACKGROUND,00,0,,0,00,0,,0,,,,,,0,,0,,0,-1,0,0,0,8/8/2015 1:45:27 AM,6668871,NO,NONE,NONE,NO,,DISABLED,DISABLED,DISABLED,0,,NO HOLDER,,,11648,3,rdbms ipc message,timeout,300,000000000000012C,,0,00,,0,00,2723168908,6,Idle,0,746,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE
000007FFB11F18E8,278,1,0,000007FFB1000078,0,,0,2147483644,,,ACTIVE,DEDICATED,0,SYS,SYSTEM,4460,ORION,ORION,ORACLE.EXE (PMON),BACKGROUND,00,0,,0,00,0,,0,,,,,,0,,0,,0,-1,0,0,0,8/8/2015 1:45:27 AM,6668871,NO,NONE,NONE,NO,,DISABLED,DISABLED,DISABLED,0,,NO HOLDER,,,405,1,pmon timer,duration,300,000000000000012C,,0,00,,0,00,2723168908,6,Idle,0,139410,WAITING,SYS$BACKGROUND,DISABLED,FALSE,FALSE


Re: Locking modes [message #643965 is a reply to message #643964] Fri, 23 October 2015 21:23 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SELECT Decode(request, 0, 'Holder: ', 
                       'Waiter: ') 
       ||vl.sid sess, 
       status, 
       id1, 
       id2, 
       lmode, 
       request, 
       vl.TYPE 
FROM   v$lock vl, 
       v$session vs 
WHERE  ( id1, id2, vl.TYPE ) IN (SELECT id1, 
                                        id2, 
                                        TYPE 
                                 FROM   v$lock 
                                 WHERE  request > 0) 
       AND vl.sid = vs.sid 
ORDER  BY id1, 
          request 
Re: Locking modes [message #643966 is a reply to message #643965] Sat, 24 October 2015 02:09 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks blackswan, as of now i am not getting any output since there are no blocking sessions. i will post it as soon as i get it.
Re: Locking modes [message #644002 is a reply to message #643965] Sun, 25 October 2015 00:00 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
Hi ,

I am getting the following locks while inserting.


SESS,STATUS,ID1,ID2,LMODE,REQUEST,TYPE
Holder: 165,INACTIVE,60877,0,3,0,TM
Waiter: 90,ACTIVE,60877,0,0,2,TM
Waiter: 234,ACTIVE,60877,0,0,2,TM
Waiter: 85,ACTIVE,60877,0,0,2,TM
Waiter: 105,ACTIVE,60877,0,0,2,TM
Waiter: 29,ACTIVE,60877,0,0,4,TM
Waiter: 198,ACTIVE,60877,0,0,4,TM


SELECT * FROM V$SESSION WHERE SID=165

SADDR,SID,SERIAL#,AUDSID,PADDR,USER#,USERNAME,COMMAND,OWNERID,TADDR,LOCKWAIT,STATUS,SERVER,SCHEMA#,SCHEMANAME,OSUSER,PROCESS,MACHINE,TERMINAL,PROGRAM,TYPE,SQL_ADDRESS,SQL_HASH_VALUE,SQL_ID,SQL_CHILD_NUMBER,PREV_SQL_ADDR,PREV_HASH_VALUE,PREV_SQL_ID,PREV_CHILD_NUMBER,PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID,PLSQL_OBJECT_ID,PLSQL_SUBPROGRAM_ID,MODULE,MODULE_HASH,ACTION,ACTION_HASH,CLIENT_INFO,FIXED_TABLE_SEQUENCE,ROW_WAIT_OBJ#,ROW_WAIT_FILE#,ROW_WAIT_BLOCK#,ROW_WAIT_ROW#,LOGON_TIME,LAST_CALL_ET,PDML_ENABLED,FAILOVER_TYPE,FAILOVER_METHOD,FAILED_OVER,RESOURCE_CONSUMER_GROUP,PDML_STATUS,PDDL_STATUS,PQ_STATUS,CURRENT_QUEUE_DURATION,CLIENT_IDENTIFIER,BLOCKING_SESSION_STATUS,BLOCKING_INSTANCE,BLOCKING_SESSION,SEQ#,EVENT#,EVENT,P1TEXT,P1,P1RAW,P2TEXT,P2,P2RAW,P3TEXT,P3,P3RAW,WAIT_CLASS_ID,WAIT_CLASS#,WAIT_CLASS,WAIT_TIME,SECONDS_IN_WAIT,STATE,SERVICE_NAME,SQL_TRACE,SQL_TRACE_WAITS,SQL_TRACE_BINDS
000007FFB115A600,165,55418,883484,000007FFB1049F20,64,RAKLIVE,3,2147483644,000007FFB2C8E390,,INACTIVE,DEDICATED,64,RAKLIVE,osama.fawzy,4816:4672,RAK\OSAMA-QHSE,OSAMA-QHSE,,USER,000007FF21D05A70,3360787591,cgjqjv7453347,0,000007FF21D05A70,3360787591,cgjqjv7453347,0,,,,,,0,,0,ORION,12554850,60786,12,218996,0,10/25/2015 8:38:22 AM,457,NO,NONE,NONE,NO,,DISABLED,ENABLED,ENABLED,0,,NO HOLDER,,,2019,259,SQL*Net message from client,driver id,1413697536,0000000054435000,#bytes,1,0000000000000001,,0,00,2723168908,6,Idle,0,457,WAITING,rakdb,DISABLED,FALSE,FALSE


--Problem is during insert , dml lock is generated. ROW(SS)
INSERT INTO OT_WORKSHOP_TIME_HEAD(WTH_DT,WTH_TXN_CODE,WTH_NO,WTH_WORKCENTER,WTH_BAY,WTH_UNIT_CODE,WTH_WONO_LIST,WTH_REMARKS,WTH_ANNOTATION,WTH_CR_UID,WTH_CR_DT,WTH_UPD_UID,WTH_UPD_DT,WTH_SYS_ID,WTH_COMP_CODE,WTH_AMD_NO,WTH_APPR_DT,WTH_APPR_UID,WTH_APPR_STATUS,WTH_PRINT_STATUS,WTH_SUBMIT_STATUS,WTH_AMD_DT,WTH_AMD_USER_ID,WTH_AMD_RES_CODE,WTH_FLEX_01,WTH_FLEX_02,WTH_FLEX_03,WTH_FLEX_04,WTH_FLEX_05,WTH_FLEX_06,WTH_FLEX_07,WTH_FLEX_08,WTH_FLEX_09,WTH_FLEX_10) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,:33,:34)  




[Updated on: Sun, 25 October 2015 00:04]

Report message to a moderator

Re: Locking modes [message #644007 is a reply to message #644002] Sun, 25 October 2015 08:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post CREATE TABLE OT_WORKSHOP_TIME_HEAD statement
does OT_WORKSHOP_TIME_HEAD have PRIMARY KEY? If so post DDL how PK is created. If so, how is PK populated?
does OT_WORKSHOP_TIME_HEAD have any TRIGGER(s)? If so, post all TRIGGER code for OT_WORKSHOP_TIME_HEAD table.

You now can see that an application bug exists.
Re: Locking modes [message #644032 is a reply to message #644007] Mon, 26 October 2015 07:02 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
thanks blackswan,
i have to admit that it was due to poor design of application.It was written by my co worker , inside forms application , he is trying to update one table based on
another table, like copy paste.
I am checking on that.
Re: Locking modes [message #644035 is a reply to message #644032] Mon, 26 October 2015 08:16 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
post CREATE TABLE OT_WORKSHOP_TIME_HEAD statement
does OT_WORKSHOP_TIME_HEAD have PRIMARY KEY? If so post DDL how PK is created. If so, how is PK populated?
does OT_WORKSHOP_TIME_HEAD have any TRIGGER(s)? If so, post all TRIGGER code for OT_WORKSHOP_TIME_HEAD table.
Re: Locking modes [message #644038 is a reply to message #644035] Mon, 26 October 2015 23:23 Go to previous messageGo to next message
m.abdulhaq
Messages: 254
Registered: April 2013
Location: Ajman
Senior Member
Thank blackswan, here are those statements.


CREATE TABLE OT_WORKSHOP_TIME_HEAD
(
  WTH_SYS_ID         NUMBER(12),
  WTH_COMP_CODE      VARCHAR2(12 BYTE),
  WTH_TXN_CODE       VARCHAR2(12 BYTE),
  WTH_NO             NUMBER(10),
  WTH_DT             DATE,
  WTH_WORKCENTER     VARCHAR2(100 BYTE),
  WTH_BAY            VARCHAR2(100 BYTE),
  WTH_UNIT_CODE      VARCHAR2(12 BYTE),
  WTH_REMARKS        VARCHAR2(2000 BYTE),
  WTH_CR_UID         VARCHAR2(12 BYTE),
  WTH_CR_DT          DATE,
  WTH_UPD_UID        VARCHAR2(12 BYTE),
  WTH_UPD_DT         DATE,
  WTH_AMD_NO         NUMBER(4),
  WTH_AMD_DT         DATE,
  WTH_AMD_USER_ID    VARCHAR2(12 BYTE),
  WTH_AMD_RES_CODE   VARCHAR2(12 BYTE),
  WTH_APPR_STATUS    NUMBER(1),
  WTH_APPR_UID       VARCHAR2(12 BYTE),
  WTH_APPR_DT        DATE,
  WTH_ANNOTATION     VARCHAR2(2000 BYTE),
  WTH_PRINT_STATUS   NUMBER,
  WTH_SUBMIT_STATUS  NUMBER,
  WTH_FLEX_01        VARCHAR2(240 BYTE),
  WTH_FLEX_02        VARCHAR2(240 BYTE),
  WTH_FLEX_03        VARCHAR2(240 BYTE),
  WTH_FLEX_04        VARCHAR2(240 BYTE),
  WTH_FLEX_05        VARCHAR2(240 BYTE),
  WTH_FLEX_06        VARCHAR2(240 BYTE),
  WTH_FLEX_07        VARCHAR2(240 BYTE),
  WTH_FLEX_08        VARCHAR2(240 BYTE),
  WTH_FLEX_09        VARCHAR2(240 BYTE),
  WTH_FLEX_10        VARCHAR2(240 BYTE),
  WTH_WONO_LIST      VARCHAR2(150 BYTE)
)



[quote] does OT_WORKSHOP_TIME_HEAD have PRIMARY KEY? If so post DDL how PK is created. If so, how is PK populated?[/quote]

CREATE UNIQUE INDEX OT_WORKSHOP_TIME_HEAD_PK ON OT_WORKSHOP_TIME_HEAD
(WTH_SYS_ID)

ALTER TABLE OT_WORKSHOP_TIME_HEAD ADD (
  CONSTRAINT OT_WORKSHOP_TIME_HEAD_PK
 PRIMARY KEY
 (WTH_SYS_ID)



[quote] how is PK populated?[/quote]


CREATE SEQUENCE RAKLIVE.WTH_SYS_ID
  START WITH 14001
  MAXVALUE 999999999999999999999999999
  MINVALUE 1
  NOCYCLE
  CACHE 20
  NOORDER;

[quote]does OT_WORKSHOP_TIME_HEAD have any TRIGGER(s)? If so, post all TRIGGER code for OT_WORKSHOP_TIME_HEAD table.[\quote]

i have no triggers .



Re: Locking modes [message #644048 is a reply to message #644038] Tue, 27 October 2015 04:52 Go to previous messageGo to next message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you sure it's the insert that's getting blocked?
Are you sure the pk is always populated by the sequence?
Re: Locking modes [message #644644 is a reply to message #644048] Wed, 11 November 2015 10:28 Go to previous messageGo to next message
vittalgh
Messages: 1
Registered: April 2009
Junior Member
Hi

is COMMIT is missing after INSERT ?



garani
Re: Locking modes [message #644645 is a reply to message #644644] Wed, 11 November 2015 10:35 Go to previous message
cookiemonster
Messages: 13915
Registered: September 2008
Location: Rainy Manchester
Senior Member
If the only unique key on the table is populated by a sequence then a lack of commit shouldn't matter.
Previous Topic: SQL*Net roundtrips
Next Topic: dbms_xplan
Goto Forum:
  


Current Time: Tue Mar 19 05:42:15 CDT 2024