| Oracle® Data Mining Administrator's Guide 11g Release 1 (11.1) Part Number B28130-01 |
|
|
View PDF |
| Oracle® Data Mining Administrator's Guide 11g Release 1 (11.1) Part Number B28130-01 |
|
|
View PDF |
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:
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 6-1 reveal information about mining models created by Oracle Data Mining.
See Also:
Oracle Database Reference for complete descriptions of the Data Mining views in the data dictionary.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".
Mining functions and algorithms are described in Oracle Data Mining Concepts.
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).
For more information on attributes, see Oracle Data Mining Application Developer's Guide.
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.
Model settings are documented in Oracle Database PL/SQL Packages and Types Reference. Many settings affect the behavior of an algorithm. These settings are described with the algorithms in Oracle Data Mining Concepts.
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 '';
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 theAUDIT ANY system privilege.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.
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]];
Specify BY SESSION if you want Oracle Database to write a single record for all operations of the same type on each mining model in the same session. Specify BY ACCESS if you want Oracle Database to write one record for each audited operation.
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 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.
Oracle Database Security Guide for a comprehensive discussion of database auditing.
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:
Database. When a DBA exports a full database using EXPDP, all data mining models in the database are exported. The IMPDP utility imports all the models with the other objects in the database.
Schema. When a DBA or an individual user exports a schema using EXPDP, all the data mining models in the schema are exported. Likewise, IMPDP imports all the models with the other objects in the schema.
Models Only. The Data Mining APIs contain utilities for exporting and importing mining models. You can select all the mining models in a schema or mining models that match specific criteria.
You cannot select individual models using EXPDP or IMPDP.
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:
Oracle Database Utilities for a complete discussion of Oracle Data Pump and the expdp and impdp utilities
Oracle Database PL/SQL Packages and Types Reference for detailed information about the export and import procedures in the DBMS_DATA_MINING package.
Oracle Data Mining Java API Reference for information about the export and import classes in the Oracle Data Mining Java API.
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.
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.
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.
The DBMS_DATA_MINING PL/SQL package contains these two procedures:
For more information about these procedures, refer to the Oracle Database PL/SQL Packages and Types Reference.
Oracle Database implements the industry-standard Java Data Mining (JDM) API Specification, which includes these two interfaces:
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
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.
In this example, the DMUSER schema contains two mining models. The DMUSER password is dmpassword.
SQL> CONNECT dmuser Enter password: dmpassword 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 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
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.
The following commands, executed as SYS, create a target schema DMUSER2. It uses the same default tablespace as the DMUSER schema (The tablespace is also called DMUSER), and it has the same privileges (granted by the DMSHGRANTS script).
SQL>CREATE USER dmuser2 IDENTIFIED BY dmuser2password
default tablespace dmuser
temporary tablespace temp
quota unlimited on dmuser;
SQL>@$ORACLE_HOME/rdbms/demo/dmshgrants sh 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
