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

Morning started with user email sending application screenshot showing error ORA-01732: data manipulation operation not legal on this view. It’s 10.2.0.3 DB

So, what this means that we are not able to update some view hmmm. As this was from some application i don’t know where it was happening. So traced DB and ask user to regenerate error and bingo. (All Names and Statement are changed but it should illustrate issue)


PARSE ERROR #23:len=448 dep=0 uid=51 oct=6 lid=51 tim=2007323474938 err=1732
UPDATE "CALL_TABLE" SET "TYPE" = 'TYPE-D' WHERE "ID"=1234567890
WAIT #23: nam='SQL*Net break/reset to client' ela= 2 driver id=1413697536 break?=1 p3=0 obj#=-1 tim=2007323475094
WAIT #23: nam='SQL*Net break/reset to client' ela= 1080 driver id=1413697536 break?=0 p3=0 obj#=-1 tim=2007323476188
WAIT #23: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2007323476202
WAIT #23: nam='SQL*Net message from client' ela= 8881 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2007323485137
XCTEND rlbk=1, rd_only=1
WAIT #0: nam='SQL*Net message to client' ela= 0 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=2007323485204

So, it was coming from application user (a common user for all connection). Finding more it was synonym which was pointing to some view. And on view there were some policy applied and finally that view was pointing to base table. Moreover, there was no complex things in that view just

create view view_v1 as select * from XYZ.CALL_TABLE

Let’s try manually doing this


Sys@xxxxxxxx> UPDATE "CALL_TABLE" SET "TYPE" = 'TYPE-D' WHERE "ID"=1234567890;

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

Sys@xxxxxxxx> explain plan for UPDATE "CALL_TABLE" SET "TYPE" = 'TYPE-D' WHERE "ID"=1234567890;

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

Hmmm let’s see what data is there


Sys@xxxxxxxx> select * from CALL_TABLE WHERE ID=1234567890

Execution Plan
----------------------------------------------------------
Plan hash value: 4166853210

---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                          |     1 |  2267 |     2   (0)| 00:00:01 |
|   1 |  VIEW                        | VW_CALL_TABLE            |     1 |  2267 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| CALL_TABLE               |     1 |   143 |     2   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PK_CALL_TABLE            |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------

So, it did return data. But why can’t we update it. Here is the clue which i got from plan and also from trace


Sys@xxxxxxxx> explain plan for UPDATE "CALL_TABLE" SET "TYPE" = 'TYPE-D' WHERE "ID"=1234567890;

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

*** 2010-11-10 16:23:36.022
*** SERVICE NAME:(SYS$USERS) 2010-11-10 16:23:36.022
*** SESSION ID:(165.46893) 2010-11-10 16:23:36.022
Registered qb: UPD$1 0xb0662550 (PARSER)
  signature (): qb_name=UPD$1 nbfros=1 flg=0
    fro(0): flg=5 objn=34994 hint_alias="CALL_TABLE"@"UPD$1"
Registered qb: SEL$1 0xb39bd130 (PARSER)
  signature (): qb_name=SEL$1 nbfros=1 flg=0
    fro(0): flg=4 objn=274478 hint_alias="CALL_TABLE"@"SEL$1"
**************************
Predicate Move-Around (PM)
**************************
PM: Considering predicate move-around in UPD$1 (#0).
PM:   Checking validity of predicate move-around in UPD$1 (#0).
JPPD:     JPPD bypassed: View not on right-side of outer join
CBQT: Validity checks failed for dnbuxs53wm06y.
CVM: Considering view merge in query block UPD$1 (#0)
CVM:   Checking validity of merging SEL$1 (#0)
CVM: Considering view merge in query block SEL$1 (#0)

There is nothing more logged in this trace except above. But, here is our clue that it has to do something with view merging while parsing(Where it’s failing). After searching something more on metalink my hunch was right. After changing that parameter “ta da” everything went fine and was able to insert data.  Here is the example.


Sys@xxxxxxxx> create table test (col1 varchar2(1));
Sys@xxxxxxxx> create view test_v as select * from test;

Sys@xxxxxxxx> alter session set "_simple_view_merging"=FALSE;

Session altered.

Sys@xxxxxxxx> insert into test_v values('A');
insert into test_v values('A')
            *
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

Sys@xxxxxxxx> alter session set "_simple_view_merging"=TRUE;

Session altered.

Sys@xxxxxxxx> insert into test_v values('A');

1 row created.

And after changing this parameter ta da

Sys@xxxxxxxx> explain plan for UPDATE "CALL_TABLE" SET "TYPE" = 'TYPE-D' WHERE "ID"=1234567890;

-----------------------------------------------------------------------------------------------
| Id  | Operation          | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                          |     1 |   143 |     2   (0)| 00:00:01 |
|   1 |  UPDATE            | CALL_TABLE               |       |       |            |          |
|*  2 |   INDEX UNIQUE SCAN| PK_CALL_TABLE            |     1 |   143 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Advertisements

About Taral
I am Rookie to Oracle Technology so let's see where it goes

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: