| Oracle® Data Mining Administrator's Guide 11g Release 2 (11.2) Part Number E12217-02 |
|
|
View PDF |
| Oracle® Data Mining Administrator's Guide 11g Release 2 (11.2) Part Number E12217-02 |
|
|
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.
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 Application Developer'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 Application Developer's Guide for more information about attributes
Oracle Data Mining Application Developer's Guide for more information about ALL_MINING_MODEL_ATTRIBUTES
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 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
Comments can be used to associate descriptive information with a database objects. 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 '';
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.
The database initialization parameter AUDIT_TRAIL controls auditing capabilities in the database. To enable auditing, set AUDIT_TRAIL to DB, DB_EXTENDED, or OS.
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.
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]];
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 export or import 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 READ,WRITE ON DIRECTORY dmtest TO dmuser;
READ permission on the directory object is required for importing models. WRITE permission is required for exporting mining models.
Note:
For security reasons, use caution in granting permissions.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. (See "Directory Objects".)
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 examples
|
 Copyright © 2005, 2009, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|