Skip Headers
Oracle® Data Mining Administrator's Guide
11g Release 1 (11.1)

B28130-04
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

7 Mining Model Schema Objects

In this chapter, you will learn how to find information about mining models in the data dictionary and how to perform various operations on mining models.

See Also:

Chapter 4, "Users and Privileges for Data Mining" for information about system and object privileges associated with mining model objects.

This chapter contains the following topics:

Obtaining Information from the Data Dictionary

Mining models are database schema objects. They can be queried in the ALL, DBA, and USER data dictionary views.

The data dictionary views in Table 7-1 reveal information about mining models created by Oracle Data Mining.

Table 7-1 Oracle Data Mining Data Dictionary Views

ALL_ Views DBA_ Views USER_ Views

ALL_MINING_MODELS

DBA_MINING_MODELS

USER_MINING_MODELS

ALL_MINING_MODEL_ATTRIBUTES

DBA_MINING_MODEL_ATTRIBUTES

USER_MINING_MODEL_ATTRIBUTES

ALL_MINING_MODEL_SETTINGS

DBA_MINING_MODEL_SETTINGS

USER_MINING_MODEL_SETTINGS


Obtaining Information about Mining Models

You can query the ALL_MINING_MODELS data dictionary view to obtain information about all accessible mining model objects. USER and DBA versions of this view are also available.

SQL> DESCRIBE all_mining_models
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 MODEL_NAME                                NOT NULL VARCHAR2(30)
 MINING_FUNCTION                                    VARCHAR2(30)
 ALGORITHM                                          VARCHAR2(30)
 CREATION_DATE                             NOT NULL DATE
 BUILD_DURATION                                     NUMBER
 MODEL_SIZE                                         NUMBER
 COMMENTS                                           VARCHAR2(4000)

The COMMENTS column contains comments created by SQL COMMENT, if they exist. See "Adding a Comment to a Mining Model".

See Also:

Oracle Data Mining Concepts for information about mining functions and algorithms

Oracle Data Mining Application Developer's Guide for more information about ALL_MINING_MODELS

Obtaining Information about Mining Model Attributes

You can query the ALL_MINING_MODEL_ATTRIBUTES data dictionary view to obtain information about all accessible mining model attributes. USER and DBA versions of this view are also available.

SQL> DESCRIBE all_mining_model_attributes
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 MODEL_NAME                                NOT NULL VARCHAR2(30)
 ATTRIBUTE_NAME                            NOT NULL VARCHAR2(30)
 ATTRIBUTE_TYPE                                     VARCHAR2(11)
 DATA_TYPE                                          VARCHAR2(12)
 DATA_LENGTH                                        NUMBER
 DATA_PRECISION                                     NUMBER
 DATA_SCALE                                         NUMBER
 USAGE_TYPE                                         VARCHAR2(8)
 TARGET                                             VARCHAR2(3)

Note:

The attributes listed in this data dictionary view are the physical columns in the build data that were used to construct the model. Some or all of these columns should be present for scoring. These data attributes are referred to as the model signature.

The term attribute is more accurately used to designate the numericals and categoricals derived from the data attributes for manipulation by the algorithm. These model attributes may or may not correspond to data attributes, depending on transformations and on whether or not the column is nested. The model attributes can be viewed in the model details (GET_MODEL_DETAILS functions).

See Also:

Oracle Data Mining Application Developer's Guide for more information about attributes

Oracle Data Mining Application Developer's Guide for more information about ALL_MINING_MODEL_ATTRIBUTES

Obtaining Information about Mining Model Settings

You can query the ALL_MINING_MODEL_SETTINGS data dictionary view to obtain information about all accessible mining model settings. USER and DBA versions of this view are also available.

SQL> DESCRIBE all_mining_model_settings
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 MODEL_NAME                                NOT NULL VARCHAR2(30)
 SETTING_NAME                              NOT NULL VARCHAR2(30)
 SETTING_VALUE                                      VARCHAR2(4000)
 SETTING_TYPE                                       VARCHAR2(7)

Model settings can be specified in a settings table used in the model build. The settings all have default values. The defaults are used when they are not overridden by settings specified in the settings table, or when there is no settings table.

