ora-01435 "user does not exist" error in Oracle database agent collections (4376047)

ora-01435 "user does not exist" error in Oracle database agent collections

The Oracle agent encounters a "Failed to execute collection [DBO_Maintenance], reason=ORA-01435: User does not exist" error or alarm when using a common user from any of several difference collections including DBO_Startup, DBO_Maintenance, and DBO_Instance_General

Prior to the Oracle user being created, the session had been altered with the following command "ALTER SESSION set "_ORACLE_SCRIPT"=true" to allow a local temporary tablespace to be used in place of a CDB-wide default temporary database, such as

create user C##USERNAME identified by PASSWORD container=all
default tablespace USERS
temporary tablespace PDB_TEMP

NOTE: This command is used to override safeguards built into the Oracle database and should not be used without a recommendation by Support.


 

In the example above PDB_TEMP is not a common tablespace which means it does not exist in some PDBs. Query the view CDB_TABLESPACES and assign the user to a proper common tablespace that is a common SYSAUX is a common table space, or do not specify a temporary tablespace when creating the user. Support does not recommend using the ALTER SESSION to bypass the Oracle best practices.

To create the new user the following steps can be used (replace username and password with the appropriate username and password)

create user C##USERNAME identified by PASSWORD container=all;
grant connect to C##USERNAME container=all

Then either enter a SYSDBA account username during the Oracle installer wizard or run permissions script with the new username.