cancel
Showing results for 
Search instead for 
Did you mean: 

Sql error 1732 performing UPD on table PROGDIR

ACE-SAP
Active Contributor
0 Kudos
315

Hello

It's about a tricky problem I get with an undocumented Oracle parameter

I get that error on a Unix box with Oracle 11.2.0.3.7 SBP 2013-09

When accessing to any table through SE16 I get a dump:

Runtime Errors         DBIF_RSQL_SQL_ERROR

Exception              CX_SY_OPEN_SQL_DB

Date and Time          07.11.2013 14:01:45

UPDATE "PROGDIR" SET "RSTAT"=:A0 , "UNAM"=:A1 , "CNAM"=:A2 , "EDTX"=:A3 , "RLOAD"=:A4 ,

"OCCURS"=:A5\ [dbsc.c#2316],

"SUBC"=:A6 WHERE "NAME"=:A7 AND "STATE"=:A8 ; [dbsc.c#2318]

***LOG BY4=> sql error 1732 performing UPD on table PROGDIR

No error were reported at Oracle level.

It took a while before I discover that this was caused by parameter _simple_view_merging  wrongly set to TRUE.

I did test this on a Windows system with  Oracle 11.2.0.3 Bundle patch 22.

That time _simple_view_merging had to be set to FALSE to get the same Dump.

The thing is that I do not understand how that parameter can cause that problem has far as no IOTs are reported on my DB.

SELECT count(*) FROM DBA_INDEXES WHERE INDEX_TYPE = 'IOT - TOP' AND OWNER like 'SAP%';

COUNT(*)

      0

I will be pleased to understand the root cause of that problem.

Anyway I think it is a good thing to know, that's why I did post this message.

Best regards

1915485 - Wrong results on Oracle Index Organized Table (IOT's)

A query involving IOT and secondary IOT indexes returns wrong results with the fix for bug 13705338 applied.

SELECT count(*) FROM DBA_INDEXES WHERE INDEX_TYPE = 'IOT - TOP' AND OWNER like 'SAP%';

COUNT(*)

      0

If you see that your SAP user having a count > 0 then IOT's are created and you are affected by this bug.
If not, you don't need to apply the mentioned patch below.

1431798 - Oracle 11.2.0: Database Parameter Settings

September 30, 2013 =>  adjusted _simple_view_merging

September 19, 2013 =>  added _simple_view_merging = false for 11.2.0.3

_simple_view_merging FALSE (Note 1915485 - only whith IOT's)

                                       UNIX SXD 11203x_date    ( 2012 05 <= date <= 2013 10 )

                                       WIN      11.2.0.3.nP ( 6 <= n <= 26 )

Accepted Solutions (1)

Accepted Solutions (1)

stefan_koehler
Active Contributor
0 Kudos

Hi Yves,

i think two things are mixed up here: Wrong result sets and an ORA-01732 (data manipulation operation not legal on this view) error. I don't know how the database object PROGDIR looks like in detail, but does this issue maybe occur on parsing?

For example on your unix box:

shell> sqlplus <SAP_SCHEMA_OWNER>/<PASS>

SQL> alter session set "_simple_view_merging"=TRUE;

SQL> explain plan for UPdATE "PROGDIR"

                                    SET "RSTAT"=:A0 , "UNAM"=:A1 , "CNAM"=:A2 , "EDTX"=:A3 , "RLOAD"=:A4 ,  "OCCURS"=:A5, "SUBC"=:A6

                                    WHERE "NAME"=:A7 AND "STATE"=:A8 ;

SQL> alter session set "_simple_view_merging"=FALSE;

SQL> explain plan for UpDATE "PROGDIR"

                                    SET "RSTAT"=:A0 , "UNAM"=:A1 , "CNAM"=:A2 , "EDTX"=:A3 , "RLOAD"=:A4 ,  "OCCURS"=:A5, "SUBC"=:A6

                                    WHERE "NAME"=:A7 AND "STATE"=:A8 ;

Regards

Stefan

ACE-SAP
Active Contributor
0 Kudos

Hello Stefan,

Thank you for your help.

It is not a matter of a wrong result set.

It's a simple update query that is seen as incorrect when the _simple_view_merging parameter is changed from it's default value

I did run the query on sqlplus, replacing the bind variables and I get the same, error.

sqlplus "/as sysdba"

set lines 128

SELECT substr(a.ksppinm,1,20)  "Parameter",  substr(b.ksppstvl,1,5) "Session", substr(c.ksppstvl,1,5) "Instance" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '%simple_view_merging%';

Parameter                                Session         Instance

---------------------------------------- --------------- ----------

_simple_view_merging                     TRUE            TRUE

UPDATE sapsr3."PROGDIR" SET rstat = 'S', unam  = 'SAP*', cnam  = 'SAP*', edtx  = 'X' , rload = 'EN', occurs = '1', subc  = '1' where name = '/1BCDWB/DBVARIS' and state = 'A';

1 row updated.

alter session set "_simple_view_merging"=FALSE;

SELECT substr(a.ksppinm,1,20)  "Parameter",  substr(b.ksppstvl,1,5) "Session", substr(c.ksppstvl,1,5) "Instance" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '%simple_view_merging%';

Parameter                                Session         Instance

---------------------------------------- --------------- ----------

_simple_view_merging                     FALSE           TRUE

UPDATE sapsr3."PROGDIR" SET rstat = 'S', unam  = 'SAP*', cnam  = 'SAP*', edtx  = 'X' , rload = 'EN', occurs = '1', subc  = '1' where name = '/1BCDWB/DBVARIS' and state = 'A';

ERROR at line 1:

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

PROGDIR is a basic view on table Reposrc.

Best regards

SQL> select OBJECT_TYPE from dba_objects where owner = 'SAPSR3' and OBJECT_NAME = 'PROGDIR';

OBJECT_TYPE

-------------------

VIEW

SQL> Set lines 80 pages 500

SQL> SET LONG 2000

SQL> COLUMN VIEW_NAME FORMAT a20

SQL> COLUMN TEXT FORMAT a80 WORD_WRAPPED

SQL> select VIEW_NAME "View", TEXT "Code"  from dba_views where view_name like 'PROGDIR';

View

------------------------------

Code

--------------------------------------------------------------------------------

PROGDIR

SELECT T1."PROGNAME", T1."R3STATE", T1."SQLX", T1."EDTX", T1."VARCL", T1."DBAPL"

, T1."DBNA", T1."CLAS", T1."TYPE", T1."OCCURS", T1."SUBC", T1."APPL", T1."SECU",

T1."CNAM", T1."CDAT", T1."UNAM", T1."UDAT", T1."VERN", T1."LEVL", T1."RSTAT", T

      1."RMAND", T1."RLOAD", T1."FIXPT", T1."SSET", T1."SDATE", T1."STIME", T1."IDATE"

, T1."ITIME", T1."LDBNAME", T1."UCCHECK" FROM "REPOSRC" T1 WHERE ( T1."R3STATE"

= 'A' OR T1."R3STATE" = 'I' )

SQL> desc sapsr3.REPOSRC

Name                                      Null?    Type

----------------------------------------- -------- ----------------------------

PROGNAME                                  NOT NULL VARCHAR2(120)

R3STATE                                   NOT NULL VARCHAR2(3)

SQLX                                      NOT NULL VARCHAR2(3)

EDTX                                      NOT NULL VARCHAR2(3)

DBNA                                      NOT NULL VARCHAR2(6)

CLAS                                      NOT NULL VARCHAR2(12)

TYPE                                      NOT NULL VARCHAR2(9)

OCCURS                                    NOT NULL VARCHAR2(3)

SUBC                                      NOT NULL VARCHAR2(3)

APPL                                      NOT NULL VARCHAR2(3)

SECU                                      NOT NULL VARCHAR2(24)

CNAM                                      NOT NULL VARCHAR2(36)

CDAT                                      NOT NULL VARCHAR2(24)

VERN                                      NOT NULL VARCHAR2(18)

LEVL                                      NOT NULL VARCHAR2(12)

RSTAT                                     NOT NULL VARCHAR2(3)

RMAND                                     NOT NULL VARCHAR2(9)

RLOAD                                     NOT NULL VARCHAR2(3)

UNAM                                      NOT NULL VARCHAR2(36)

UDAT                                      NOT NULL VARCHAR2(24)

UTIME                                     NOT NULL VARCHAR2(18)

DATALG                                    NOT NULL NUMBER(10)

VARCL                                     NOT NULL VARCHAR2(3)

DBAPL                                     NOT NULL VARCHAR2(3)

FIXPT                                     NOT NULL VARCHAR2(3)

SSET                                      NOT NULL VARCHAR2(3)

SDATE                                     NOT NULL VARCHAR2(24)

STIME                                     NOT NULL VARCHAR2(18)

IDATE                                     NOT NULL VARCHAR2(24)

ITIME                                     NOT NULL VARCHAR2(18)

LDBNAME                                   NOT NULL VARCHAR2(60)

UCCHECK                                   NOT NULL VARCHAR2(3)

MAXLINELN                                 NOT NULL NUMBER(5)

DATA                                      BLOB


stefan_koehler
Active Contributor
0 Kudos

Hi Yves,

thanks for the detailed information.

I knew that your current issue is not about the wrong result set, but you have mentioned SAPnote #1915485 which explains this as a reason for setting this parameter - in reality there are some others as well

I am pretty sure that this is not an issue with the execution of the SQL - it is an issue with parsing. Can you please run my previous posted SQL example on your unix box and post the result.

Regards

Stefan

ACE-SAP
Active Contributor
0 Kudos

Stefan,

Sure that parameter must have other effects, but I was not able to find that much information about them on the Web, even on Metalink.

It is strange because the view & the update statement are very simple.

DST Upgrade using DBMS_DST.BEGIN_PREPARE fail with ORA-2014 (Doc ID 1407273.1)

The same issue has been reported under Bug 10138792  ORA-2014 ON SEGMENT ADVISOR SELECT STMT WHEN _SIMPLE_VIEW_MERGING=FALSE, closed as not a bug. When the SELECT FOR UPDATE statement applied to view, anything that prevents view merging from taking place would result in ORA-2014  error;  "anything" could be no_merge hint, setting the parameter  "_simple_view_merging" to false, or something in the query structure that  prevents view merging. In this case, the database has "_simple_view_merging" set to FALSE and the DBMS_DST.BEGIN_PREPARE has similar calls to SELECT FOR UPDATE statements.

Bug 12537316 - ORA-600 / ORA-7445 for SQL with merged subquery (Doc ID 12537316.8)

When a query has a view with a select sub-query, after the view is merged and  the select sub-query appears in places other than the select list then ORA-600 or ORA-7445 errors may be produced.


Find in attachment the command run on the Windows box.

I do not have a remote access to the Unix box, I'll try to have someone running it for me tomorrow and I will post the result.

Thanks for you help

stefan_koehler
Active Contributor
0 Kudos

Hi Yves,

i have done a little bit of researching about this with an adopted and stripped down example on Oracle 11.2.0.3.6.

SQL> create table TESTME (a VARCHAR2(10), B VARCHAR2(2));

SQL> insert into TESTME values ('TEST','A');

SQL> commit;

SQL> create view TESTME_V as select A from TESTME where B = 'A';

SQL> alter session set "_simple_view_merging"=TRUE;

SQL> explain plan for UPDATE TESTME_V SET A = 'TESTU' where A = 'TEST';

SQL> alter session set "_simple_view_merging"=FALSE;

SQL> explain plan for UPDATE TESTME_V SET A = 'TESTU' where A = 'TEST';

ERROR at line 1:

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

So far it is reproducible with that stripped exampled. It behaves the same way on 11g R2 and 12c R1. Parameter "_simple_view_merging" is described as "control simple view merging performed by the optimizer". As this issue occurs by parsing - let's do some CBO traces.

*** CBO Trace (with "_simple_view_merging"=TRUE;") ***

CVM - complex view merging

SPJ - select-project-join

OJE: Begin: find best directive for query block SEL$1 (#0)

OJE: End: finding best directive for query block SEL$1 (#0)

CVM:   Merging SPJ view SEL$1 (#0) into UPD$1 (#0)

Registered qb: SEL$DA9F4B51 0x7b31eab8 (VIEW MERGE UPD$1; SEL$1)

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT 0 FROM SYS."TESTME" "TESTME" WHERE "TESTME"."A"='TEST' AND "TESTME"."B"='A'

kkoqbc: optimizing query block SEL$DA9F4B51 (#0)

*** CBO Trace (with "_simple_view_merging"=FALSE;") ***

OJE: Begin: find best directive for query block SEL$1 (#0)

OJE: End: finding best directive for query block SEL$1 (#0)

SVM:     SVM bypassed: Parameter.

Take a look at the cost based query transformation in the example with simple view merging enabled (for UPDATE statement). At last let's take a look at the detailed execution plans in case of SELECTs and the corresponding working UPDATE.

SQL> alter session set "_simple_view_merging"=TRUE;

SQL> select A from TESTME_V where A = 'TEST';

-----------------------------------------------------------------------------

| Id  | Operation      | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |       |        |        |      2 (100)|        |

|*  1 |  TABLE ACCESS FULL| TESTME |      1 |     10 |      2   (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(("A"='TEST' AND "B"='A'))

SQL> alter session set "_simple_view_merging"=FALSE;

SQL> select A from TESTME_V where A = 'TEST';

--------------------------------------------------------------------------------

| Id  | Operation       | Name     | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

--------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |          |        |       |     2 (100)|           |

|   1 |  VIEW           | TESTME_V |      1 |     7 |     2     (0)| 00:00:01 |

|*  2 |   TABLE ACCESS FULL| TESTME   |      1 |    10 |     2     (0)| 00:00:01 |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(("B"='A' AND "A"='TEST'))

SQL> alter session set "_simple_view_merging"=TRUE;

SQL> UPDATE TESTME_V SET A = 'TESTU' where A = 'TEST';

------------------------------------------------------------------------------

| Id  | Operation       | Name   | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |

------------------------------------------------------------------------------

|   0 | UPDATE STATEMENT   |        |         |         |       2 (100)|         |

|   1 |  UPDATE        | TESTME |         |         |          |         |

|*  2 |   TABLE ACCESS FULL| TESTME |       1 |      10 |       2   (0)| 00:00:01 |

------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - filter(("A"='TEST' AND "B"='A'))

If simple view merging is enabled the view definition is resolved (eliminated) and a simple SELECT or UPDATE with the concatenated predicates is performed on the underlying table. Otherwise only the predicate is pushed down, but this works in a SELECT scenario only. In case of an UPDATE the optimizer took notice of that and stops at the point of SVM.

Regards

Stefan

P.S.: I can highly recommend the presentation of Jože Senegačnik about "Query Transformations", if you are interested into such query transformations in more detail.

ACE-SAP
Active Contributor
0 Kudos

Hello Stefan

Thank you very much for this very interesting analysis.

Best regards

ACE-SAP
Active Contributor
0 Kudos

A quick update, SAP has released a note on that problem...

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

Funny to notice that there is a mistake, (at least in version 2) of the note as undocumented (beginning with an underscore) parameters cannot be queried through view v$parameter...

select value from v$parameter where name='_simple_view_merging';

no rows selected

To find the value of hidden parameter, as stated few lines upper:

SELECT substr(a.ksppinm,1,20) "Parameter",  substr(b.ksppstvl,1,5) "Session", substr(c.ksppstvl,1,5) "Instance" FROM x$ksppi a, x$ksppcv b, x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm LIKE '%simple_view_merging%';

Answers (0)