remove dmsys befor upgrading to 11.2.0.X

How To Remove DMSYS Schema In 11G [ID 1497250.1]

Applies to:

Oracle Data Mining - Version 11.2.0.1 and later
Information in this document applies to any platform.

Goal

You are planning to upgrade your databases from 11.2.0.x to 11.2.0.3.
As per the pre-req checks, the DMSYS schema has to be removed prior to the upgrade as the ODM objects do not reside in a separate schema.

Fix

Schemas used by Oracle Data Mining:

ODM - 9i

DMSYS- 10G

SYS- 11G

In Oracle Database 11g, Data Mining models are implemented as data dictionary
objects in the SYS schema. The DMSYS schema no longer exists unless you upgraded from 10G.

If you plan to upgrade a database with the Data Mining option from 11.2.0.1 to 11.2.0.X, make
sure that the DMSYS schema does not exist in your 11.2.0.1 database. If it does, you
should drop the DMSYS schema and its associated objects from the database as follows:

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 SQL returns non-zero rows, create and run a SQL script as shown in the
following example:
SQL> SET HEAD OFF
SQL> SPOOL dir_path/DROP_DMSYS_SYNONYMS.SQL
SQL> SELECT 'Drop public synonym ' ||'"'||SYNONYM_NAME||'";' FROM DBA_SYNONYMS
WHERE TABLE_OWNER = 'DMSYS';
SQL> SPOOL OFF
SQL> @dir_path/DROP_DMSYS_SYNONYMS.SQL
SQL> EXIT;

If you upgrade a database from 10g to 11.2, all Data Mining metadata objects are
migrated from DMSYS to SYS. After the upgrade, when you determine that there is no
need to perform a downgrade, set the initialization parameter COMPATIBLE to 11.2 and
drop the DMSYS schema and its associated objects as described above.