See Also:

Oracle Database PL/SQL Packages and Types Reference for descriptions of model settings

Oracle Data Mining Concepts for details about algorithm settings

Oracle Data Mining Application Developer's Guide for more information about ALL_MINING_MODEL_SETTINGS

Adding a Comment to a Mining Model

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 MODEL system privilege.

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.

SQL> 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.

SQL> COLUMN comments FORMAT a22
SQL> 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:

SQL> COMMENT ON mining model dt_sh_clas_sample '';

Auditing Mining Models

You can use the SQL auditing system to track operations on data mining models.

Note:

To audit a mining model in another schema, you must have the AUDIT ANY system privilege.

Enabling Auditing in the Database

The database initialization parameter AUDIT_TRAIL controls auditing capabilities in the database. To enable auditing, set AUDIT_TRAIL to DB, DB_EXTENDED, or OS.

Set AUDIT_TRAIL to NONE to prevent auditing information from being recorded. By default, AUDIT_TRAIL is set to NONE.

Opening an Audit Trail on Mining Models

Use the SQL AUDIT statement to open an auditing trail on a data mining model.

AUDIT {operation|ALL} ON mining model schema_name.model_name;

You can track the following operations on mining models.

Audit Operation Description
AUDIT Generate an audit trail for a mining model
COMMENT Add a comment to a mining model
GRANT Give permission to a user to access the model
RENAME Change the name of the model
SELECT Apply the model or view its signature.

For example, this statement generates an audit trail for all GRANT operations on the model NB_SH_CLAS_SAMPLE in the DMUSER schema.

SQL> AUDIT GRANT ON mining model dmuser.nb_sh_clas_sample;

This statement generates an audit trail for all operations on the same model.

SQL> AUDIT GRANT,AUDIT,COMMENT,RENAME,SELECT 
            ON mining model dmuser.nb_sh_clas_sample;

You can refine the criteria for auditing with the following additional semantics.

AUDIT {operation|ALL} ON MINING MODEL schema_name.model_name
                                    [BY [SESSION|ACCESS]]
                                    [WHENEVER [NOT] SUCCESSFUL]];

Closing the Audit Trail

Use the NOAUDIT statement to stop one or more auditing operations previously enabled by the AUDIT statement.

NOAUDIT {operation| ALL} ON MINING MODEL model_name
                                      [WHENEVER [NOT] SUCCESSFUL]];

Viewing the Audit Trail

For each audited operation, Oracle Database produces an audit record containing:

  • The name of the user performing the operation

  • The type of operation

  • The object involved in the operation

  • The date and time of the operation

Several data dictionary views present auditing information. Some examples are:

  • DBA_AUDIT_OBJECT displays audit trail records for all objects in the database.

  • USER_AUDIT_OBJECT displays audit trail records for all objects accessible to the current user

  • DBA_OBJ_AUDIT_OPTS describes auditing options for all objects in the database.

  • USER_OBJ_AUDIT_OPTS describes auditing options for all objects owned by the current user.

Note:

The Oracle Database auditing system is a powerful, highly configurable tool for tracking operations on schema objects. Refer to the following manuals for more information:

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 production database). All methods for exporting and importing models are based in Oracle Data Pump technology.

Oracle Data Pump consists of two command-line clients and two PL/SQL APIs. The command-line clients, EXPDP and IMPDP, provide an easy-to-use interface to the Data Pump export and import utilities. The Data Mining APIs also use the Data Pump export and import utilities.

You can export and import models at different levels, depending on your access rights in the database:

The Data Pump export utility writes the tables and metadata that constitute a model to a dump file set, which consists of one or more files. The Data Pump import utility retrieves the tables and metadata from the dump file and restores them to the target database.

See Also:

Prerequisites

To export and import Data Mining models, you must have read and write access to a directory object, and you may need additional database permissions.

Directory Objects

A directory object is a logical name in the database for a physical directory on the host computer. Without read and write access to a directory object, you cannot access the host computer file system from within Oracle Database.

You must have the CREATE ANY DIRECTORY privilege to create directory objects.

