| Transaction Hange [message #400580] |
Tue, 28 April 2009 23:38  |
snsiddiqui
Messages: 172
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 #400795 is a reply to message #400580] |
Wed, 29 April 2009 11:19  |
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
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
