应用厂家同事报错，随即诊断如下：
[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 trace时发现的：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博客

Drop Column之ORA-01732

最新文章