The following SQL command creates, or re-creates if it already exists, a directory object named dmtest. The file system directory (in this example, C:\oracle\product\11.1.0\dmining) must already exist and have shared read/write access rights granted by the operating system.

CREATE OR REPLACE DIRECTORY dmtest AS 'c:\oracle\product\11.1.0\dmining';

This SQL command gives user dmuser both read and write access to dmtest.

GRANT ALL ON DIRECTORY dmtest TO dmuser;

For more information about creating database directories, refer to the CREATE DIRECTORY and GRANT commands in the Oracle Database SQL Language Reference.

Additional Database Privileges

You may need special privileges in the database to take full advantage of all Data Pump features, such as importing models and other objects 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 models from 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.

PL/SQL APIs for Exporting and Importing Models

The DBMS_DATA_MINING PL/SQL package contains these two procedures:

  • EXPORT_MODEL

  • IMPORT_MODEL

For more information about these procedures, refer to the Oracle Database PL/SQL Packages and Types Reference.

Java APIs for Exporting and Importing Models

Oracle Database implements the industry-standard Java Data Mining (JDM) API Specification, which includes these two interfaces:

  • javax.datamining.task.ExportTask

  • javax.datamining.task.ImportTask

For more information about the standard JDM API, refer to the Java Help for the JSR-73 Specification, which is available on the Oracle Technology Network at

http://www.oracle.com/technology/products/bi/odm/JSR-73/index.html

Tables Created By Exporting and Importing Models

The Data Mining export and import utilities create tables in the user's schema that are for internal use only:

  • DM$P_MODEL_EXPIMP_TEMP. Used for internal purposes during export and import, and provides a job history.

  • DM$P_MODEL_IMPORT_TEMP. Used only for internal purposes during import.

  • DM$P_MODEL_TABKEY_TEMP. Used only for internal purposes during export and import.

Do not alter these tables. However, you may drop them when no export or import job is running. The utilities will re-create them for the next job.

Example: Exporting and Importing Models

This example creates a dump file containing two models and imports the models from the dump file.

This example was generated on a Linux system. The directory object mydir identifies the path /scratch/dumpfiles. (See "Directory Objects".)

Exporting Models from the DMUSER Schema

In this example, the dmuser schema contains two mining models.

SQL> CONNECT dmuser
Enter password: password

SQL> SELECT model_name FROM user_mining_models;
 
MODEL_NAME
------------------------------
GLMR_SH_REGR_SAMPLE
SVMC_SH_CLAS_SAMPLE

The following command exports all models from dmuser to the directory identified by the directory object mydir.

SQL> EXECUTE dbms_data_mining.export_model('all_dmuser_models.dmp', 'mydir');

An export or import creates a log file in the same directory as the dump file. Error messages are returned to the current output device (such as the screen), and the log file may provide additional information.

This sample export created two files in the mydir directory:

  • A dump file named ALL_DMUSER_MODELS01.DMP (note the 2-digit suffix added to the name)

  • A log file with the name DMUSER_EXP_920.LOG

For detailed information about the default names of files, see Oracle Database PL/SQL Packages and Types Reference.

You can view the log file using a system command or editor. You must know the path of the physical directory in order to locate the file.

DMUSER_EXP_920.LOG lists the two exported mining models and supporting objects .

Starting "DMUSER"."DMUSER_exp_17":  DM_EXPIMP_JOB_ID=17
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 1.062 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
. . exported "DMUSER"."DM$PIGLMR_SH_REGR_SAMPLE"         7.085 KB      14 rows
. . exported "DMUSER"."DM$PISVMC_SH_CLAS_SAMPLE"         7.507 KB      17 rows
. . exported "DMUSER"."DM$PCGLMR_SH_REGR_SAMPLE"         53.27 KB    2278 rows
. . exported "DMUSER"."DM$PAGLMR_SH_REGR_SAMPLE"         5.796 KB      24 rows
. . exported "DMUSER"."DM$PBGLMR_SH_REGR_SAMPLE"         15.76 KB      67 rows
. . exported "DMUSER"."DM$PDGLMR_SH_REGR_SAMPLE"             8 KB      66 rows
. . exported "DMUSER"."DM$PDSVMC_SH_CLAS_SAMPLE"         9.023 KB      88 rows
. . exported "DMUSER"."DM$PFGLMR_SH_REGR_SAMPLE"         5.656 KB      10 rows
. . exported "DMUSER"."DM$POSVMC_SH_CLAS_SAMPLE"         5.320 KB       8 rows
. . exported "DMUSER"."DM$PXSVMC_SH_CLAS_SAMPLE"         7.265 KB      77 rows
. . exported "DMUSER"."DM$PZSVMC_SH_CLAS_SAMPLE"         6.164 KB       1 rows
. . exported "DMUSER"."DM$P_MODEL_EXPIMP_TEMP"           5.921 KB       2 rows
Master table "DMUSER"."DMUSER_exp_17" successfully loaded/unloaded
******************************************************************************
Dump file set for DMUSER.DMUSER_exp_17 is:
  /scratch/dumpfiles/all_dmuser_models01.dmp
