在通过跨库同义词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