Drop Column之ORA-01732

应用厂家同事报错，随即诊断如下：
[root@netmgrdb1 ~]# su - oracle
[oracle@netmgrdb1 ~]$ id
uid=1101(oracle) gid=1000(oinstall) groups=1000(oinstall),1020(asmadmin),1021(asmdba),1022(asmoper),1031(dba)
[oracle@netmgrdb1 ~]$ sqlplus '/ as sysdba'
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 17 16:26:56 2015
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> 
SQL> alter table INF_IMP.WM_J_TASK_ORDER drop column order_id;

alter table INF_IMP.WM_J_TASK_ORDER drop column order_id
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01732: data manipulation operation not legal on this view

undefine tablename;
SQL> @mview_log.sql
SQL> set echo off
Enter value for owner: INF_IMP
Enter value for tablename: WM_J_TASK_ORDER

no rows selected


SQL> radebug setmypid
SP2-0734: unknown command beginning "radebug se..." - rest of line ignored.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> oradebug event 1732 trace name errorstack forever,level 10;
Statement processed.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
SQL> oradebug event 1732 trace name errorstack forever,level 10;
Statement processed.
SQL> alter table INF_IMP.WM_J_TASK_ORDER drop column order_id;
alter table INF_IMP.WM_J_TASK_ORDER drop column order_id
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01732: data manipulation operation not legal on this view
*************************************************************
对应trace报错如下：

 10046+errorstack trac e时 发现的：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

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

 SQL> @parameter_hide.sql
SQL> set echo off
Enter Search Parameter (i.e. max|all) : _simple_view_merging

PARAMETER                                SESSION_VALUE        INSTANCE_VALUE       DESCRIPTION
---------------------------------------- -------------------- -------------------- ------------------------------------------------------------
_simple_view_merging                     FALSE                FALSE                control simple view merging performed by the optimizer 

SQL> SQL> 
SQL> 
SQL> set long 10000
SQL> select dbms_metadata.get_ddl('VIEW','WM_J_TASK_ORDER') from dual;
ERROR:
ORA-31603: object "WM_J_TASK_ORDER" of type VIEW not found in schema "SYS"
ORA-06512: at "SYS.DBMS_METADATA", line 5088
ORA-06512: at "SYS.DBMS_METADATA", line 7589
ORA-06512: at line 1

no rows selected

SQL> 
SQL> 
SQL> 
SQL> 
SQL> desc dba_views;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 VIEW_NAME                                 NOT NULL VARCHAR2(30)
 TEXT_LENGTH                                        NUMBER
 TEXT                                               LONG
 TYPE_TEXT_LENGTH                                   NUMBER
 TYPE_TEXT                                          VARCHAR2(4000)
 OID_TEXT_LENGTH                                    NUMBER
 OID_TEXT                                           VARCHAR2(4000)
 VIEW_TYPE_OWNER                                    VARCHAR2(30)
 VIEW_TYPE                                          VARCHAR2(30)
 SUPERVIEW_NAME                                     VARCHAR2(30)
 EDITIONING_VIEW                                    VARCHAR2(1)
 READ_ONLY                                          VARCHAR2(1)
SQL> select OWNER,VIEW_NAME,text  from dba_views  where VIEW_NAME='WM_J_TASK_ORDER';

no rows selected

SQL> 
SQL> 
SQL> 
SQL> alter system set "_simple_view_merging"=true;
System altered.

SQL>  alter table INF_IMP.WM_J_TASK_ORDER drop column order_id;

Table altered.


SQL> SQL> SQL> SQL> SQL> SQL> 


参考mos:
Applies to: 
 Oracle Database - Enterprise Edition - Version 10.1.0.2 to 11.2.0.2 [Release 10.1 to 11.2]
Information in this document applies to any platform.

Symptoms
ORA-01732 IN DBA_APPLY_ERROR ON REPLICATING DROP COLUMN DDL IF _simple_view_merging =FALSE .
For example:- 
If _simple_view_merging =FALSE is set to False in the Database then Replicated DROP COLUMN DDL can fail with ORA-01732 while applying at target database.
alter table TERMS3.PRODUKTE_ZU_GUTSCHEIN_AKTIONEN drop column ENUTZER; 
Above command executed at stream source database and transaction failed at Apply database with ORA-01732 and reported in DBA_APPLY_ERROR. 

The streams replication aborted with following message: 
local transaction ID: 2.12.74300 
source database: TERMSX 
creation time: 09.09.09 10:53:30 
error number: 1732 
message text: ORA-01732: data manipulation operation not legal on this view 
ORA-06512: at "SYS.LCR$_DDL_RECORD", line 256 
ORA-06512: at "STRMADMIN.TERMS3_DDL_HANDLER", line 32 
ORA-06512: at line 1 

Cause
Similar issue is reported in Bug 5232477 which was closed as as not a bug.
 Development explained that due to _simple_view_merging =FALSE being set, this will affect queries that are candidate for simple view merging ,Since the update query is generated internally when you execute the alter table command and the inline view is not merged. Hence the error.

Solution
-- To implement the solution, please execute the following steps::
1. Please set the hidden parameter in your init/spfile file and retest the workaround.
_simple_view_merging = TRUE
2. If you have the error still in the apply error, You may re-execute the error using.
dbms_apply.execute_error('LOCAL_TRANSACTION_ID');

 ---- Replace this with the real transaction id.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13750068/viewspace-1583787/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13750068/viewspace-1583787/