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.