Re: Urgent: ORA-02067 transaction or savepoint rollback required (Distributed transaction)

Charu,

I guess the error you are getting is "Table or Trigger is mutating". and perhaps you are using an after insert row trigger. This is a common and expected problem. There are ways to get around it. You have not mentioned the version of Oracle; I would assume 8.1.7 or later.

Inside an after insert row trigger, you can not operate on the table on which trigger is defined. for instance in your case the trigger (say, TRA_LT) on the table LT can't insert into LT inside it. However if you define the same in a BEFORE INSERT row trigger, it's allowed.

Caution, though, the insert through the trigger will fire the BEFORE INSERT trigger too, which in turn will insert a row in LT and then the trigger fires again..a vicious cycle. Fortuanately Oracle breaks the cascading trigger after 17 atetmpts. So, you need to have a limiting condition while defining the trigger WHEN (NEW.STATUS='OLD') or similar to make sure you can differentiate between the trigger inserted and the user inserted rows.

Finally, if you insert into the table LT using a construct like INSERT INTO LT VALUES (...) it works; but if you use INSERT INTO LT SELECT .. FROM ANOTHER_TABLE, it does not. this is not documented anywhere; I found this by accident.

Hope this helps.

Arup Nanda
www.proligence.com

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, November 21, 2002 9:18 AM (Distributed transaction)

> Gurus,
>
> Desperately need some ideas to solve this one.
>
> Following is the sequence of events as it happens:
>
> 1. Front-end application (VC++) queries from a view V. V fetches data from
a
> remote database RD via a database link.
> 2. Front-end inserts a row in a local table LT.
> 3. The Insert trigger on this table queries view V to take a final stock.
> 4a. If conditions match, the trigger inserts in remote table RT on RD. NO
> PROBLEM in condition.
> 4b. If conditions don't match, the front-end application tries to insert
> another row in LT. The trigger starts again and tries to insert row in RT.
> At this point we SEEM to get the error.
>
> We can't get anybody to debug the front-end application. We tried setting
> SQL_TRACE TRUE, and the only thing made clear was that the trigger didn't
go
> beyond trying to insert into RT.
>
> We tried replicating the whole scenario by executing each and every
> statement that the front-end fires through SQL*Plus as a script, and it
> doesn't give any error.
>
> I realize that I have given only a fraction of the whole information, but
> what is the best way to debug this situation?
>
> Are there any do's don'ts in distributed transactions which our code may
not
> be following?
>
> Thanks in advance,
> Charu
>
> -----Original Message-----
> Waleed
> Sent: Wednesday, November 20, 2002 11:33 PM
> To: Multiple recipients of list ORACLE-L
>
> EMC has now hardware striping. The smallest stripe size is one track
(1MB).
>
> Regards,
>
> Waleed
>
> -----Original Message-----
> Sent: Wednesday, November 20, 2002 5:54 PM
> To: Multiple recipients of list ORACLE-L
>
> A number of papers recommend a stripe size of 1 M (even for EMC) for
volumes
> containing data files. I also have the following email from Eyal Aronoff
of
> Quest dated Nov 2000. A number of the white papers are more recent.
>
> ============================================================
> The reasons for a larger stripe size on a non-RAID 5 device are:
> 1) Sequential reads are faster if you can take advantege of the read ahead
> built into the disk caching
> 2) If a 64K read does not start on the first block of the stripe, two
> "spindled" are locked for the duration of the read
>
> However, lately we have been testing some EMC gear and it looks like EMC
> have optimized both of those for smaller strip size too.
>
> The bottom line - I no longer have an opinion one way or another. The
> undelying technology just changes too rapidly.
>
> Eyal
> ============================================================
>
> Your opinions/comments as far as a "best" practice in setting stripe sizes
> would be greatly appreciated.
>
> Thanks,
> Ethan
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Post, Ethan
> INET: Ethan.Post_at_ps.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Khedr, Waleed
> INET: Waleed.Khedr_at_FMR.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>
> *********************************************************
> Disclaimer
>
> This message (including any attachments) contains
> confidential information intended for a specific
> individual and purpose, and is protected by law.
> If you are not the intended recipient, you should
> delete this message and are hereby notified that
> any disclosure, copying, or distribution of this
> message, or the taking of any action based on it,
> is strictly prohibited.
>
> *********************************************************
> Visit us at http://www.mahindrabt.com
>
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Charu Joshi
> INET: joshic_at_mahindrabt.com
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Arup Nanda
  INET: arupnanda_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).