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@]