ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ORA-12840: 在并行/插入直接加载 txn 之后无法访问远程表

ORA-12840: 在并行/插入直接加载 txn 之后无法访问远程表

原创 Linux操作系统 作者:心飘 时间:2011-05-05 11:03:45 0 删除 编辑

在通过跨库同义词CAST导数据测试时,报ORA-12840: cannot access a remote table after parallel/insert direct load txn错误:

SQL> alter table dcustCREDITbalance_new nologging;

Table altered.

Elapsed: 00:00:00.09
SQL> insert /*+ append */ into dcustCREDITbalance_new select * from dcustCREDITbalance;    

1742678 rows created.

Elapsed: 00:00:19.12
SQL> /
insert /*+ append */ into dcustCREDITbalance_new select * from dcustCREDITbalance
                                                               *
ERROR at line 1:
ORA-12840: cannot access a remote table after parallel/insert direct load txn


Elapsed: 00:00:00.02

经查询,为并行/插入直接加载导致,以上为/*+ append */ 引起,解决办法:rollback或者commit

12840, 00000, "cannot access a remote table after parallel/insert direct load txn"
// *Cause: Within a transaction, an attempt was made to perform. distributed
// access after a PDML or insert direct  statement had been issued.
// *Action: Commit/rollback the PDML transaction first, and then perform.
// the distributed access, or perform. the distributed access before the
// first PDML statement in the transaction.

commit提交一下就正常了:

SQL> commit;

Commit complete.

Elapsed: 00:00:00.04


SQL> insert /*+ append */ into dcustCREDITbalance_new select * from dcustCREDITbalance
  2  ;

1742678 rows created.

Elapsed: 00:00:18.95

再次insert append,统计表中记录时又出现新的错误:ORA-12838: cannot read/modify an object after modifying it in parallel

SQL> select count(*) from dcustCREDITbalance_new;
select count(*) from dcustCREDITbalance_new
                     *
ERROR at line 1:
ORA-12838: cannot read/modify an object after modifying it in parallel


Elapsed: 00:00:00.01

12838, 00000, "cannot read/modify an object after modifying it in parallel"
// *Cause: Within the same transaction, an attempt was made to add read or
// modification statements on a table after it had been modified in parallel
// or with direct load. This is not permitted.
// *Action: Rewrite the transaction, or break it up into two transactions:
// one containing the initial modification and the second containing the
// parallel modification operation.

我直接再commit提交,恢复正常
SQL> commit;

Commit complete.

Elapsed: 00:00:00.02
SQL> select count(*) from dcustCREDITbalance_new;

  COUNT(*)
----------
  10456068

Elapsed: 00:00:09.50
SQL> insert /*+ append */ into dcustCREDITbalance_new select * from dcustCREDITbalance;

1742678 rows created.

Elapsed: 00:00:18.21

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/665930/viewspace-694486/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-29

  • 博文量
    29
  • 访问量
    119202