An update job is not able to update the table

[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

[sachin1]

please let us know the abort error message..............

[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.

[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

[chulett]

Still, deletes without an index equals a full table scan per request. As requested - what is the abort when it does finally fail?

[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.

[chulett]

No kidding. What happens if you do not delete the indexes? How many deletes are we talking about here?

[Vivek Vazarkar]

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.

[Vivek Vazarkar]

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.