oracle prebuilt table,DML对prebuilt table的内容修改时出现ORA-01732

Insert到一张聚合表时出现如下错误，显然是因为MVs建在该表上，所以不能改，通过查看Metalink的: 463025.1,420777.1

尝试不删除的情况下直接修改，但失败了，这种方法只对View有用，对MV没用？如下测试:

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

SQL> create table kl1 as select * from sh.sales where rownum<200; --- kl1 sales

Table created.

SQL> create table kl2 as select * from sh.products where rownum<200; ---kl2 products

Table created.

SQL> create table kl3 as select * from sh.times where rownum<200; ---- kl3 times

Table created.

SQL> create table kl911 as select p.prod_id, p.prod_name, t.time_id, t.week_ending_day,

2 s.channel_id, s.promo_id, s.cust_id, s.amount_sold

3 from kl1 s, kl2 p, kl3 t

4 where s.time_id=t.time_id and s.prod_id=p.prod_id;

Table created.

SQL> create materialized view kl911

2 on prebuilt table without reduced precision

3 using index

4 refresh force on demand

5 with rowid

6 using default local rollback segment

7 enable query rewrite

8 as

9 select p.prod_id, p.prod_name, t.time_id, t.week_ending_day,

10 s.channel_id, s.promo_id, s.cust_id, s.amount_sold

11 from kl1 s, kl2 p, kl3 t

12 where s.time_id=t.time_id and s.prod_id=p.prod_id;

Materialized view created.

SQL> delete kl911 where CUST_ID=2380;

delete kl911 where CUST_ID=2380

*

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

SQL> conn sh/sh@k01

Connected.

SQL> GRANT MERGE ANY VIEW TO kl;

Grant succeeded.

----修改参数 OPTIMIZER_SECURE_VIEW_MERGING:

SQL> alter system set OPTIMIZER_SECURE_VIEW_MERGING=FALSE scope=memory;

System altered.

SQL> grant all on kl.kl911 to kl;

Grant succeeded.

SQL> grant merge any view to kl;

Grant succeeded.

SQL> conn kl/kl@k01

Connected.

SQL> delete kl911 where CUST_ID=2380;

delete kl911 where CUST_ID=2380

*

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

发现即使修改optimizer_secure_view_merging为false, 并且grant merge any view和all on 'some mvs' name'，还是不能直接修改pre-built table的内容。[@more@]