Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-12840cannot access a remote table after parallel/insert direct load txn

Vili DialisJun 24 2010 — edited Jun 24 2010
Hello,i am on 10.2.0.3.0 and i have problems when i try to use materialize hint.
If i go with this query :
WITH a AS
     (SELECT 
             ca.customer_id_high, ca.customer_id,
             NVL (ca.cscomptaxno, ca.passportno) jmbg
        FROM customer_all@vipbscs ca
       WHERE ca.cstype <> 'd'),
     b AS
     (SELECT 
             co.customer_id, co.co_id contract_id,
             cis.billing.cotomsisdn (co.co_id) msisdn
        FROM contract_all@vipbscs co),
     c AS
     (SELECT pah.cid
        FROM pa_head_agent pah, pa_teams t, pa_team_agents ta
       WHERE pah.agent_id = ta.agent_id
         AND t.ID = ta.team_id
         AND t.ID IN (21)
         AND ta.agent_id IN (10210))
SELECT     b.contract_id, b.msisdn, a.customer_id, a.jmbg, ka.nr ka_nr
      FROM a, b, ka_head ka
     WHERE a.customer_id = ka.cid(+) AND a.customer_id = b.customer_id(+)
CONNECT BY PRIOR a.customer_id = a.customer_id_high
START WITH a.customer_id IN (SELECT cid
                               FROM c)
It takes years to finish.

And if i go with this query, exeplain plan is showing let's say exceptable execution time estimation, but it fails with ORA-12840: cannot access a remote table after parallel/insert direct load txn :
WITH a AS
     (SELECT /*+ MATERIALIZE */
             ca.customer_id_high, ca.customer_id,
             NVL (ca.cscomptaxno, ca.passportno) jmbg
        FROM customer_all@vipbscs ca
       WHERE ca.cstype <> 'd'),
     b AS
     (SELECT /*+ MATERIALIZE */
             co.customer_id, co.co_id contract_id,
             cis.billing.cotomsisdn (co.co_id) msisdn
        FROM contract_all@vipbscs co),
     c AS
     (SELECT pah.cid
        FROM pa_head_agent pah, pa_teams t, pa_team_agents ta
       WHERE pah.agent_id = ta.agent_id
         AND t.ID = ta.team_id
         AND t.ID IN (21)
         AND ta.agent_id IN (10210))
SELECT     b.contract_id, b.msisdn, a.customer_id, a.jmbg, ka.nr ka_nr
      FROM a, b, ka_head ka
     WHERE a.customer_id = ka.cid(+) AND a.customer_id = b.customer_id(+)
CONNECT BY PRIOR a.customer_id = a.customer_id_high
START WITH a.customer_id IN (SELECT cid
                               FROM c)
Can you suggest something i could do optimize this query ?
I can post explan plan if you thing it could help.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jul 22 2010
Added on Jun 24 2010
4 comments
1,320 views