FAQ
Listers,

We have two stored procedures that are suddenly
throwing random "distributed operation not supported"
errors.

These pieces of code are doing an "insert into as
select" across a database link. The data is being
pushed, not pulled. They have been running for months
in production without a problem. Suddenly, they start
throwing these errors, but will often succeed on the
retry.

Anyone have a similiar problem?

thx,

jack

Do You Yahoo!?
Yahoo! - Official partner of 2002 FIFA World Cup
http://fifaworldcup.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack Silvey
INET: jack_silvey_at_yahoo.com

Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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

Search Discussions

  • Michael P Sale at Jun 21, 2002 at 7:43 pm
    Without seeing more information, I have seen this type of a response
    where either the instance is not registered with the listener, or the db
    is not accepting connections for a variety of reasons (e.g. no more
    memory all the way to the db is shut down).

    Regards,

    Michael Sale
    Author: Oracle9i for Windows(R) 2000 Tips & Techniques
    http://www.amazon.com/exec/obidos/ASIN/0072194626

    -----Original Message-----
    Silvey
    Sent: Friday, June 21, 2002 12:08 PM
    To: Multiple recipients of list ORACLE-L

    Listers,

    We have two stored procedures that are suddenly
    throwing random "distributed operation not supported"
    errors.

    These pieces of code are doing an "insert into as
    select" across a database link. The data is being
    pushed, not pulled. They have been running for months
    in production without a problem. Suddenly, they start
    throwing these errors, but will often succeed on the
    retry.

    Anyone have a similiar problem?

    thx,

    jack

    Do You Yahoo!?
    Yahoo! - Official partner of 2002 FIFA World Cup
    http://fifaworldcup.yahoo.com
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack Silvey
    INET: jack_silvey_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    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: Michael P Sale
    INET: Michael.Sale_at_oracle.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------

    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).
  • Jack Silvey at Jun 21, 2002 at 8:28 pm
    Oracle support is saying much the same thing. They are
    advising an upgrade to 8.1.7.4 (from 8.1.7.2). Doesn't
    give us the reason WHY it is happening, but it might
    fix it. We have run 10053 and 10046 traces and can see
    no obvious issues.

    Michael P Sale wrote:
    Without seeing more information, I have seen this
    type of a response
    where either the instance is not registered with the
    listener, or the db
    is not accepting connections for a variety of
    reasons (e.g. no more
    memory all the way to the db is shut down).

    Regards,

    Michael Sale
    Author: Oracle9i for Windows(R) 2000 Tips &
    Techniques
    http://www.amazon.com/exec/obidos/ASIN/0072194626


    -----Original Message-----
    Silvey
    Sent: Friday, June 21, 2002 12:08 PM
    To: Multiple recipients of list ORACLE-L


    Listers,

    We have two stored procedures that are suddenly
    throwing random "distributed operation not
    supported"
    errors.

    These pieces of code are doing an "insert into as
    select" across a database link. The data is being
    pushed, not pulled. They have been running for
    months
    in production without a problem. Suddenly, they
    start
    throwing these errors, but will often succeed on the
    retry.

    Anyone have a similiar problem?

    thx,

    jack

    __________________________________________________
    Do You Yahoo!?
    Yahoo! - Official partner of 2002 FIFA World Cup
    http://fifaworldcup.yahoo.com
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: Jack Silvey
    INET: jack_silvey_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX:
    (858) 538-5051
    San Diego, California -- Public Internet
    access / Mailing Lists
    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: Michael P Sale
    INET: Michael.Sale_at_oracle.com

    Fat City Network Services -- (858) 538-5051 FAX:
    (858) 538-5051
    San Diego, California -- Public Internet
    access / Mailing Lists
    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).
    Do You Yahoo!?
    Yahoo! - Official partner of 2002 FIFA World Cup
    http://fifaworldcup.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack Silvey
    INET: jack_silvey_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).
  • Michael P Sale at Jun 21, 2002 at 11:13 pm
    Ahh yes, the elusive "Why". If you are already working with support then
    I would set an errorstack on the ora-2064 error number you are getting(I
    suspect that is what it is, you never say). This will give them
    extensive information as to the state of the call at the time of the
    error.

    If you are running a high enough version of the db this statement will
    work:
    alter system set events '2064 trace name errorstack level 3';

    Otherwise you can set it in the init.ora:
    event = "2064 trace name errorstack level 3"
    Where 2064 is the error number you are getting.

    You can also set it at the session level with the "alter session syntax"
    and on another session with oradebug. This is obviously the much more
    painful route.

    The 10053 would only be relevant if you were running into a CBO issue,
    not likely unless you are running with certain events and/or underscore
    parameters in place, and the 10046 should show the sql being run (if
    taken at the proper level) but not why the error message, only that this
    particular sql was run and resulted in this error.

    An upgrade to address this problem directly might not solve it
    **UNLESS** you are running into bug 2140287 "CREATE TABLE AS SELECT WITH
    DECODE CORRUPTING DATA" (see the 8.1.7.4 patchset readme or metalink
    note 120613.1). In which case you could possibly have serious issues
    that would require you move to this patchset!!!

    Either way, the errorstack will give you the "why" even though only
    Oracle support or development would likely be able to interpret this for
    you.

    Regards,

    Michael Sale
    Author: Oracle9i for Windows(R) 2000 Tips & Techniques
    http://www.amazon.com/exec/obidos/ASIN/0072194626

    -----Original Message-----
    Silvey
    Sent: Friday, June 21, 2002 2:28 PM
    To: Multiple recipients of list ORACLE-L

    Oracle support is saying much the same thing. They are
    advising an upgrade to 8.1.7.4 (from 8.1.7.2). Doesn't
    give us the reason WHY it is happening, but it might
    fix it. We have run 10053 and 10046 traces and can see
    no obvious issues.

    Michael P Sale wrote:
    Without seeing more information, I have seen this
    type of a response
    where either the instance is not registered with the listener, or the
    db is not accepting connections for a variety of
    reasons (e.g. no more
    memory all the way to the db is shut down).

    Regards,

    Michael Sale
    Author: Oracle9i for Windows(R) 2000 Tips &
    Techniques
    http://www.amazon.com/exec/obidos/ASIN/0072194626


    -----Original Message-----
    Silvey
    Sent: Friday, June 21, 2002 12:08 PM
    To: Multiple recipients of list ORACLE-L


    Listers,

    We have two stored procedures that are suddenly
    throwing random "distributed operation not
    supported"
    errors.

    These pieces of code are doing an "insert into as
    select" across a database link. The data is being
    pushed, not pulled. They have been running for
    months
    in production without a problem. Suddenly, they
    start
    throwing these errors, but will often succeed on the
    retry.

    Anyone have a similiar problem?

    thx,

    jack
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Michael P Sale
    INET: Michael.Sale_at_oracle.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).
  • Jack Silvey at Jun 22, 2002 at 5:16 pm
    Thanks for the input, Michael.

    You are correct in your error number assumption. It is
    the 2064 error.

    We did set the 2064 event in a logon trigger and last
    week and got a trace, not much help there either. I
    tried to get the analyst to choose between oradebug
    and dbms_system.set_ev so that I could set it
    independently of the session, but we finally just put
    a good old alter session in our load script.

    We ran a 10053 trace since the problem seems that it
    might be some sort of parse error on the source side.

    We wanted the 10046 trace to see exactly where the
    error was occuring since it is in a stored procedure
    and what it was waiting on.

    In working the theory that it might be
    object/stats/parse related I rebuilt the tables and
    recreated the indexes, no effect.

    The strange thing is that this error just started
    showing up two weeks ago, in two stored procedures
    (both doing the same thing, different tables) having
    the exact same frequency, scope, and duration of
    error. There must be a connection, but unfortunately
    this is a very hot problem for us and I don't have the
    window to figure it out. It is too bad, it is a very
    good puzzle. Almost hate to solve it with an upgrade.

    The main reason that I am upgrading is that Oracle
    support has tentatively identified a possible bug
    (although it is internal, so no details) that will be
    fixed by 8.1.7.4. Their position is that unless I
    upgrade they won't pursue this further. So, upgrading
    I am, even though I do not feel that they have really
    nailed the problem, but they won't give further
    support. Reasonable on their part, since they might
    find that we have a bug that is already solved.

    jack

    Michael P Sale wrote:
    Ahh yes, the elusive "Why". If you are already
    working with support then
    I would set an errorstack on the ora-2064 error
    number you are getting(I
    suspect that is what it is, you never say). This
    will give them
    extensive information as to the state of the call at
    the time of the
    error.

    If you are running a high enough version of the db
    this statement will
    work:
    alter system set events '2064 trace name errorstack
    level 3';

    Otherwise you can set it in the init.ora:
    event = "2064 trace name errorstack level 3"
    Where 2064 is the error number you are getting.

    You can also set it at the session level with the
    "alter session syntax"
    and on another session with oradebug. This is
    obviously the much more
    painful route.

    The 10053 would only be relevant if you were running
    into a CBO issue,
    not likely unless you are running with certain
    events and/or underscore
    parameters in place, and the 10046 should show the
    sql being run (if
    taken at the proper level) but not why the error
    message, only that this
    particular sql was run and resulted in this error.

    An upgrade to address this problem directly might
    not solve it
    **UNLESS** you are running into bug 2140287 "CREATE
    TABLE AS SELECT WITH
    DECODE CORRUPTING DATA" (see the 8.1.7.4 patchset
    readme or metalink
    note 120613.1). In which case you could possibly
    have serious issues
    that would require you move to this patchset!!!

    Either way, the errorstack will give you the "why"
    even though only
    Oracle support or development would likely be able
    to interpret this for
    you.


    Regards,

    Michael Sale
    Author: Oracle9i for Windows(R) 2000 Tips &
    Techniques
    http://www.amazon.com/exec/obidos/ASIN/0072194626


    -----Original Message-----
    Silvey
    Sent: Friday, June 21, 2002 2:28 PM
    To: Multiple recipients of list ORACLE-L


    Oracle support is saying much the same thing. They
    are
    advising an upgrade to 8.1.7.4 (from 8.1.7.2).
    Doesn't
    give us the reason WHY it is happening, but it might
    fix it. We have run 10053 and 10046 traces and can
    see
    no obvious issues.




    --- Michael P Sale wrote:
    Without seeing more information, I have seen this
    type of a response
    where either the instance is not registered with
    the listener, or the
    db is not accepting connections for a variety of
    reasons (e.g. no more
    memory all the way to the db is shut down).

    Regards,

    Michael Sale
    Author: Oracle9i for Windows(R) 2000 Tips &
    Techniques
    http://www.amazon.com/exec/obidos/ASIN/0072194626


    -----Original Message-----
    Silvey
    Sent: Friday, June 21, 2002 12:08 PM
    To: Multiple recipients of list ORACLE-L


    Listers,

    We have two stored procedures that are suddenly
    throwing random "distributed operation not
    supported"
    errors.

    These pieces of code are doing an "insert into as
    select" across a database link. The data is being
    pushed, not pulled. They have been running for
    months
    in production without a problem. Suddenly, they
    start
    throwing these errors, but will often succeed on the
    retry.

    Anyone have a similiar problem?

    thx,

    jack
    --
    Please see the official ORACLE-L FAQ:
    http://www.orafaq.com
    --
    Author: Michael P Sale
    INET: Michael.Sale_at_oracle.com

    Fat City Network Services -- (858) 538-5051 FAX:
    (858) 538-5051
    San Diego, California -- Public Internet
    access / Mailing Lists
    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).
    Do You Yahoo!?
    Yahoo! - Official partner of 2002 FIFA World Cup
    http://fifaworldcup.yahoo.com

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Jack Silvey
    INET: jack_silvey_at_yahoo.com

    Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
    San Diego, California -- Public Internet access / Mailing Lists
    --------------------------------------------------------------------
    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).

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedJun 21, '02 at 6:08p
activeJun 22, '02 at 5:16p
posts5
users2
websiteoracle.com

2 users in discussion

Jack Silvey: 3 posts Michael P Sale: 2 posts

People

Translate

site design / logo © 2022 Grokbase