Job "DMUSER"."DMUSER_exp_17" successfully completed at 10:30:19

Importing Models Into the Same Schema

The exported models still exist in DMUSER. In this example, we drop the models before importing from the dump file. An import will not overwrite an existing model with the same name.

SQL> EXECUTE dbms_data_mining.drop_model('GLMR_SH_REGR_SAMPLE');
SQL> EXECUTE dbms_data_mining.drop_model('SVMC_SH_CLAS_SAMPLE');

The following command restores all models from the dump file to the DMUSER schema.

SQL> EXECUTE dbms_data_mining.import_model('all_dmuser_models01.dmp', 'mydir');

Importing Models Into a Different Schema

A user with the necessary privileges can load the models from a dump file into a different schema. The target schema must have the same permissions and have access to the same tablespace as the schema from which the models were exported.

CREATE USER dmuser2 IDENTIFIED BY password
        default tablespace dmuser
        temporary tablespace temp
        quota unlimited on dmuser;

@ $ORACLE_HOME/RDBMS/demo/dmshgrants dmuser2

The import command, also executed as SYS, loads the two models into the DMUSER2 schema.

SQL> EXECUTE dbms_data_mining.import_model('all_dmuser_models01.dmp', 'mydir', null, null, null, 'todmuser2', 'dmuser:dmuser2');

A parameter specifies TODMUSER2.LOG as the name of the log file; the .LOG extension is added automatically to the name. The log file shows the names of the imported models and supporting metadata.

Master table "SYS"."todmusr2" successfully loaded/unloaded
Starting "SYS"."todmusr4":  DM_EXPIMP_JOB_ID=21
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "DMUSER2"."DM$PIGLMR_SH_REGR_SAMPLE"        7.085 KB      14 rows
. . imported "DMUSER2"."DM$PISVMC_SH_CLAS_SAMPLE"        7.507 KB      17 rows
. . imported "DMUSER2"."DM$PCGLMR_SH_REGR_SAMPLE"        53.27 KB    2278 rows
. . imported "DMUSER2"."DM$PAGLMR_SH_REGR_SAMPLE"        5.796 KB      24 rows
. . imported "DMUSER2"."DM$PBGLMR_SH_REGR_SAMPLE"        15.76 KB      67 rows
. . imported "DMUSER2"."DM$PDGLMR_SH_REGR_SAMPLE"            8 KB      66 rows
. . imported "DMUSER2"."DM$PDSVMC_SH_CLAS_SAMPLE"        9.023 KB      88 rows
. . imported "DMUSER2"."DM$PFGLMR_SH_REGR_SAMPLE"        5.656 KB      10 rows
. . imported "DMUSER2"."DM$POSVMC_SH_CLAS_SAMPLE"        5.320 KB       8 rows
. . imported "DMUSER2"."DM$PXSVMC_SH_CLAS_SAMPLE"        7.265 KB      77 rows
. . imported "DMUSER2"."DM$PZSVMC_SH_CLAS_SAMPLE"        6.164 KB       1 rows
. . imported "DMUSER2"."DM$P_MODEL_EXPIMP_TEMP"          5.921 KB       2 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/POST_INSTANCE/PROCACT_INSTANCE
Job "SYS"."todmusr2" successfully completed at 11:15:26