In this chapter, you will learn how to retrieve information about mining models from the data dictionary and perform various operations on mining models.
This chapter contains the following topics:
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 5-1 reveal information about mining models created by Oracle Data Mining.
You can query the ALL_MINING_MODELS data dictionary view to obtain information about all accessible mining model objects.
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 information in the COMMENTS column is generated by a SQL COMMENT statement. The comment provides descriptive information about the model. If no comment was generated by SQL COMMENT, the COMMENTS column is empty. See "Adding a Comment to a Mining Model".
See Also:
Oracle Data Mining Concepts for information about mining functions and algorithms
Oracle Data Mining User's Guide for more information about ALL_MINING_MODELS
You can query the ALL_MINING_MODEL_ATTRIBUTES data dictionary view to obtain information about all accessible mining model attributes.
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 returned by the GET_MODEL_DETAILS functions in the DBMS_DATA_MINING package.
See Also:
Oracle Data Mining User's Guide for more information about attributes
Oracle Data Mining User's Guide for more information about ALL_MINING_MODEL_ATTRIBUTES
Oracle Database PL/SQL Packages and Types Reference for details about the GET_MODEL_DETAILS functions
You can query the ALL_MINING_MODEL_SETTINGS data dictionary view to obtain information about all accessible mining model settings.
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 are specified in a settings table that is 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 more information about model settings
Oracle Data Mining User's Guide for more information about ALL_MINING_MODEL_SETTINGS
The DDL operations for mining models are described in Table 5-2. The operations are procedures in the DBMS_DATA_MINING PL/SQL package.
Table 5-2 DDL for Mining Models
| DDL | DBMS_DATA_MINING Procedure | Description |
|---|---|---|
|
Add cost matrix |
|
Adds a cost matrix to a classification model |
|
Alter reverse expression |
|
Alters the reverse transformation expression associated with a model |
|
Create model |
|
Creates a model |
|
Drop model |
|
Drops a model |
|
Remove cost matrix |
|
Removes the cost matrix from a classification model |
|
Rename model |
|
Renames a model |
See:
Oracle Database PL/SQL Packages and Types Reference for detailsComments 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 theCOMMENT 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 '';
The SQL auditing system is a DBA tool for tracking operations on database objects in a production environment. Data mining models can be tracked with the SQL auditing system.
Note:
To audit a mining model in another schema, you must have theAUDIT ANY system privilege.
DBAs use auditing to monitor activity in the database. Audit records can be generated to monitor various database operations. They include information such as the operation that was audited, the user performing the operation, and the date and time of the operation.
Audit records can be stored in either a data dictionary table, called the database audit trail, or in operating system files, called an operating system audit trail.
The database initialization parameter AUDIT_TRAIL controls auditing capabilities in the database. To enable auditing, set AUDIT_TRAIL to one of the following:
DB DB,EXTENDED OS XML XML,EXTENDED
Set AUDIT_TRAIL to NONE to prevent auditing information from being recorded. By default, AUDIT_TRAIL is set to DB.
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.
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]];
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]];
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:Oracle Database Security Guide for a comprehensive discussion of database auditing
Oracle Database SQL Language Reference for documentation of the AUDIT and NOAUDIT statements
Oracle Database Reference for documentation of the AUDIT_TRAIL initialization parameter and the data dictionary views for querying the database audit trail
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. EXPDP and IMPDP can be used to export/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 theexpdp and impdp utilitiesThe 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.
See Also:
Oracle Database PL/SQL Packages and Types Reference for detailed information about the export and import procedures in theDBMS_DATA_MINING packageYour options for exporting and importing mining models depend on your access rights in the database:
A DBA can use EXPDP to export a full database and IMPDP to import a full database. All mining models in the database are included in the export/import.
A DBA or individual user can use EXPDP to export a schema and IMPDP to import a schema. All mining models in the schema are included in the export/import.
A DBA or individual user can use DBMS_DATA_MINING.EXPORT_MODEL to export individual models and DBMS_DATA_MINING.IMPORT_MODEL to import individual models. These procedures can export/import a single mining model, all the mining models in a schema, or mining models that match specific criteria.
By default, EXPORT_MODEL and IMPORT_MODEL support export and import within the same schema. 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/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_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, 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 READ,WRITE ON DIRECTORY dmtest TO dmuser;
Note:
For security reasons, use caution in granting permissions.See Also:
Oracle Database SQL Language Reference for information about theCREATE DIRECTORY statementMining models are stored in the default tablespace of the mining model owner, or in a tablespace to which the owner has access. The tablespace must also exist in the target database, and the target user must have access to it. If the tablespace does not exist in the target database, you must create it before importing the models.
For example, if the models were created in schema DMUSER and the default tablespace for DMUSER is USERS, then the USERS tablespace must exist in the target database. You can create the USERS tablespace and grant access to a target user with appropriate tablespace quota as follows.
connect / as sysdba; create tablespace USERS datafile 'data_file_name' size 200M autoextend on; alter user target_user quota unlimited on USERS;
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.
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 Object for EXPORT_MODEL and IMPORT_MODEL".)
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 commands create the directory object mydir and export all models from dmuser to mydir.
SQL> CREATE OR REPLACE DIRECTORY mydir AS '/scratch/dumpfiles';
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
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');
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
You can export/import a single model, a list of models, or a group of models that share certain characteristics. For example, the following command exports two models, NMF_SH_SAMPLE and SVMR_SH_REGR_SAMPLE to the directory specified by directory object EXP_OUT.
SQL>execute dbms_data_mining.export_model ( 'models2_out', 'EXP_OUT',
'name in (''SVMR_SH_REGR_SAMPLE'')');
See:
Oracle Database PL/SQL Packages and Types Reference for more examplesThis functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2) Data Mining.
You can import a mining model represented in Predictive Model Markup Language (PMML). The model must be of type RegressionModel, either linear regression or binary logistic regression.
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.
See:
Oracle Database PL/SQL Packages and Types Reference for more information about PMML import