ITPub博客

首页 > 数据库 > Oracle > DML对prebuilt table的内容修改时出现ORA-01732

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

原创 Oracle 作者:kl911 时间:2008-07-04 14:15:56 0 删除 编辑

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/661919/viewspace-1006720/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-01-03

  • 博文量
    20
  • 访问量
    320511