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

在通过跨库同义词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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/665930/viewspace-694486/