数据库安装RU的DATAPATCH报：ORA-01732

郑全

在安装完RU后，在执行./datapatch -verbose时，发现报错：
DBD::Oracle::st execute failed: ORA-01732: data manipulation operation not legal on this view
ORA-06512: at line 2 (DBD ERROR: OCIStmtExecute) [for Statement "BEGIN
           INSERT INTO sys.dba_registry_sqlpatch_ru_info
             (patch_id,
              patch_uid,
              patch_descriptor,
              ru_version,
              ru_build_description,
              ru_build_timestamp,
              patch_directory)
           VALUES
             (:patch_id,
              :patch_uid,
              :patch_descriptor,
              :ru_version,
              :ru_build_description,
              TO_TIMESTAMP(:ru_build_timestamp, 'YYMMDDHH24MISS'),
              :patch_directory);
           COMMIT;
         END;" with ParamValues: :patch_descriptor=OCIXMLTypePtr=SCALAR(0x326f5f0), :patch_directory


经MOS查询，发现是On a Materialized View environment , value of "_simple_view_merging parameter" parameter was set to FALSE 导致，这个确实是前面19.12上的一个BUG，修改了该参数，去掉该参数后，再去执行DATAPATCH ，问题消失。


附：
   Datapatch Fails with "ORA-01732: data manipulation operation not legal error" (Doc ID 2058586.1)


In this Document
Symptoms
Cause
Solution
References


APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Information in this document applies to any platform.
SYMPTOMS
After applying one-off Patch 21290101 , post install step produces below error


$ datapatch


Validating logfiles...
DBD::Oracle::st execute failed: ORA-01732: data manipulation operation not legal on this view (DBD ERROR: error possibly near <*> indicator at char 7 in 'UPDATE <*>dba_registry_sqlpatch
SET status = :p1, action_time = SYSTIMESTAMP
WHERE patch_id = :p2
AND patch_uid = :p3
AND action = UPPER(:p4)
AND action_time = (SELECT MAX(action_time)
FROM dba_registry_sqlpatch
WHERE patch_id = :p5
AND patch_uid = :p6
AND action = UPPER(:p7))') [for Statement "UPDATE dba_registry_sqlpatch
SET status = ?, action_time = SYSTIMESTAMP
WHERE patch_id = ?
AND patch_uid = ?
AND action = UPPER(?)
AND action_time = (SELECT MAX(action_time)
FROM dba_registry_sqlpatch
WHERE patch_id = ?
AND patch_uid = ?
AND action = UPPER(?))" with ParamValues: :p1='SUCCESS', :p2='19877336', :p3='18313828', :p4='apply', :p5='19877336', :p6='18313828', :p7='apply'] at /u01/app/oracle/product/12.1.0/dbhome/sqlpatch/sqlpatch.pm line 2659.



CAUSE
On a Materialized View environment , value of "_simple_view_merging parameter" parameter was set to FALSE


SOLUTION
1. Log in to database as sysdba


SQL>connect sys as sysdba
2. Modify the parameter "_simple_view_merging parameter" to TRUE.


SQL>ALTER SYSTEM SET "_simple_view_merging"=TRUE
3. Execute datapatch.