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

Prior to upgrading my database, on running the utlu112i.sql all my checks passed except for the “DMSYS schema exists” message.

**********************************************************************

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.

Refer to the Oracle Data Mining Administration Guide for the
instructions on how to perform this task.

**********************************************************************


After making sure that the scehma is not being used I followed the Metalink document 1497250.1 to remove the DMSYS schema.

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';


After removing the schema, the public synonyms referring to the dropped objects in the DMSYS schema need to be removed.

set header off
spool dropDmsysSyn.sql
SELECT 'Drop public synonym ' ||'"'||SYNONYM_NAME||'";' FROM DBA_SYNONYMS
WHERE TABLE_OWNER = 'DMSYS';
spool off
@dropDmsysSyn.sql


The upgrade succeeded without any issues.

Other 12c Database Articles
Master Oracle 12c Database Administration – The Index
Upgrade Classic Extract to Integrated Capture

Other GoldenGate Articles
GoldenGate Guides – The Index
Oracle GoldenGate 12c: Twelve Advanced New Features