倾情大奉献--ORACLE+UNIX易错问题集锦

xzh2000

嗯，不错，这是本年度最有价值的分享。。。

xsmdel

确实用心写作的。刚刚看了几页。不错。总结的不错。

xsmdel

但是有个疑问刚刚看到
ORA-12841: 无法改变事务处理中的会话并行 DML 状态
好像没有解释清楚。哈哈

xsmdel

SQL> create table parallel_test as select * from dba_objects;

Table created

SQL> select table_name, degree from user_tables where table_name = upper('parallel_test');

TABLE_NAME                     DEGREE
------------------------------ --------------------
PARALLEL_TEST                           1

SQL> insert into parallel_test  select * from dba_objects;

49875 rows inserted

SQL> insert into parallel_test  select * from dba_objects;

49875 rows inserted

SQL> insert into parallel_test  select * from dba_objects;

49875 rows inserted

SQL>

有点不明白为什么并行度为4的时候有问题呢？

我测试为1的时候咋没有问题呢？

xsmdel

索性来个更全面的测试，带着点疑问版主

SQL> create table parallel_test as select * from dba_objects;

Table created

SQL> select table_name, degree from user_tables where table_name = upper('parallel_test');

TABLE_NAME                     DEGREE
------------------------------ --------------------
PARALLEL_TEST                           1

SQL> insert into parallel_test  select * from dba_objects;

49875 rows inserted

SQL> insert into parallel_test  select * from dba_objects;

49875 rows inserted

SQL> insert into parallel_test  select * from dba_objects;

49875 rows inserted

SQL> drop table parallel_test;

Table dropped

SQL> create table parallel_test parallel (degree 4) as select * from dba_objects;

Table created

SQL> select table_name,degree from user_tables where table_name=upper('parallel_test');

TABLE_NAME                     DEGREE
------------------------------ --------------------
PARALLEL_TEST                           4

SQL> insert into paraller_test select * from dba_objects;

insert into paraller_test select * from dba_objects

ORA-00942: 表或视图不存在

SQL> insert into parallel_test select * from dba_objects;

49875 rows inserted

SQL> insert into parallel_test select * from dba_objects;

49875 rows inserted

SQL> insert into parallel_test select * from dba_objects;

49875 rows inserted

SQL>

我把并行度调整成了4还是没有问题，没有commit;

xsmdel

ORA-12838: 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.
ORA-12700 to ORA-19380 13-9
Action: Rewrite the transaction, or break it up into two transactions: one
containing the initial modification and the second containing the parallel
modification operation.

huanhuanlove

赶紧射精。。。。。啊

mwdba

楼主够强，无私奉献精神可嘉！！！

wabjtam123

原帖由 xsmdel 于 2010-4-9 23:42 发表
索性来个更全面的测试，带着点疑问版主

SQL> create table parallel_test as select * from dba_objects;

Table created

SQL> select table_name, degree from user_tables where table_name = upper('parallel_test');

TABLE_NAME                     DEGREE
------------------------------ --------------------
PARALLEL_TEST                           1

SQL> insert into parallel_test  select * from dba_objects;

49875 rows inserted

SQL> insert into parallel_test  select * from dba_objects;

49875 rows inserted

SQL> insert into parallel_test  select * from dba_objects;

49875 rows inserted

SQL> drop table parallel_test;

Table dropped

SQL> create table parallel_test parallel (degree 4) as select * from dba_objects;

Table created

SQL> select table_name,degree from user_tables where table_name=upper('parallel_test');

TABLE_NAME                     DEGREE
------------------------------ --------------------
PARALLEL_TEST                           4

SQL> insert into paraller_test select * from dba_objects;

insert into paraller_test select * from dba_objects

ORA-00942: 表或视图不存在

SQL> insert into parallel_test select * from dba_objects;

49875 rows inserted

SQL> insert into parallel_test select * from dba_objects;

49875 rows inserted

SQL> insert into parallel_test select * from dba_objects;

49875 rows inserted

SQL>

我把并行度调整成了4还是没有问题，没有commit;

看来真是经典案例，你举的例子中的并行根本没有生效，所以当然不会有这个问题，你继续做实验，只不过在这个SESSION  设置alter session enable parallel dml;
你就明白了

SQL>
SQL> drop table  parallel_test;

Table dropped

SQL> create table parallel_test parallel (degree 4) as select * from dba_objects;

Table created

SQL> alter session enable parallel dml;

Session altered

SQL> select table_name,degree from user_tables where table_name=upper('parallel_test');

TABLE_NAME                     DEGREE
------------------------------ --------------------
PARALLEL_TEST                           4

SQL> insert into parallel_test select * from parallel_test;

59160 rows inserted

SQL> insert into parallel_test select * from parallel_test;

insert into parallel_test select * from parallel_test

ORA-12838: 无法在并行模式下修改之后读/修改对象

SQL>


如下是你的例子

SQL> create table parallel_test parallel (degree 4) as select * from dba_objects;

Table created

SQL> select table_name,degree from user_tables where table_name=upper('parallel_test');

TABLE_NAME                     DEGREE
------------------------------ --------------------
PARALLEL_TEST                           4

SQL> insert into parallel_test select * from parallel_test;

59161 rows inserted

SQL> insert into parallel_test select * from parallel_test;

118322 rows inserted

SQL>



差别知道了吧：）

其实完全可以通过执行计划和select * from v$px_session 等观察出来，你的例子并行度根本没生效，《ORACLE+UNIX易错集锦》前面就是描述这个为什么执行中看不到并行的例子，你可能没注意看：）

[ 本帖最后由 wabjtam123 于 2010-4-10 09:14 编辑 ]

xsmdel

确实是这个样。