[20160311]ora-01732.txt

[20160311]ora-01732.txt

--今天工作出现ora-01732错误,做一个测试例子,做一个记录:

1.环境:

SCOTT@test01p> @ ver1

PORT_STRING          VERSION     BANNER                                                                        CON_ID
-------------------- ----------- ----------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.1.0.1.0  Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production       0

SCOTT@test01p> create table dept2 as select * from dept ;
Table created.

2.测试:
SCOTT@test01p> delete from (select * from dept2 where rownum=1);
delete from (select * from dept2 where rownum=1)
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

D:\tools\rlwrap>oerr ora 1732
oerr ora 1732
01732, 00000, "data manipulation operation not legal on this view"
// *Cause:
// *Action:

--我操作的对象是表,不是视图啊,为什么呢?

3.但是如果你查询如下:
SCOTT@test01p> select *  from (select * from dept2 where rownum=1);
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  d8c0y9masj9g5, child number 0
-------------------------------------
select *  from (select * from dept2 where rownum=1)
Plan hash value: 1094723229
------------------------------------------------------------------------------
| Id  | Operation           | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |       |        |       |     2 (100)|          |
|   1 |  VIEW               |       |      1 |    30 |     2   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY     |       |        |       |            |          |
|   3 |    TABLE ACCESS FULL| DEPT2 |      1 |    20 |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2 / from$_subquery$_001@SEL$1
   2 - SEL$2
   3 - SEL$2 / DEPT2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM=1)

--可以注意ID=1,已经转换为视图.如果这样写:

SCOTT@test01p> select *  from (select * from dept2 ) where rownum=1;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK

SCOTT@test01p> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  4zkrhx0q6g68j, child number 0
-------------------------------------
select *  from (select * from dept2 ) where rownum=1
Plan hash value: 2935958459
-----------------------------------------------------------------------------
| Id  | Operation          | Name  | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |        |       |     2 (100)|          |
|*  1 |  COUNT STOPKEY     |       |        |       |            |          |
|   2 |   TABLE ACCESS FULL| DEPT2 |      1 |    20 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$F5BB74E1
   2 - SEL$F5BB74E1 / DEPT2@SEL$2
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)

--知道这些就明白什么回事了.写成如下:

SCOTT@test01p> update (select * from dept2 ) set dname='cccc' where rownum=1;
1 row updated.

SCOTT@test01p> select * from dept2;
    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 cccc           NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     DALLAS

SCOTT@test01p> rollback ;
Rollback complete.