During a database upgrade vom 11.2.0.4 to 18.4.1 i encountered the following problem:
REASON:
ERRORS FOUND: During Upgrade
FILENAME: /data/oracle/product/cfgtoollogs/dbua/upgrade2019-02-22_10-18-20AM/DB/catupgrd1R0.log AT LINE NUMBER: 666432
------------------------------------------------------
Identifier CATALOG 19-02-22 11:00:37
SCRIPT = [/data/oracle/product/18.4/rdbms/admin/cdcore_cols.sql]
ERROR = [ORA-01422: exact fetch returns more than requested
number of rows ORA-06512: at "SYS.CDBVIEW_INTERNAL", line 199
ORA-06512: at "SYS.CDBVIEW_INTERNAL", line 64
ORA-06512: at "SYS.CDBVIEW", line 6
ORA-06512: at line 1
]
STATEMENT = [BEGIN CDBView.create_cdbview(false,'SYS','DBA_TAB_COLS','CDB_TAB_COLS'); END;
]
------------------------------------------------------
Note that the view name (here: DBA_TAB_COLS) can change!
The reason for this to fail is in the file $ORACLE_HOME/rdbms/admin/catcdbviews.sql:
-- Check if oldview already exist, qualify with owner# to avoid ORA-1422 -- error,if two dictionary view with the same name exists across different -- schemas like DBA_DV_STATUS which exists in both SYS and DVSYS schemas. begin execute immediate 'SELECT 1 FROM OBJ$ WHERE NAME = :1' || ' AND TYPE# in (2, 4) and owner# = :2' into cCheck using oldview, ownerId;
The developer already identified one issue with his code, but overlooked another: Whenever a database link gets referenced in a piece of pl/sql code (user code or internal code – does not matter), a object with the same name as referenced by the database link is created in OBJ$.
SQL> select obj#, name, remoteowner,linkname from obj$ where TYPE# in (2, 4) and name=’DBA_TAB_COLS’;
OBJ# NAME REMOTEOWNER LINKNAME ---------- -------------- ------------- -------------------------- 14000219 DBA_TAB_COLS SYS STAGING_SYSTEM 3334 DBA_TAB_COLS
As you can see we have a second object in OBJ$ named DBA_TAB_COLS for the database link. And this causes the upgrade to fail.
The fix is relatively simple: Either get a patch from Oracle or fix the code in catcdbviews.sql yourself:
begin execute immediate 'SELECT 1 FROM OBJ$ WHERE NAME = :1' || ' AND TYPE# in (2, 4) and remoteowner is null and owner# = :2' into cCheck using oldview, ownerId;
After editing the scirpt, install it by executing catcdbview.sql to apply the changes and restart the upgrade. The upgrade will start from the failed step onwards.
Note that the package you edit here is only used during the database upgrade to transfer comment from non-CDB to CDB views. It is deleted after the upgrade and is not needed or used during normal database operation.