4 Replies Latest reply: Nov 16, 2012 11:58 AM by jgarry RSS

    ISOLATION LEVEL: via dblink

    678833
      Hi all,

      I'm in a very troublesome situation. I'am trying every dirty solutions but nothing...

      I need a suggestion to solve a problem related to a typical "reading from dblink" situation.

      I have a very simple INSERT that reads from remote tables, via dblink.
      INSERT INTO t1
      SELECT f1, f2, .. FROM remote_table@dblink x1 
      INNER JOIN remote_table@dblink x2 ON (x1.f1 = x2.f1);
      
      COMMIT;
      Sometime this query never ends (WAIT for something). I must kill it.
      I suppose that other processes make insert in "remote_table" during my job execution (I'm not sure, I can't verify !).

      Anyway is possible to get all committed records ? I think that is the default mode, but...

      Do you know how could be set the isolation level to try other ways ?
      READ UNCOMMITTED ??

      Thanks in advance.
        • 1. Re: ISOLATION LEVEL: via dblink
          sb92075
          you are trying to pull the data to destination DB.
          Can you change things so that you are pushing the data to the destination DB?

          INSERT INTO T1@REMOTE
          SELECT f1, f2, .. FROM LOCAL_table x1
          INNER JOIN LOCAL_table x2 ON (x1.f1 = x2.f1);
          • 2. Re: ISOLATION LEVEL: via dblink
            678833
            No, I can't.
            • 3. Re: ISOLATION LEVEL: via dblink
              rp0428
              >
              Do you know how could be set the isolation level to try other ways ?
              READ UNCOMMITTED ??
              >
              Oracle doesn't support READ UNCOMMITTED. Even if it did why would you want to pull data that might get rolled back after you pull it?

              You can't fix a problem until you know what is causing the problem. Guessing doesn't work.

              Mark this question ANSWERED and repost it in the SQL and PL/SQL forum
              PL/SQL

              Before you post read FAQ #3 in the *** SQL and PL/SQL Frequenty Asked Questions (FAQ) *** at the top of the forum thread list
              3. How can improve the performance of my query? / My query is running slow.
              • 4. Re: ISOLATION LEVEL: via dblink
                jgarry
                Also be sure and post your version/patch level.

                Search MOS bug database for ansi join dblink. Try rewriting your ansi join in the old oracle syntax, and look up the driving site hint in the docs to see if you can push some of the work there, rather than sucking all the data over then joining.

                Also see http://jonathanlewis.wordpress.com/2008/12/05/distributed-dml/