ora-01732錯誤 for ORACLE 10G

SQL> create table t1(name varchar2(6),age number(3));

Table created.


SQL> insert into t1 values('leo',28);

1 row created.

SQL> commit
2 ;

Commit complete.

SQL> insert into t1 values('ken',29);

1 row created.

SQL> commit;

Commit complete.

SQL> CREATE MATERIALIZED VIEW v1 as (select * from t1);

Materialized view created.

SQL> select * from v1;

NAME AGE
------ ----------
leo 28
ken 29

SQL> insert into t1 values('eric',29);

1 row created.

SQL> commit;

Commit complete.

[@more@]

SQL> create table t1(name varchar2(6),age number(3));

Table created.


SQL> insert into t1 values('leo',28);

1 row created.

SQL> commit
2 ;

Commit complete.

SQL> insert into t1 values('ken',29);

1 row created.

SQL> commit;

Commit complete.

SQL> CREATE MATERIALIZED VIEW v1 as (select * from t1);

Materialized view created.

SQL> select * from v1;

NAME AGE
------ ----------
leo 28
ken 29

SQL> insert into t1 values('eric',29);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from v1;

NAME AGE
------ ----------
leo 28
ken 29

SQL> delete from v1;
delete from v1
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


SQL> update v1 set age=29 where name='leo';
update v1 set age=29 where name='leo'
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


SQL> update t1 set age=29 where name='leo';

1 row updated.

SQL> commit;

Commit complete.

SQL> truncate table v1;

Table truncated.

SQL> select * from v1;

no rows selected

SQL> select * from t1;

NAME AGE
------ ----------
leo 29
ken 29
eric 29


SQL> insert into v1 values('ella',30);
insert into v1 values('ella',30)
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view


SQL> drop materialized view v1;

Materialized view dropped.

SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME VARCHAR2(6)
AGE NUMBER(3)

SQL> select * from t1;

NAME AGE
------ ----------
leo 29
ken 29
eric 29

SQL> create MATERIALIZED VIEW v1 refresh force with rowid for update as (select * from t1);

Materialized view created.

SQL> select * from v1;

NAME AGE
------ ----------
leo 29
ken 29
eric 29

SQL> delete from v1;

3 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from v1;

no rows selected

SQL> exec dbms_mview.refresh('v1');

PL/SQL procedure successfully completed.

SQL> select * from v1;

NAME AGE
------ ----------
leo 29
ken 29
eric 29

參考網址：

http://blog.sina.com.cn/s/blog_492c0acd01009lec.html

http://database.group.javaeye.com/group/blog/546309

http://yangtingkun.itpub.net/post/468/501199

http://hi.baidu.com/optical/blog/item/fc15c1fcdda93687b901a09c.html

http://blog.chinaunix.net/u/9295/showart_176143.html

http://hi.baidu.com/strongit/blog/item/3c4c7f822ec0f6a60cf4d290.html