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

Cause:

An attempt was made to use an UPDATE, INSERT, or DELETE statement on a view that contains expressions or functions or was derived from more than one table. If a join operation was used to create the view or the view contains virtual columns derived from functions or expressions, then the view may only be queried.


Action:

UPDATE, INSERT, or DELETE rows in the base tables instead and restrict the operations on the view to queries.

Troubleshooting:

Based on knowledge articles it felt like application internal code involving view or materialized view containing complex views being updated causing the error ORA-01732.

Use tracing to confirm the exact triggering SQL, if called within package/procedure/functions.

oradebug setospid <spid>

oradebug event 10046 trace name context forever, level 12

oradebug tracefile_name

oradebug event 10046 trace name context off;

or

alter session set events '10046 trace name context forever, level 12';

alter session set events '10046 trace name context off';

context off will disable the tracing  ; oradebug to trace session externally and alter session to trace within session.

tkprof <tracefile> <outputfile> waits=yes sort=exeela 

Error encountered :ORA-01732

***************************

update (select intcol#, ind$.bo# bo# 

from ind$, icol$ 

where ind$.obj# = icol$.obj#) 

set intcol#=intcol#-:1 

where (bo#=:2 or bo#=:3) and intcol#>:4

Above SQL confirms that internal fixed view DML's of optimizer causing ORA-01732: data manipulation operation not legal on this view and not application code.

Solution:

Simple view merging in Oracle is a query transformation that merges select-project-join views to improve performance. 

alter session set "_simple_view_merging"=true;

As "_simple_view_merging" is oracle hidden parameter and should not be set without oracle consultation as it will drive the optimizer behavior for merge statements performance , hence if required only set at session level within a particular job/call.  

alter system set "_simple_view_merging"=true;

Hope this should fix your issue !