Page 1 of 1
An update job is not able to update the table
Posted: Thu Sep 04, 2008 9:32 am
by mini
We have a server job which tries to insert/update records in the table.
The logic goes this way:before the job the indexess on the table are dropped and then insert/update is performed on the table and the indexes are then rebuild on the table.
But since a couple of weeks the server job which insert/updates the table is not able to update the table.It keeps on runnning for days and later aborts.
I talked to dba but he says the table doesnot have any problem.
Could you help me on this
Re: An update job is not able to update the table
Posted: Thu Sep 04, 2008 9:35 am
by sachin1
please let us know the abort error message..............
Posted: Thu Sep 04, 2008 9:44 am
by chulett
Define what you mean by "insert/update" - two separate links or a combo Update Action?
ps. Updates don't work so good without an index to support them, you know.
Posted: Thu Sep 04, 2008 10:02 am
by mini
I am sorry its my mistake.
The job is actually deleting records and not updation or inserting.
The deletion of records is taking time
Posted: Thu Sep 04, 2008 10:19 am
by chulett
Still, deletes without an index equals a full table scan per request. As requested - what is the abort when it does finally fail?
Posted: Thu Sep 04, 2008 12:30 pm
by mini
The error in log is
DelPendConsUsg..DCX_PENDING_CONSOLIDATED_USAGE: DELETE FROM DCX_PENDING_CONSOLIDATED_USAGE WHERE OBJ_ID = :1
DelPendConsUsg..S_ROT_CLASS: DELETE FROM S_ROT_CLASS WHERE OBJ_ID = :1
DelPendConsUsg..xmfPendDelUsg: The value of the row is: OBJ_ID =
DelPendConsUsg..xmfPendDelUsg: ORA-03113: end-of-file on communication channel
DelPendConsUsg..xmfPendDelUsg: DBMS.CODE=ORA-03113
DelPendConsUsg..xmfPendDelUsg: The value of the row is: OBJ_ID =
DelPendConsUsg..xmfPendDelUsg: ORA-03114: not connected to ORACLE
DelPendConsUsg..xmfPendDelUsg: DBMS.CODE=ORA-03114
The connection gets timed out as the job was running since 4 days.
Posted: Thu Sep 04, 2008 1:01 pm
by chulett
No kidding. What happens if you do not delete the indexes? How many deletes are we talking about here?
Posted: Fri Sep 05, 2008 1:18 am
by Vivek Vazarkar
mini wrote:The error in log is
DelPendConsUsg..DCX_PENDING_CONSOLIDATED_USAGE: DELETE FROM DCX_PENDING_CONSOLIDATED_USAGE WHERE OBJ_ID = :1
DelPendConsUsg..S_ROT_CLASS: DELETE FROM S_ROT_CLASS WHERE OBJ_ID = :1
DelPendConsUsg..xmfPendDelUsg: The value of the row is: OBJ_ID =
DelPendConsUsg..xmfPendDelUsg: ORA-03113: end-of-file on communication channel
DelPendConsUsg..xmfPendDelUsg: DBMS.CODE=ORA-03113
DelPendConsUsg..xmfPendDelUsg: The value of the row is: OBJ_ID =
DelPendConsUsg..xmfPendDelUsg: ORA-03114: not connected to ORACLE
DelPendConsUsg..xmfPendDelUsg: DBMS.CODE=ORA-03114
The connection gets timed out as the job was running since 4 days.
Try to Create Unique index on OBJ_ID and before deleting record try to Analyze the table and index and then try to delete the record. If the OBJ_ID column having duplicate rows then try Bitmap index on it.
Posted: Fri Sep 05, 2008 1:19 am
by Vivek Vazarkar
mini wrote:The error in log is
DelPendConsUsg..DCX_PENDING_CONSOLIDATED_USAGE: DELETE FROM DCX_PENDING_CONSOLIDATED_USAGE WHERE OBJ_ID = :1
DelPendConsUsg..S_ROT_CLASS: DELETE FROM S_ROT_CLASS WHERE OBJ_ID = :1
DelPendConsUsg..xmfPendDelUsg: The value of the row is: OBJ_ID =
DelPendConsUsg..xmfPendDelUsg: ORA-03113: end-of-file on communication channel
DelPendConsUsg..xmfPendDelUsg: DBMS.CODE=ORA-03113
DelPendConsUsg..xmfPendDelUsg: The value of the row is: OBJ_ID =
DelPendConsUsg..xmfPendDelUsg: ORA-03114: not connected to ORACLE
DelPendConsUsg..xmfPendDelUsg: DBMS.CODE=ORA-03114
The connection gets timed out as the job was running since 4 days.
Try to Create Unique index on OBJ_ID and before deleting record try to Analyze the table and index and then try to delete the record. If the OBJ_ID column having duplicate rows then try Bitmap index on it.