datapatch Fails with Error:"ORA-01427: single-row subquery returns more than one row"

(Doc ID 2280864.1)

Last updated on JUNE 30, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.

Symptoms

 datapatch fails with following error:

SQL> — This detects all RMAN base catalogs deployed and does privileges adjustments
SQL> — depending on the presence of the VPC users associated with that catalog and
SQL> — its current VPD status
SQL> declare
2 procedure r (
3 i_priv in varchar2
4 )
5 is
6 begin
7 execute immediate ‘revoke ‘ || i_priv
8 || ‘ from recovery_catalog_owner’;
9 exception
.
..
88 for u in (
89 select u.username catowner
90 , (
91 select ‘y’
92 from dba_tab_privs t
93 where t.grantee = r.granted_role
94 and t.table_name = ‘DBMS_RLS’
95 and t.privilege = ‘EXECUTE’
96 and t.owner = ‘SYS’
97 ) has_rls
..
..
112 execute immediate
113 ‘grant recovery_catalog_owner_vpd to ‘
114 || dbms_assert.enquote_name(u.catowner);
115 execute immediate
116 ‘revoke recovery_catalog_owner from ‘
117 || dbms_assert.enquote_name(u.catowner);
118 end if;
119 end loop;
120
121 r(‘drop any synonym’);
122 r(‘create any trigger’);
123 r(‘create any synonym’);
124 r(‘create public synonym’);
125 r(‘administer database trigger’);
126 r(‘recovery_catalog_user’);
127 r(‘execute on dbms_rls’);
128 end;
129 /
declare
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
ORA-06512: at line 88

Changes

 While running datapatch

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms