Skip Headers
Oracle® Data Mining User's Guide
12c Release 1 (12.1)

E17693-15
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

8 Administrative Tasks for Oracle Data Mining

This chapter explains how to perform administrative tasks related to Oracle Data Mining. The chapter contains the following topics:

Installing and Configuring a Database for Data Mining

This section contains the following topics:

About Installation

Oracle Data Mining is a component of the Oracle Advanced Analytics option to Oracle Database Enterprise Edition. To install Oracle Database, follow the installation instructions for your platform. Choose a Data Warehousing configuration during the installation.

Oracle Data Miner, the graphical user interface to Oracle Data Mining, is an extension to Oracle SQL Developer. Instructions for downloading SQL Developer and installing the Data Miner repository are available on the Oracle Technology Network:

http://www.oracle.com/pls/topic/lookup?ctx=db121&id=datminGUI

To perform data mining activities, you must be able to log on to the Oracle database, and your user ID must have the database privileges described in Example 8-7, "Privileges Required for Data Mining".

See Also:

Installing and Upgrading page of the Oracle Database online documentation library for your platform-specific installation instructions: http://www.oracle.com/pls/db121/homepage

Enabling or Disabling a Database Option

The Oracle Advanced Analytics option is enabled by default during installation of Oracle Database Enterprise Edition. After installation, you can use the command-line utility chopt to enable or disable a database option. For instructions, see "Enabling or Disabling Database Options" in the installation guide for your platform. For example:

Database Tuning Considerations for Data Mining

DBAs managing production databases that support Oracle Data Mining should follow standard administrative practices as described in Oracle Database Administrator's Guide.

Building data mining models and batch scoring of mining models tend to put a DSS-like workload on the system. Single-row scoring tends to put an OLTP-like workload on the system. (Scoring operations are discussed in Chapter 6 of this manual.)

Database memory management can have a major impact on data mining. The correct sizing of PGA memory is very important for model building, complex queries, and batch scoring. From a data mining perspective, the SGA is generally less of a concern. However, the SGA should be sized to accommodate real-time scoring, which loads models into the shared cursor in the SGA. In most cases, you can configure the database to manage memory automatically. To do so, specify the total maximum memory size in the tuning parameter MEMORY_TARGET. With automatic memory management, Oracle Database dynamically exchanges memory between the SGA and the instance PGA as needed to meet processing demands.

Most data mining algorithms can take advantage of parallel execution when it is enabled in the database. Parameters in INIT.ORA control the behavior of parallel execution.

See Also:

Upgrading or Downgrading Oracle Data Mining

This section contains the following topics:

Pre-Upgrade Steps

Before upgrading, you must drop any data mining models that were created in Java and any mining activities that were created in Oracle Data Miner Classic (the earlier version of Oracle Data Miner).

Important:

In Oracle Database 12c , Oracle Data Mining does not support a Java API, and Oracle Data Miner Classic cannot run against Oracle Database 12c databases.

Dropping Models Created in Java

If your 10g or 11g database contains models created in Java, use the DBMS_DATA_MINING.DROP_MODEL routine to drop the models before upgrading the database.

Dropping Mining Activities Created in Oracle Data Miner Classic

If your database contains mining activities from Oracle Data Miner Classic, delete the mining activities and drop the repository before upgrading the database. Follow these steps:

  1. Use the Data Miner Classic user interface to delete the mining activities.

  2. In SQL*Plus or SQL Developer, drop these tables:

    DM4J$ACTIVITIES
    DM4J$RESULTS
    DM4J$TRANSFORMS
    

    and these views:

    DM4J$MODEL_RESULTS_V
    DM4J$RESULTS_STATE_V
    

There should be no tables or views with the prefix DM4J$ in any schema in the database after you complete these steps.

Upgrading Oracle Data Mining

After you complete the Pre-Upgrade Steps, all models and mining metadata are fully integrated with the Oracle Database upgrade process — whether you are upgrading from 11g or from 10g releases.

Upgraded models continue to work as they did in prior releases. Both upgraded models and new models that you create in the upgraded environment can make use of the new mining functionality introduced in the new release.

To upgrade a database, you can use Database Upgrade Assistant (DBUA) or you can perform a manual upgrade using export/import utilities.

See Also:

Oracle Database Upgrade Guide for complete database upgrade instructions

Using Database Upgrade Assistant to Upgrade Oracle Data Mining

Oracle Database Upgrade Assistant provides a graphical user interface that guides you interactively through the upgrade process.

On Windows platforms, follow these steps to start the Upgrade Assistant:

  1. Go to the Windows Start menu and choose the Oracle home directory.

  2. Choose the Configuration and Migration Tools menu.

  3. Launch the Upgrade Assistant.

On Linux platforms, run the DBUA utility to upgrade Oracle Database.

Upgrading from Release 10g

In Oracle Data Mining 10g, data mining metadata and PL/SQL packages are stored in the DMSYS schema. In Oracle Data Mining 11g and 12c, DMSYS no longer exists; data mining metadata objects are stored in SYS.

When Oracle Database 10g is upgraded to 12c, all data mining metadata objects and PL/SQL packages are migrated from DMSYS to SYS. The DMSYS schema and its associated objects are removed after a successful migration. When DMSYS is removed, the SYS.DBA_REGISTRY view no longer lists Oracle Data Mining as a component.

After upgrading to Oracle Database 12c, you can no longer switch to the Data Mining Scoring Engine (DMSE). The Scoring Engine does not exist in Oracle Database 11g or 12c.

Upgrading from Release 11g

If you upgrade Oracle Database 11g to Oracle Database 12c, and the database was previously upgraded from Oracle Database 10g, the DMSYS schema may still be present. If the upgrade process detects DMSYS, it displays a warning message and drops DMSYS during the upgrade.

Using Export/Import to Upgrade Data Mining Models

If you wish, you can use a less automated approach to upgrading data mining models. You can export the models created in a previous version of Oracle Database and import them into an instance of Oracle Database 12c.

Note:

Do not import data mining models that were created in Java. They are not supported in Oracle Database 12c.
Export/Import Release 10g Data Mining Models

To export models from an instance of Oracle Database 10g to a dump file, follow the instructions in "Exporting and Importing Mining Models".Before importing the models from the dump file, run the DMEIDMSYS script to create the DMSYS schema in Oracle Database 12c.

SQL>CONNECT / as sysdba;
SQL>@ORACLE_HOME\RDBMS\admin\dmeidmsys.sql
SQL>EXIT;

Note:

The TEMP tablespace must already exist in the Oracle Database 12g database. The DMEIDMSYS script uses the TEMP and SYSAUX tablespaces to create the DMSYS schema.

To import the dump file into the Oracle Database 12c database:

%ORACLE_HOME\bin\impdp system\<password> 
       dumpfile=<dumpfile_name> 
       directory=<directory_name> 
       logfile=<logfile_name> .....
SQL>CONNECT / as sysdba;
SQL>EXECUTE dmp_sys.upgrade_models();
SQL>ALTER SYSTEM FLUSH SHARED_POOL;
SQL>ALTER SYSTEM FLUSH BUFFER_CACHE;
SQL>EXIT;

The upgrade_models script migrates all data mining metadata objects and PL/SQL packages from DMSYS to SYS and then drops DMSYS before upgrading the models.

Export/Import Release 11g Data Mining Models

To export models from an instance of Oracle Database 11g to a dump file, follow the instructions in "Exporting and Importing Mining Models".

Note:

Do not import data mining models that were created in Java. They are not supported in Oracle Database 12c.

To import the dump file into the Oracle Database 12c database:

%ORACLE_HOME\bin\impdp system\<password> 
       dumpfile=<dumpfile_name> 
       directory=<directory_name> 
       logfile=<logfile_name> .....
SQL>CONNECT / as sysdba;
SQL>EXECUTE dmp_sys.upgrade_models();
SQL>ALTER SYSTEM flush shared_pool;
SQL>ALTER SYSTEM flush buffer_cache;
SQL>EXIT;

Post Upgrade Steps

After upgrading the database, check the DBA_MINING_MODELS view in the upgraded database. The newly upgraded mining models should be listed in this view.

After you have verified the upgrade and confirmed that there will be no need to downgrade, you should set the initialization parameter COMPATIBLE to 12.1.

Important:

The CREATE MINING MODEL privilege must be granted to Data Mining user accounts that will be used to create mining models. Refer to "Creating a Data Mining User" and "Controlling Access to Mining Models and Data" for more information.

Downgrading Oracle Data Mining

Before downgrading the Oracle Database 12c database back to the previous version, ensure that no Singular Value Decomposition models or Expectation Maximization models are present. These algorithms are only available in Oracle Database 12c. Use the DBMS_DATA_MINING.DROP_MODEL routine to drop these models before downgrading. If you do not do this, the database downgrade process will be aborted.

Issue the following SQL statement in SYS to verify the downgrade:

SQL>SELECT o.name FROM sys.model$ m, sys.obj$ o 
                  WHERE m.obj#=o.obj# AND m.version=2;

Exporting and Importing Mining Models

You can export data mining models to flat files to back up work in progress or to move models to a different instance of Oracle Database Enterprise Edition (such as from a development database to a test database). All methods for exporting and importing models are based on Oracle Data Pump technology.

The DBMS_DATA_MINING package includes the EXPORT_MODEL and IMPORT_MODEL procedures for exporting and importing individual mining models. EXPORT_MODEL and IMPORT_MODEL use the export and import facilities of Oracle Data Pump.

This section contains the following topics

About Oracle Data Pump

Oracle Data Pump consists of two command-line clients and two PL/SQL packages. The command-line clients, expdp and impdp, provide an easy-to-use interface to the Data Pump export and import utilities. You can use expdp and impdp to export and import entire schemas or databases.

The Data Pump export utility writes the schema objects, including the tables and metadata that constitute mining models, to a dump file set. The Data Pump import utility retrieves the schema objects, including the model tables and metadata, from the dump file set and restores them in the target database.

expdp and impdp cannot be used to export/import individual mining models.

See Also:

Oracle Database Utilities for information about Oracle Data Pump and the expdp and impdp utilities

Options for Exporting and Importing Mining Models

Options for exporting and importing mining models are described in Table 8-1.

Table 8-1 Export and Import Options for Oracle Data Mining

Task Description

Export or import a full database

(DBA only) Use expdp to export a full database and impdp to import a full database. All mining models in the database are included.

Export or import a schema

Use expdp to export a schema and impdp to import a schema. All mining models in the schema are included.

Export or import individual models within a database

Use DBMS_DATA_MINING.EXPORT_MODEL to export individual models and DBMS_DATA_MINING.IMPORT_MODEL to import individual models. These procedures can export and import a single mining model, all mining models, or mining models that match specific criteria.

By default, IMPORT_MODEL imports models back into the schema from which they were exported. You can specify the schema_remap parameter to import models into a different schema. You can specify tablespace_remap with schema_remap to import models into a schema that uses a different tablespace.

You may need special privileges in the database to import models into a different schema. These privileges are granted by the EXP_FULL_DATABASE and IMP_FULL_DATABASE roles, which are only available to privileged users (such as SYS or a user with the DBA role). You do not need these roles to export or import models within your own schema.

To import models, you must have the same database privileges as the user who created the dump file set. Otherwise, a DBA with full system privileges must import the models.

Export or import individual models to or from a remote database

Use a database link to export individual models to a remote database or import individual models from a remote database. A database link is a schema object in one database that enables access to objects in a different database. The link must be created before you execute EXPORT_MODEL or IMPORT_MODEL.

To create a private database link, you must have the CREATE DATABASE LINK system privilege. To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege. Also, you must have the CREATE SESSION system privilege on the remote Oracle Database. Oracle Net must be installed on both the local and remote Oracle Databases.


See Also:

Directory Objects for EXPORT_MODEL and IMPORT_MODEL

EXPORT_MODEL and IMPORT_MODEL use a directory object to identify the location of the dump file set. A directory object is a logical name in the database for a physical directory on the host computer.

To export data mining models, you must have write access to the directory object and to the file system directory that it represents. To import data mining models, you must have read access to the directory object and to the file system directory. Also, the database itself must have access to file system directory.You must have the CREATE ANY DIRECTORY privilege to create directory objects.

The following SQL command creates a directory object named dmuser_dir. The file system directory that it represents must already exist and have shared read/write access rights granted by the operating system.

CREATE OR REPLACE DIRECTORY dmuser_dir AS '/dm_path/dm_mining';

The following SQL command gives user dmuser both read and write access to dmuser_dir.

GRANT READ,WRITE ON DIRECTORY dmuser_dir TO dmuser;

See Also:

CREATE DIRECTORY in Oracle Database SQL Language Reference

Using EXPORT_MODEL and IMPORT_MODEL

The examples in this section illustrate various export and import scenarios with EXPORT_MODEL and IMPORT_MODEL. The examples use the directory object dmdir shown in Example 8-1 and two schemas, dm1 and dm2. Both schemas have data mining privileges. dm1 has two models. dm2 has one model.

SELECT owner, model_name, mining_function, algorithm FROM all_mining_models;
 
OWNER      MODEL_NAME           MINING_FUNCTION      ALGORITHM
---------- -------------------- -------------------- --------------------------
DM1        EM_SH_CLUS_SAMPLE    CLUSTERING           EXPECTATION_MAXIMIZATION
DM1        DT_SH_CLAS_SAMPLE    CLASSIFICATION       DECISION_TREE
DM2        SVD_SH_SAMPLE        FEATURE_EXTRACTION   SINGULAR_VALUE_DECOMP

Example 8-1 Creating the Directory Object

-- connect as system user
CREATE OR REPLACE DIRECTORY dmdir AS '/scratch/dmuser/expimp';
GRANT READ,WRITE ON DIRECTORY dmdir TO dm1;
GRANT READ,WRITE ON DIRECTORY dmdir TO dm2;
SELECT * FROM all_directories WHERE directory_name IN 'DMDIR';

OWNER      DIRECTORY_NAME             DIRECTORY_PATH
---------- -------------------------- ----------------------------------------
SYS        DMDIR                      /scratch/dmuser/expimp

Example 8-2 Exporting All Models From DM1

-- connect as dm1
BEGIN
  dbms_data_mining.export_model (
                   filename =>   'all_dm1',
                   directory =>  'dmdir');
END;
/
 

A log file and a dump file are created in /scratch/dmuser/expimp, the physical directory associated with dmdir. The name of the log file is dm1_exp_11.log. The name of the dump file is all_dm101.dmp.

Example 8-3 Importing the Models Back Into DM1

The models that were exported in Example 8-2 still exist in dm1. Since an import does not overwrite models with the same name, you must drop the models before importing them back into the same schema.

BEGIN
  dbms_data_mining.drop_model('EM_SH_CLUS_SAMPLE');
  dbms_data_mining.drop_model('DT_SH_CLAS_SAMPLE');
  dbms_data_mining.import_model(
                   filename => 'all_dm101.dmp',
                   directory => 'DMDIR');
END;
/
SELECT model_name FROM user_mining_models;
 
MODEL_NAME
------------------------------
DT_SH_CLAS_SAMPLE
EM_SH_CLUS_SAMPLE

Example 8-4 Importing Models Into a Different Schema

In this example, the models that were exported from dm1 in Example 8-2 are imported into dm2. The dm1 schema uses the example tablespace; the dm2 schema uses the sysaux tablespace.

-- CONNECT as sysdba 
BEGIN
  dbms_data_mining.import_model (
                   filename => 'all_d101.dmp',
                   directory => 'DMDIR',
                   schema_remap => 'DM1:DM2',
                   tablespace_remap => 'EXAMPLE:SYSAUX');
END;
/
-- CONNECT as dm2
SELECT model_name from user_mining_models;
 
MODEL_NAME
--------------------------------------------------------------------------------
SVD_SH_SAMPLE
EM_SH_CLUS_SAMPLE
DT_SH_CLAS_SAMPLE

Example 8-5 Exporting Specific Models

You can export a single model, a list of models, or a group of models that share certain characteristics.

-- Export the model named dt_sh_clas_sample
EXECUTE dbms_data_mining.export_model (
             filename => 'one_model', 
             directory =>'DMDIR',
             model_filter => 'name in (''DT_SH_CLAS_SAMPLE'')');
-- one_model01.dmp and dm1_exp_37.log are created in /scratch/dmuser/expimp

-- Export Decision Tree models
EXECUTE dbms_data_mining.export_model(
             filename => 'algo_models',
             directory => 'DMDIR',
             model_filter => 'ALGORITHM_NAME IN (''DECISION_TREE'')');
-- algo_model01.dmp and dm1_exp_410.log are created in /scratch/dmuser/expimp

-- Export clustering models 
EXECUTE dbms_data_mining.export_model(
             filename =>'func_models',
             directory => 'DMDIR',
             model_filter => 'FUNCTION_NAME = ''CLUSTERING''');
-- func_model01.dmp and dm1_exp_513.log are created in /scratch/dmuser/expimp

Importing From PMML

PMML is an XML-based standard specified by the Data Mining Group (http://www.dmg.org). Applications that are PMML-compliant can deploy PMML-compliant models that were created by any vendor. Oracle Data Mining supports the core features of PMML 3.1 for regression models.

You can import regression models represented in Predictive Model Markup Language (PMML). The models must be of type RegressionModel, either linear regression or binary logistic regression.

See:

Oracle Database PL/SQL Packages and Types Reference for more information about PMML import

Controlling Access to Mining Models and Data

This section contains the following topics:

Creating a Data Mining User

A Data Mining user is a database user account that has privileges for performing data mining activities. Example 8-6 shows how to create a database user. Example 8-7 shows how to assign data mining privileges to the user.

Example 8-6 Creating a Database User in SQL*Plus

  1. Log in to SQL*Plus with system privileges.

        Enter user-name: sys as sysdba
        Enter password: password
    
  2. To create a user named dmuser, type these commands. Specify a password of your choosing.

    CREATE USER dmuser IDENTIFIED BY password
           DEFAULT TABLESPACE USERS
           TEMPORARY TABLESPACE TEMP
           QUOTA UNLIMITED ON USERS;
    Commit;
    

    The USERS and TEMP tablespace are included in the pre-configured database that Oracle ships with the database media. USERS is used mostly by demo users; it would be appropriate for running the sample programs described in Appendix A. TEMP is the temporary tablespace that is shared by most database users.

    Note:

    Tablespaces for Data Mining users should be assigned according to standard DBA practices, depending on system load and system resources.
  3. To login as dmuser, type the following.

    CONNECT dmuser
    Enter password: password
    

Note:

To create a user for the Data Mining sample programs, you must run two configuration scripts as described in Appendix A.

See Also:

Oracle Database SQL Language Reference for the complete syntax of the CREATE USER statement

Assigning Privileges for Data Mining

You must have the CREATE MINING MODEL privilege to create models in your own schema. You can perform any operation on models that you own. This includes applying the model, adding a cost matrix, renaming the model, and dropping the model.

The GRANT statements in Example 8-7 assign a set of basic data mining privileges to the dmuser account. Some of these privileges may not be required for all mining activities, however it is prudent to grant them all as a group.

Example 8-7 Privileges Required for Data Mining

GRANT CREATE MINING MODEL TO dmuser;
GRANT CREATE SESSION TO dmuser;
GRANT CREATE TABLE TO dmuser;
GRANT CREATE VIEW TO dmuser;
GRANT EXECUTE ON CTXSYS.CTX_DDL TO dmuser;

SELECT privileges are required for data that is not in your schema. For example, the following statement grants SELECT access to the sh.customers table.

GRANT SELECT ON sh.customers TO dmuser;

Additional system and object privileges are required for enabling or restricting specific mining activities.

System Privileges for Data Mining

A system privilege confers the right to perform a particular action in the database or to perform an action on a type of schema objects. For example, the privileges to create tablespaces and to delete the rows of any table in a database are system privileges.

You can perform specific operations on mining models in other schemas if you have the appropriate system privileges. For example, CREATE ANY MINING MODEL enables you to create models in other schemas. SELECT ANY MINING MODEL enables you to apply models that reside in other schemas. You can add comments to models if you have the COMMENT ANY MINING MODEL privilege.

To grant a system privilege, you must either have been granted the system privilege with the ADMIN OPTION or have been granted the GRANT ANY PRIVILEGE system privilege.

The system privileges listed in Table 8-2 control operations on mining models.

Table 8-2 System Privileges for Data Mining

System Privilege Allows you to....

CREATE MINING MODEL

Create mining models in your own schema.

CREATE ANY MINING MODEL

Create mining models in any schema.

ALTER ANY MINING MODEL

Change the name or cost matrix of any mining model in any schema.

DROP ANY MINING MODEL

Drop any mining model in any schema.

SELECT ANY MINING MODEL

Apply a mining model in any schema, also view model details in any schema.

COMMENT ANY MINING MODEL

Add a comment to any mining model in any schema. (See "Adding a Comment to a Mining Model".)

AUDIT_ADMIN role

Generate an audit trail for any mining model in any schema. (See Oracle Database Security Guide for details.)


Example 8-8 Grant System Privileges for Data Mining

The following statements allow dmuser to score data and view model details in any schema as long as SELECT access has been granted to the data. However, dmuser can only create models in the dmuser schema.

GRANT CREATE MINING MODEL TO dmuser;
GRANT SELECT ANY MINING MODEL TO dmuser;

The following statement revokes the privilege of scoring or viewing model details in other schemas. When this statement is executed, dmuser can only perform data mining activities in the dmuser schema.

REVOKE SELECT ANY MINING MODEL FROM dmuser;

Object Privileges for Mining Models

An object privilege confers the right to perform a particular action on a specific schema object. For example, the privilege to delete rows from the SH.PRODUCTS table is an example of an object privilege.

You automatically have all object privileges for schema objects in your own schema. You can grant object privilege on objects in your own schema to other users or roles.

The object privileges listed in Table 8-3 control operations on specific mining models.

Table 8-3 Object Privileges for Mining Models

Object Privilege Allows you to....

ALTER MINING MODEL

Change the name or cost matrix of the specified mining model object.

SELECT MINING MODEL

Apply the specified mining model object and view its model details.


Example 8-9 Grant Object Privileges on Mining Models

The following statements allow dmuser to apply the model testmodel to the sales table, specifying different cost matrixes with each apply. The user dmuser can also rename the model testmodel. The testmodel model and sales table are in the sh schema, not in the dmuser schema.

GRANT SELECT ON MINING MODEL sh.testmodel TO dmuser;
GRANT ALTER ON MINING MODEL sh.testmodel TO dmuser;
GRANT SELECT ON sh.sales TO dmuser;

The following statement prevents dmuser from renaming or changing the cost matrix of testmodel. However, dmuser can still apply testmodel to the sales table.

REVOKE ALTER ON MINING MODEL sh.testmodel FROM dmuser;

Auditing and Adding Comments to Mining Models

Mining model objects support SQL COMMENT and AUDIT statements.

Adding a Comment to a Mining Model

Comments can be used to associate descriptive information with a database object. You can associate a comment with a mining model using a SQL COMMENT statement.

COMMENT ON MINING MODEL schema_name.model_name IS string;

Note:

To add a comment to a model in another schema, you must have the COMMENT ANY MINING MODEL system privilege.

See Table 8-2, "System Privileges for Data Mining".

To drop a comment, set it to the empty '' string.

The following statement adds a comment to the model DT_SH_CLAS_SAMPLE in your own schema.

COMMENT ON MINING MODEL dt_sh_clas_sample IS
           'Decision Tree model predicts promotion response';

You can view the comment by querying the catalog view USER_MINING_MODELS.

SELECT model_name, mining_function, algorithm, comments FROM user_mining_models; 

MODEL_NAME        MINING_FUNCTION  ALGORITHM      COMMENTS
----------------- ---------------- -------------- -----------------------------------------------
DT_SH_CLAS_SAMPLE CLASSIFICATION   DECISION_TREE  Decision Tree model predicts promotion response 

To drop this comment from the database, issue the following statement:

COMMENT ON MINING MODEL dt_sh_clas_sample '';

See Also:

Oracle Database SQL Language Reference for details about SQL COMMENT statements

Auditing Mining Models

The Oracle Database auditing system is a powerful, highly configurable tool for tracking operations on schema objects in a production environment. The auditing system can be used to track operations on data mining models.

Note:

To audit mining models, you must have the AUDIT_ADMIN role.

Unified auditing is documented in Oracle Database Security Guide. However, the full unified auditing system is not enabled by default. Instructions for migrating to unified auditing are provided in Oracle Database Upgrade Guide.

See Also: