Oracle Pre-Upgrade Check Failed – DMSYS schema exists in the Database

Problem

Pre-upgrade check failed with following error when upgrade database 

The DMSYS schema exists in the database. Prior to performing an upgrade Oracle recommends that the DMSYS schema, and its associated objects be removed from the database.

Solution

Make sure the schema is not being used, then do the following 

SQL> CONNECT / AS SYSDBA;
SQL> DROP USER DMSYS CASCADE;
SQL> DELETE FROM SYS.EXPPKGACT$ WHERE SCHEMA = 'DMSYS'; 

SQL> SELECT COUNT(*) FROM DBA_SYNONYMS WHERE TABLE_OWNER = 'DMSYS';

If the above query return non-zero values then do the following to remove public synonyms referring DMSYS objects 

SQL> SET HEAD OFF
SQL> SPOOL drop_dmsys.sql
SQL> SELECT 'Drop public synonym ' ||'"'||SYNONYM_NAME||'";' FROM DBA_SYNONYMS WHERE TABLE_OWNER = 'DMSYS';
SQL> SPOOL OFF
SQL> @drop_dmsys.sql
SQL> EXIT;