Home » SQL & PL/SQL » SQL & PL/SQL » update issue (oracle sql developer,oracle 11g)
update issue [message #587367] Fri, 14 June 2013 08:49 Go to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Hi,
While i was trying to update a specific column it is hanging.It is not showing any error.Kindly do needful.

Report message to a moderator

Re: update issue [message #587368 is a reply to message #587367] Fri, 14 June 2013 08:50 Go to previous messageGo to next message
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
Senior Member
There is probably another session that hasn't issued a COMMIT or ROLLBACK involving that table.

Report message to a moderator

Re: update issue [message #587369 is a reply to message #587368] Fri, 14 June 2013 08:56 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
But that type of lock will be row dependent right?Here i cant able to update any one of the table column.

Report message to a moderator

Re: update issue [message #587372 is a reply to message #587369] Fri, 14 June 2013 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/


my car does not go.
tell me how to make my car go.

Report message to a moderator

Re: update issue [message #587382 is a reply to message #587372] Fri, 14 June 2013 09:09 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Hi,

TABLE DDL:

REM START MST_PRDCT_SPCFCTN_CLR

CREATE TABLE "MST_PRDCT_SPCFCTN_CLR"
( "RC" VARCHAR2(2),
"PRDCTN_FMLY_CD" VARCHAR2(6),
"RGN_GRP" VARCHAR2(2),
"EXTRR_CLR_CD" VARCHAR2(5),
"INTRR_CLR_CD" VARCHAR2(2),
"APP_DTL_CD" VARCHAR2(1),
"G2B_RVSN_NO" VARCHAR2(2),
"SIS_RVSN_NO" VARCHAR2(2),
"APPLCTN_TYP" VARCHAR2(300),
"SPCL_CLR_MRK" VARCHAR2(1),
"FIL1" VARCHAR2(9),
"ADPT_DT" VARCHAR2(4),
"ABLSH_DT" VARCHAR2(4),
"RLS_DT" VARCHAR2(6),
"UPDT_DT_TM" VARCHAR2(6),
"FIL2" VARCHAR2(848),
"LF" VARCHAR2(1),
"CRTD_BY" VARCHAR2(20),
"CRTD_DT" TIMESTAMP (6),
"UPDTD_BY" VARCHAR2(20),
"UPDTD_DT" TIMESTAMP (6)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM"

REM END SCSK_DEV MST_PRDCT_SPCFCTN_CLR;


Here i couldnt able to update any column.It is hanging.

Report message to a moderator

Re: update issue [message #587384 is a reply to message #587382] Fri, 14 June 2013 09:11 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Hi,
Now the same problem happens for many tables.Shall i ask to restart oracle server.What is the source of this issue?

Report message to a moderator

Re: update issue [message #587386 is a reply to message #587382] Fri, 14 June 2013 09:14 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It works for me
SQL> ed
Wrote file afiedt.buf

  1  CREATE TABLE "MST_PRDCT_SPCFCTN_CLR"
  2  (  "RC" VARCHAR2(2),
  3  "PRDCTN_FMLY_CD" VARCHAR2(6),
  4  "RGN_GRP" VARCHAR2(2),
  5  "EXTRR_CLR_CD" VARCHAR2(5),
  6  "INTRR_CLR_CD" VARCHAR2(2),
  7  "APP_DTL_CD" VARCHAR2(1),
  8  "G2B_RVSN_NO" VARCHAR2(2),
  9  "SIS_RVSN_NO" VARCHAR2(2),
 10  "APPLCTN_TYP" VARCHAR2(300),
 11  "SPCL_CLR_MRK" VARCHAR2(1),
 12  "FIL1" VARCHAR2(9),
 13  "ADPT_DT" VARCHAR2(4),
 14  "ABLSH_DT" VARCHAR2(4),
 15  "RLS_DT" VARCHAR2(6),
 16  "UPDT_DT_TM" VARCHAR2(6),
 17  "FIL2" VARCHAR2(848),
 18  "LF" VARCHAR2(1),
 19  "CRTD_BY" VARCHAR2(20),
 20  "CRTD_DT" TIMESTAMP (6),
 21  "UPDTD_BY" VARCHAR2(20),
 22  "UPDTD_DT" TIMESTAMP (6)
 23* )
SQL> /

Table created.

SQL> INSERT into MST_PRDCT_SPCFCTN_CLR (RC) VALUES ('AA');

1 row created.

SQL> UPDATE MST_PRDCT_SPCFCTN_CLR SET RC = 'AB';

1 row updated.

Report message to a moderator

Re: update issue [message #587388 is a reply to message #587386] Fri, 14 June 2013 09:19 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Hi,
Ya...You are right.Now only i am facing this issue for this table.

Report message to a moderator

Re: update issue [message #587390 is a reply to message #587388] Fri, 14 June 2013 09:21 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
I don't know what you have.
I don't know what you do.
I don't know what you see.
It is really, Really, REALLY difficult to fix a problem that can not be seen.
use COPY & PASTE so we can see what you do & how Oracle responds.

Report message to a moderator

Re: update issue [message #587394 is a reply to message #587390] Fri, 14 June 2013 09:26 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
REM INSERTING into MST_PRDCT_SPCFCTN_CLR
Insert into MST_PRDCT_SPCFCTN_CLR (RC,PRDCTN_FMLY_CD,RGN_GRP,EXTRR_CLR_CD,INTRR_CLR_CD,APP_DTL_CD,G2B_RVSN_NO,SIS_RVSN_NO,APPLCTN_TYP,SPCL_CLR_MRK,FIL1,ADPT_DT,ABLSH_D T,RLS_DT,UPDT_DT_TM,FIL2,LF,CRTD_BY,CRTD_DT,UPDTD_BY,UPDTD_DT) values ('CO','9261SD','AD','AASAD','AD','A','SD','SA','000SAD234234SDFSFDSFDSFSXCVXVSDFLDFSDFLSFKSDFOSDFSDFSNFSMFSDNFSJFSDKFSDFNSMFBNFSREWBR WENRBSBFSFBSFBSFSDFDSFNBSDFMSDNBFSDFHWEBRWNEBRWMEWEREWREWNRMNEWRNEWREWNREWRNWEKRJHEWKRHEWREWREWREWREWRSDFDSREWRDXVCSDFSFSFDSFSDFSJDFK SLJFWEORJEWREJWRJEWREWRSMNFSBFDSDFSJDHFKSHFCFSNDFSDNFKSJFKEJWHRKEWHRSNFSDNFSDFNWJE','K','JRHWEH43H','5H32','5NFS','NDFSND','FSNDFK',n ull,null,'Interface',to_timestamp('13-JUN-13 06.47.44.214000000 PM','DD-MON-RR HH.MI.SS.FF AM'),null,null);
Insert into MST_PRDCT_SPCFCTN_CLR (RC,PRDCTN_FMLY_CD,RGN_GRP,EXTRR_CLR_CD,INTRR_CLR_CD,APP_DTL_CD,G2B_RVSN_NO,SIS_RVSN_NO,APPLCTN_TYP,SPCL_CLR_MRK,FIL1,ADPT_DT,ABLSH_D T,RLS_DT,UPDT_DT_TM,FIL2,LF,CRTD_BY,CRTD_DT,UPDTD_BY,UPDTD_DT) values ('CO','9261SD','AD','AASAD','AD','A','SD','SA','DADSAD234234SDFSFDSFDSFSXCVXVSDFLDFSDFLSFKSDFOSDFSDFSNFSMFSDNFSJFSDKFSDFNSMFBNFSREWBR WENRBSBFSFBSFBSFSDFDSFNBSDFMSDNBFSDFHWEBRWNEBRWMEWEREWREWNRMNEWRNEWREWNREWRNWEKRJHEWKRHEWREWREWREWREWRSDFDSREWRDXVCSDFSFSFDSFSDFSJDFK SLJFWEORJEWREJWRJEWREWRSMNFSBFDSDFSJDHFKSHFCFSNDFSDNFKSJFKEJWHRKEWHRSNFSDNFSDFNWJE','K','JRHWEH43H','5H32','5NFS','NDFSND','FSNDFK',n ull,null,'Interface',to_timestamp('13-JUN-13 07.34.56.212000000 PM','DD-MON-RR HH.MI.SS.FF AM'),null,null);
Insert into MST_PRDCT_SPCFCTN_CLR (RC,PRDCTN_FMLY_CD,RGN_GRP,EXTRR_CLR_CD,INTRR_CLR_CD,APP_DTL_CD,G2B_RVSN_NO,SIS_RVSN_NO,APPLCTN_TYP,SPCL_CLR_MRK,FIL1,ADPT_DT,ABLSH_D T,RLS_DT,UPDT_DT_TM,FIL2,LF,CRTD_BY,CRTD_DT,UPDTD_BY,UPDTD_DT) values ('CO','9261SD','AD','AASAD','AD','A','SD','SA','DADSAD234234SDFSFDSFDSFSXCVXVSDFLDFSDFLSFKSDFOSDFSDFSNFSMFSDNFSJFSDKFSDFNSMFBNFSREWBR WENRBSBFSFBSFBSFSDFDSFNBSDFMSDNBFSDFHWEBRWNEBRWMEWEREWREWNRMNEWRNEWREWNREWRNWEKRJHEWKRHEWREWREWREWREWRSDFDSREWRDXVCSDFSFSFDSFSDFSJDFK SLJFWEORJEWREJWRJEWREWRSMNFSBFDSDFSJDHFKSHFCFSNDFSDNFKSJFKEJWHRKEWHRSNFSDNFSDFNWJE','K','JRHWEH43H','5H32','5NFS','NDFSND','FSNDFK',n ull,null,'Interface',to_timestamp('13-JUN-13 08.17.23.986000000 PM','DD-MON-RR HH.MI.SS.FF AM'),null,null);
Insert into MST_PRDCT_SPCFCTN_CLR (RC,PRDCTN_FMLY_CD,RGN_GRP,EXTRR_CLR_CD,INTRR_CLR_CD,APP_DTL_CD,G2B_RVSN_NO,SIS_RVSN_NO,APPLCTN_TYP,SPCL_CLR_MRK,FIL1,ADPT_DT,ABLSH_D T,RLS_DT,UPDT_DT_TM,FIL2,LF,CRTD_BY,CRTD_DT,UPDTD_BY,UPDTD_DT) values ('CO','9261SD','AD','AASAD','AD','A','SD','SA','DADSAD234234SDFSFDSFDSFSXCVXVSDFLDFSDFLSFKSDFOSDFSDFSNFSMFSDNFSJFSDKFSDFNSMFBNFSREWBR WENRBSBFSFBSFBSFSDFDSFNBSDFMSDNBFSDFHWEBRWNEBRWMEWEREWREWNRMNEWRNEWREWNREWRNWEKRJHEWKRHEWREWREWREWREWRSDFDSREWRDXVCSDFSFSFDSFSDFSJDFK SLJFWEORJEWREJWRJEWREWRSMNFSBFDSDFSJDHFKSHFCFSNDFSDNFKSJFKEJWHRKEWHRSNFSDNFSDFNWJE','K','JRHWEH43H','5H32','5NFS','NDFSND','FSNDFK',n ull,null,'Interface',to_timestamp('13-JUN-13 08.59.05.051000000 PM','DD-MON-RR HH.MI.SS.FF AM'),null,null);
Insert into MST_PRDCT_SPCFCTN_CLR (RC,PRDCTN_FMLY_CD,RGN_GRP,EXTRR_CLR_CD,INTRR_CLR_CD,APP_DTL_CD,G2B_RVSN_NO,SIS_RVSN_NO,APPLCTN_TYP,SPCL_CLR_MRK,FIL1,ADPT_DT,ABLSH_D T,RLS_DT,UPDT_DT_TM,FIL2,LF,CRTD_BY,CRTD_DT,UPDTD_BY,UPDTD_DT) values ('CO','9261SD','AD','AASAD','AD','A','SD','SA','DADSAD234234SDFSFDSFDSFSXCVXVSDFLDFSDFLSFKSDFOSDFSDFSNFSMFSDNFSJFSDKFSDFNSMFBNFSREWBR WENRBSBFSFBSFBSFSDFDSFNBSDFMSDNBFSDFHWEBRWNEBRWMEWEREWREWNRMNEWRNEWREWNREWRNWEKRJHEWKRHEWREWREWREWREWRSDFDSREWRDXVCSDFSFSFDSFSDFSJDFK SLJFWEORJEWREJWRJEWREWRSMNFSBFDSDFSJDHFKSHFCFSNDFSDNFKSJFKEJWHRKEWHRSNFSDNFSDFNWJE','K','JRHWEH43H','5H32','5NFS','NDFSND','FSNDFK',n ull,null,'Interface',to_timestamp('13-JUN-13 08.59.59.477000000 PM','DD-MON-RR HH.MI.SS.FF AM'),null,null);
Insert into MST_PRDCT_SPCFCTN_CLR (RC,PRDCTN_FMLY_CD,RGN_GRP,EXTRR_CLR_CD,INTRR_CLR_CD,APP_DTL_CD,G2B_RVSN_NO,SIS_RVSN_NO,APPLCTN_TYP,SPCL_CLR_MRK,FIL1,ADPT_DT,ABLSH_D T,RLS_DT,UPDT_DT_TM,FIL2,LF,CRTD_BY,CRTD_DT,UPDTD_BY,UPDTD_DT) values ('CO','9261SD','AD','AASAD','AD','A','SD','SA','DADSAD234234SDFSFDSFDSFSXCVXVSDFLDFSDFLSFKSDFOSDFSDFSNFSMFSDNFSJFSDKFSDFNSMFBNFSREWBR WENRBSBFSFBSFBSFSDFDSFNBSDFMSDNBFSDFHWEBRWNEBRWMEWEREWREWNRMNEWRNEWREWNREWRNWEKRJHEWKRHEWREWREWREWREWRSDFDSREWRDXVCSDFSFSFDSFSDFSJDFK SLJFWEORJEWREJWRJEWREWRSMNFSBFDSDFSJDHFKSHFCFSNDFSDNFKSJFKEJWHRKEWHRSNFSDNFSDFNWJE','K','JRHWEH43H','5H32','5NFS','NDFSND','FSNDFK',n ull,null,'Interface',to_timestamp('14-JUN-13 09.59.20.031000000 AM','DD-MON-RR HH.MI.SS.FF AM'),null,null);
Insert into MST_PRDCT_SPCFCTN_CLR (RC,PRDCTN_FMLY_CD,RGN_GRP,EXTRR_CLR_CD,INTRR_CLR_CD,APP_DTL_CD,G2B_RVSN_NO,SIS_RVSN_NO,APPLCTN_TYP,SPCL_CLR_MRK,FIL1,ADPT_DT,ABLSH_D T,RLS_DT,UPDT_DT_TM,FIL2,LF,CRTD_BY,CRTD_DT,UPDTD_BY,UPDTD_DT) values ('CO','9261SD','AD','AASAD','AD','A','SD','SA','DADSAD234234SDFSFDSFDSFSXCVXVSDFLDFSDFLSFKSDFOSDFSDFSNFSMFSDNFSJFSDKFSDFNSMFBNFSREWBR WENRBSBFSFBSFBSFSDFDSFNBSDFMSDNBFSDFHWEBRWNEBRWMEWEREWREWNRMNEWRNEWREWNREWRNWEKRJHEWKRHEWREWREWREWREWRSDFDSREWRDXVCSDFSFSFDSFSDFSJDFK SLJFWEORJEWREJWRJEWREWRSMNFSBFDSDFSJDHFKSHFCFSNDFSDNFKSJFKEJWHRKEWHRSNFSDNFSDFNWJE','K','JRHWEH43H','5H32','5NFS','NDFSND','FSNDFK',n ull,null,'Interface',to_timestamp('14-JUN-13 06.03.14.319000000 PM','DD-MON-RR HH.MI.SS.FF AM'),null,null);
Insert into MST_PRDCT_SPCFCTN_CLR (RC,PRDCTN_FMLY_CD,RGN_GRP,EXTRR_CLR_CD,INTRR_CLR_CD,APP_DTL_CD,G2B_RVSN_NO,SIS_RVSN_NO,APPLCTN_TYP,SPCL_CLR_MRK,FIL1,ADPT_DT,ABLSH_D T,RLS_DT,UPDT_DT_TM,FIL2,LF,CRTD_BY,CRTD_DT,UPDTD_BY,UPDTD_DT) values ('CO','9261SD','AD','AAS','A','A','SD','SA','DADSAD234234SDFSFDSFDSFSXCVXVSDFLDFSDFLSFKSDFOSDFSDFSNFSMFSDNFSJFSDKFSDFNSMFBNFSREWBRWEN RBSBFSFBSFBSFSDFDSFNBSDFMSDNBFSDFHWEBRWNEBRWMEWEREWREWNRMNEWRNEWREWNREWRNWEKRJHEWKRHEWREWREWREWREWRSDFDSREWRDXVCSDFSFSFDSFSDFSJDFKSLJ FWEORJEWREJWRJEWREWRSMNFSBFDSDFSJDHFKSHFCFSNDFSDNFKSJFKEJWHRKEWHRSNFSDNFSDFNWJE','K','JRHWEH43H','5H32','5NFS','NDFSND','FSNDFK',null ,null,'Interface',to_timestamp('14-JUN-13 06.06.39.024000000 PM','DD-MON-RR HH.MI.SS.FF AM'),null,null);
Insert into MST_PRDCT_SPCFCTN_CLR (RC,PRDCTN_FMLY_CD,RGN_GRP,EXTRR_CLR_CD,INTRR_CLR_CD,APP_DTL_CD,G2B_RVSN_NO,SIS_RVSN_NO,APPLCTN_TYP,SPCL_CLR_MRK,FIL1,ADPT_DT,ABLSH_D T,RLS_DT,UPDT_DT_TM,FIL2,LF,CRTD_BY,CRTD_DT,UPDTD_BY,UPDTD_DT) values ('CO','9261SD','AD','AAS','A','A','SD','SA','DADSAD234234SDFSFDSFDSFSXCVXVSDFLDFSDFLSFKSDFOSDFSDFSNFSMFSDNFSJFSDKFSDFNSMFBNFSREWBRWEN RBSBFSFBSFBSFSDFDSFNBSDFMSDNBFSDFHWEBRWNEBRWMEWEREWREWNRMNEWRNEWREWNREWRNWEKRJHEWKRHEWREWREWREWREWRSDFDSREWRDXVCSDFSFSFDSFSDFSJDFKSLJ FWEORJEWREJWRJEWREWRSMNFSBFDSDFSJDHFKSHFCFSNDFSDNFKSJFKEJWHRKEWHRSNFSDNFSDFNWJE','K','JRHWEH43H','5H32','5NFS','NDFSND','FSNDFK',null ,null,'Interface',to_timestamp('14-JUN-13 06.14.42.097000000 PM','DD-MON-RR HH.MI.SS.FF AM'),null,null);
Insert into MST_PRDCT_SPCFCTN_CLR (RC,PRDCTN_FMLY_CD,RGN_GRP,EXTRR_CLR_CD,INTRR_CLR_CD,APP_DTL_CD,G2B_RVSN_NO,SIS_RVSN_NO,APPLCTN_TYP,SPCL_CLR_MRK,FIL1,ADPT_DT,ABLSH_D T,RLS_DT,UPDT_DT_TM,FIL2,LF,CRTD_BY,CRTD_DT,UPDTD_BY,UPDTD_DT) values ('CO','9261SD','AD','AAS','A','A','SD','SA','DADSAD234234SDFSFDSFDSFSXCVXVSDFLDFSDFLSFKSDFOSDFSDFSNFSMFSDNFSJFSDKFSDFNSMFBNFSREWBRWEN RBSBFSFBSFBSFSDFDSFNBSDFMSDNBFSDFHWEBRWNEBRWMEWEREWREWNRMNEWRNEWREWNREWRNWEKRJHEWKRHEWREWREWREWREWRSDFDSREWRDXVCSDFSFSFDSFSDFSJDFKSLJ FWEORJEWREJWRJEWREWRSMNFSBFDSDFSJDHFKSHFCFSNDFSDNFKSJFKEJWHRKEWHRSNFSDNFSDFNWJE','K','JRHWEH43H','5H32','5NFS','NDFSND','FSNDFK',null ,null,'Interface',to_timestamp('14-JUN-13 06.15.45.715000000 PM','DD-MON-RR HH.MI.SS.FF AM'),null,null);


Here i cant able to update PRDCTN_FMLY_CD.But morning it was working fine.

Report message to a moderator

Re: update issue [message #587395 is a reply to message #587394] Fri, 14 June 2013 09:27 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
After giving commit,oracle hangs.Thats all.

Report message to a moderator

Re: update issue [message #587398 is a reply to message #587395] Fri, 14 June 2013 09:34 Go to previous messageGo to next message
gazzag
Messages: 1119
Registered: November 2010
Location: Bedwas, UK
Senior Member
Copy & Paste everything you do. Use the [code] tags please. That post is unreadable.

Report message to a moderator

Re: update issue [message #587419 is a reply to message #587398] Fri, 14 June 2013 11:29 Go to previous messageGo to next message
Michel Cadot
Messages: 68767
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
... and keep your lines of code in 80 character width: no more than 80 characters on each line.

Regards
Michel

Report message to a moderator

Re: update issue [message #587427 is a reply to message #587394] Fri, 14 June 2013 12:31 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
siraj.accet@gmail.com wrote on Fri, 14 June 2013 10:26

Here i cant able to update PRDCTN_FMLY_CD.But morning it was working fine.


There are so many possibilities, but without more information, here is my most likely incorrect guess.

Your filesystem is full where log files reside and Oracle can not write out a new log file, so it hangs.

Report message to a moderator

Re: update issue [message #587493 is a reply to message #587395] Sun, 16 June 2013 06:23 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
Is your issue resolved? Or, are you gathering more information to post?

Report message to a moderator

Re: update issue [message #587537 is a reply to message #587493] Mon, 17 June 2013 00:08 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Hi all,
After restarting server,it is working fine.

Report message to a moderator

Re: update issue [message #587541 is a reply to message #587537] Mon, 17 June 2013 00:34 Go to previous messageGo to next message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
There could have been a lot of possibilities for this issue. Since you mentioned the issue happens only after COMMIT, it could be an issue with REDO buffers.

Now that you have bounced the DB, so it might have released the buffers which were not being flushed and thus no new entries were made to them. One probable reason!

Report message to a moderator

Re: update issue [message #587623 is a reply to message #587541] Mon, 17 June 2013 08:34 Go to previous messageGo to next message
oracle_doubt
Messages: 96
Registered: December 2012
Location: CHENNAI
Member
Hi all,

SELECT l.inst_id,SUBSTR(L.ORACLE_USERNAME,1,Cool ORA_USER, SUBSTR(L.SESSION_ID,1,3) SID,
S.serial#,
SUBSTR(O.OWNER||'.'||O.OBJECT_NAME,1,40) OBJECT, P.SPID OS_PID,
L.LOCKED_MODE
--,DECODE(L.LOCKED_MODE, 0,'NONE',1,'NULL',2,'ROW SHARE',3,'ROW EXCLUSIVE',4,'SHARE',5,'SHARE ROW EXCLUSIVE',6,'EXCLUSIVE',NULL) LOCK_MODE


FROM sys.GV_$LOCKED_OBJECT L, DBA_OBJECTS O, sys.GV_$SESSION S, sys.GV_$PROCESS P
WHERE L.OBJECT_ID = O.OBJECT_ID
and l.inst_id = s.inst_id
AND L.SESSION_ID = S.SID
and s.inst_id = p.inst_id
AND S.PADDR = P.ADDR(+)
order by l.inst_id ;

RESULT:

1	SCSK_DEV	146	7885	SCSK_DEV.MST_PRDCT_SPCFCTN_MDL	22024	3
1	OMS_BATC	152	19921	OMS_BATCH.MST_SPC_CLR	18494	3
1	SCSK_DEV	210	14955	SCSK_DEV.MST_PRDCT_SPCFCTN_MDL	12742	3
1	SCSK_DEV	96	3494	SCSK_DEV.MST_PRDCT_SPCFCTN_MDL	20183	3
1	SCSK_DEV	215	32761	SCSK_DEV.MST_PRMTR	18542	3
1	OMS_BATC	152	19921	OMS_BATCH.MST_CLR	18494	3
1	OMS_BATC	159	2298	OMS_BATCH.MST_SPC	17208	3
1	OMS_BATC	148	5812	OMS_BATCH.MST_SPC	16870	3
1	OMS_BATC	151	16638	OMS_BATCH.MST_SPC	13495	3
1	OMS_BATC	214	31346	OMS_BATCH.MST_SPC	14213	3
1	OMS_BATC	201	8016	OMS_BATCH.MST_SPC	12772	3
1	OMS_BATC	38	740	OMS_BATCH.MST_SPC	19456	3
1	OMS_BATC	32	4010	OMS_BATCH.MST_SPC	17602	3
1	OMS_BATC	6	315	OMS_BATCH.MST_SPC	16900	3
1	OMS_BATC	37	9632	OMS_BATCH.MST_SPC	19652	3
1	OMS_BATC	78	62979	OMS_BATCH.MST_SPC	19408	3
1	OMS_BATC	199	9882	OMS_BATCH.MST_SPC	16936	3
1	OMS_BATC	216	28577	OMS_BATCH.MST_SPC	13878	3
1	OMS_BATC	222	2549	OMS_BATCH.MST_SPC	11053	3
1	OMS_BATC	202	1646	OMS_BATCH.MST_SPC	17717	3
1	OMS_BATC	130	11971	OMS_BATCH.MST_SPC	14261	3
1	OMS_BATC	82	23097	OMS_BATCH.MST_SPC	13405	3
1	OMS_BATC	208	31129	OMS_BATCH.MST_SPC	12823	3
1	OMS_BATC	152	19921	OMS_BATCH.MST_SPC	18494	3
1	OMS_BATC	150	52072	OMS_BATCH.MST_SPC	17185	3
1	OMS_BATC	218	46153	OMS_BATCH.MST_SPC_CD	22406	3
1	OMS_BATC	159	2298	OMS_BATCH.MST_SPC_CD	17208	3
1	OMS_BATC	148	5812	OMS_BATCH.MST_SPC_CD	16870	3
1	OMS_BATC	151	16638	OMS_BATCH.MST_SPC_CD	13495	3
1	OMS_BATC	214	31346	OMS_BATCH.MST_SPC_CD	14213	3
1	OMS_BATC	201	8016	OMS_BATCH.MST_SPC_CD	12772	3
1	OMS_BATC	38	740	OMS_BATCH.MST_SPC_CD	19456	3
1	OMS_BATC	32	4010	OMS_BATCH.MST_SPC_CD	17602	3
1	OMS_BATC	6	315	OMS_BATCH.MST_SPC_CD	16900	3
1	OMS_BATC	37	9632	OMS_BATCH.MST_SPC_CD	19652	3
1	OMS_BATC	78	62979	OMS_BATCH.MST_SPC_CD	19408	3
1	OMS_BATC	199	9882	OMS_BATCH.MST_SPC_CD	16936	3
1	OMS_BATC	216	28577	OMS_BATCH.MST_SPC_CD	13878	3
1	OMS_BATC	222	2549	OMS_BATCH.MST_SPC_CD	11053	3
1	OMS_BATC	202	1646	OMS_BATCH.MST_SPC_CD	17717	3
1	OMS_BATC	130	11971	OMS_BATCH.MST_SPC_CD	14261	3
1	OMS_BATC	82	23097	OMS_BATCH.MST_SPC_CD	13405	3
1	OMS_BATC	208	31129	OMS_BATCH.MST_SPC_CD	12823	3
1	OMS_BATC	150	52072	OMS_BATCH.MST_SPC_CD	17185	3
1	SCSK_DEV	90	20525	SCSK_DEV.MST_PRDCT_SPCFCTN_MDL	20332	3


How to avoid EXCLUSIVE ROW LOCK.And when it will happens?

*BlackSwan added {code} tags. Do so yourself in the future.

[Updated on: Mon, 17 June 2013 08:36] by Moderator

Report message to a moderator

Re: update issue [message #587637 is a reply to message #587623] Mon, 17 June 2013 10:53 Go to previous message
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
Senior Member
siraj.accet@gmail.com wrote on Mon, 17 June 2013 19:04

How to avoid EXCLUSIVE ROW LOCK.And when it will happens?


In a ROW EXCLUSIVE LOCK on the table, you cannot do any DML transaction neither at table level nor at row level. Locks will be released only after a commit from the respective session.

Just try yourself opening 2 sqlplus sessions and use V$LOCK to understand when and how locks are generated at what level(table level share, row share or row exclusive lock). You will find numerous examples and explanations over Google.

Check this out by T.Kyte http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2247028425532

Report message to a moderator

Previous Topic: spooling xml column into one single row
Next Topic: enhancing performance of join
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Sep 01 19:54:54 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.