Re: Help : ORA-08177 during index update

Hi,

Did you try to increase your rollback segment?

Andrew Protasov

> 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
>
>
>
>
>
Received on Tue Nov 24 1998 - 02:26:54 CST