Error while updating index in serializable transaction ORA-08177

From: <pgaudin_at_my-dejanews.com>
Date: Thu, 12 Nov 1998 10:38:34 GMT
Message-ID: <72edra$iei$1@nnrp1.dejanews.com>

Hi everybody !

I hope someone will be able to help me because Oracle support could'nt...

Here is my problem.

I have created triggers saving all modifications on a set of tables in tables having the structure :

TABLE1

---

ACTION_TIMESTAMP,
ACTION_CODE,  (I for insert, U for update, D for delete)
ACTION_SEQ_NR,

ID,
data_fields

These tables are used to update tables ODS having the structure :

TABLE2

---

DWHOUT_SEQ_NR,
FROM_DATE,
TO_DATE,
ACTION_CODE,
CURRENT_DATA, (Y or N)
TO_ODS_JOB_ID,
TO_STAR_JOB_ID,
ID,
data_fields

The update of TABLE2 is done in a SERIALIZABLE transaction with the following pseudocode :

      if TABLE1.Action_Code = 'D' then

        update TABLE2 set To_Date = TABLE1.Action_Timestamp
        where Current_Data = 'Y' and TABLE1.ID=TABLE2.ADDR_ID;

      elsif TABLE1.ACTION_CODE = 'U' then

        update ODS.ADDR set TO_DATE = TABLE1.ACTION_TIMESTAMP, CURRENT_DATA
= 'N'
        where CURRENT_DATA = 'Y' and ADDR_ID=TABLE1.ADDR_ID;

        insert into TABLE2 (DWHOUT_SEQ_NR,FROM_DATE,TO_DATE,

ACTION_CODE,CURRENT_DATA,TO_ODS_JOB_ID,TO_STAR_JOB_ID,ID,data_fields)
        values (TABLE1.ACTION_SEQ_NR,TABLE1.ACTION_TIMESTAMP,NULL,
        TABLE1.ACTION_CODE,'Y',P_Job_ID,NULL,TABLE1.ADDR_ID,data_fields);

      elsif TABLE1.ACTION_CODE = 'I' then

        insert into TABLE2 (DWHOUT_SEQ_NR,FROM_DATE,TO_DATE,

ACTION_CODE,CURRENT_DATA,TO_ODS_JOB_ID,TO_STAR_JOB_ID,ID,data_fields)
        values(TABLE1.ACTION_SEQ_NR,TABLE1.ACTION_TIMESTAMP,NULL,
        TABLE1.ACTION_CODE,'Y',P_Job_ID,NULL,TABLE1.ID,data_fields);

      end if;

This pseudo-code has been executed as the only user on the database.

This sytemactically produces an error "ORA-08177: can't serialize access for this transaction" when index(es) is defined on TABLE2.

I have tried to increase both PCT_FREE and INI_TRANS parameters on the index(es).
I have also tried to set delayed_logging_block_cleanouts=false

Any other ideas ?

Thanks very much in advance !

PS : could you cc your answers directly to my e-mail... thanks

--

Philippe Gaudin ( PGaudin_at_isaserver.be ) N.V. IsaServer S.A
B - 1000 Brussels
Belgium

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Nov 12 1998 - 04:38:34 CST