Home » RDBMS Server » Server Administration » Transaction Hange (Oracle10g, Forms6i, Win XP)
Transaction Hange [message #400580] Tue, 28 April 2009 23:38 Go to next message
snsiddiqui
Messages: 168
Registered: December 2008
Senior Member
Dear All

I have a table which contains every transaction entry, problem that I am facing, end user transaction goes hang if I am doing some changes in data (due to some requirement) via SQL.

One thing I must clear, end user and I both are working on different data but the table is same.



TABLE STRUCTURE
---------------

CREATE TABLE ITEM_TRANS_DETL
(
TID_ID NUMBER,
TID_CC_COMPC VARCHAR2(2),
TID_CB_BRANC VARCHAR2(3),
TID_F_CD_DCODE VARCHAR2(3),
TID_F_CDDESCR VARCHAR2(100),
TID_F_CG_GODWNC VARCHAR2(3),
TID_F_CGDESCR VARCHAR2(100),
TID_DOC_TYPE VARCHAR2(10),
TID_T_CD_DCODE VARCHAR2(3),
TID_T_DESCR VARCHAR2(100),
TID_T_CG_GODWNC VARCHAR2(3),
TID_T_CGDESCR VARCHAR2(100),
TID_TRANS# VARCHAR2(10),
TID_TRN_DT DATE,
TID_CIM_ICODE VARCHAR2(10),
TID_DESCR VARCHAR2(100),
TID_CUOM_UCODE VARCHAR2(3),
TID_QTY NUMBER,
TID_RATE NUMBER,
TID_AMT NUMBER
TID_CFY_CODE VARCHAR2(9),
)
tablespace TBS_MTX
pctfree 10
pctused 40
initrans 1
maxtrans 255
storage
(
initial 12K
next 12K
minextents 1
maxextents 249
pctincrease 50
);

alter table ITEM_TRANS_DETL
add CONSTRAINT ITEM_TRANS_DETL_TIDID_PK primary key (TID_ID)
using index
tablespace TBS_MTX
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 24K
next 12K
minextents 1
maxextents 249
pctincrease 50
);

ALTER TABLE ITEM_TRANS_DETL
ADD CONSTRAINT ITEM_TRANS_DETL_TIDCCCOMPC_FK FOREIGN KEY (TID_CC_COMPC)
REFERENCES COMM_COMPANY (CC_COMPC);

ALTER TABLE ITEM_TRANS_DETL
ADD CONSTRAINT ITEM_TRANS_DETL_TIDCBBRANC_FK FOREIGN KEY (TID_CB_BRANC)
REFERENCES COMM_BRANCH (CB_BRANC);

ALTER TABLE ITEM_TRANS_DETL
ADD CONSTRAINT ITEM_TRANS_DETL_TIDFCDDCODE_FK FOREIGN KEY (TID_F_CD_DCODE)
REFERENCES COMM_DEPT (CD_DCODE);

ALTER TABLE ITEM_TRANS_DETL
ADD CONSTRAINT ITEM_TRANS_DETL_TIDCUOMUCODEFK FOREIGN KEY (TID_CUOM_UCODE)
REFERENCES COMM_UOM (CUOM_UCODE);

ALTER TABLE ITEM_TRANS_DETL
ADD CONSTRAINT ITEM_TRANS_DETL_TIDFCGGODWNCFK FOREIGN KEY (TID_F_CG_GODWNC)
REFERENCES COMM_GODOWNS (CG_GODWNC);

ALTER TABLE ITEM_TRANS_DETL
ADD CONSTRAINT ITEM_TRANS_DETL_TIDTCGGODWNCFK FOREIGN KEY (TID_T_CG_GODWNC)
REFERENCES COMM_GODOWNS (CG_GODWNC);

ALTER TABLE ITEM_TRANS_DETL
ADD CONSTRAINT ITEM_TRANS_DETL_TIDCFYCODE_FK FOREIGN KEY (TID_CFY_CODE)
REFERENCES COMM_FISCAL_YEAR (CFY_CODE);



Re: Transaction Hange [message #400596 is a reply to message #400580] Wed, 29 April 2009 00:49 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Query v$lock, v$session_wait, activate trace on waiting session these are ways to know why it is waiting.

Regards
Michel
Re: Transaction Hange [message #400795 is a reply to message #400580] Wed, 29 April 2009 11:19 Go to previous message
gkrishn
Messages: 506
Registered: December 2005
Location: Putty a dark screen
Senior Member
mostly the symptom of a blocking session.
use below script to get block details.

SELECT s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ','||s1.Serial#
||' )  is blocking ' || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid
||','||s2.Serial#||' ) ' AS blocking_status
FROM v$lock l1, v$session s1, v$lock l2, v$session s2 
 WHERE s1.sid = l1.sid 
AND s2.sid = l2.sid 
 AND l1.BLOCK = 1 
AND l2.request > 0 
  AND l1.id1 = l2.id1 
  AND l2.id2 = l2.id2
/

Previous Topic: Variable size memory
Next Topic: XDB not working properly after Oracle Upgrade
Goto Forum:
  


Current Time: Wed Dec 07 18:28:36 CST 2016

Total time taken to generate the page: 0.07464 seconds