28 Administrative Tasks for Oracle Data Mining
Explains how to perform administrative tasks related to Oracle Data Mining.
28.1 Installing and Configuring a Database for Data Mining
Learn how to install and configure a database for Data Mining.
28.1.1 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.
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 28-7.
See Also:
Install and Upgrade page of the Oracle Database online documentation library for your platform-specific installation instructions: http://docs.oracle.com/en/database/database.html
28.1.2 Enabling or Disabling a Database Option
Learn how you can enable or disable Oracle Advanced Analytics option after the installation.
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 and Disabling Database Options After Installation" in the installation guide for your platform.
28.1.3 Database Tuning Considerations for Data Mining
Understand the Database tuning considerations for Data Mining.
DBAs managing production databases that support Oracle Data Mining must 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.
Database memory management can have a major impact on data mining. The correct sizing of Program Global Area (PGA) memory is very important for model building, complex queries, and batch scoring. From a data mining perspective, the System Global Area (SGA) is generally less of a concern. However, the SGA must 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.
28.2 Upgrading or Downgrading Oracle Data Mining
Understand how to upgrade and downgrade Oracle Data Mining.
28.2.1 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).
Caution:
In Oracle Database 12c, Oracle Data Mining does not support a Java API, and Oracle Data Miner Classic cannot run against Oracle Database 12c .
28.2.1.1 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.
28.2.1.2 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:
-
Use the Data Miner Classic user interface to delete the mining activities.
-
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 must be no tables or views with the prefix DM4J$ in any schema in the database after you complete these steps.
28.2.2 Upgrading Oracle Data Mining
Learn how to upgrade 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.
Related Topics
28.2.2.1 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:
-
Go to the Windows Start menu and choose the Oracle home directory.
-
Choose the Configuration and Migration Tools menu.
-
Launch the Upgrade Assistant.
On Linux platforms, run the DBUA utility to upgrade Oracle Database.
28.2.2.1.1 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.
28.2.2.2 Using Export/Import to Upgrade Data Mining Models
If required, you can 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.
Caution:
Do not import data mining models that were created in Java. They are not supported in Oracle Database 12c.
28.2.2.2.1 Export/Import Release 10g Data Mining Models
Follow the instructions for exporting and importing 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.
ALTER SYSTEM Statement
You can flush the Database Smart Flash Cache by issuing an ALTER SYSTEM FLUSH FLASH_CACHE statement. Flushing the Database Smart Flash Cache can be useful if you need to measure the performance of rewritten queries or a suite of queries from identical starting points.
Related Topics
28.2.2.2.2 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.
Caution:
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;
ALTER SYSTEM Statement
You can flush the Database Smart Flash Cache by issuing an ALTER SYSTEM FLUSH FLASH_CACHE statement. Flushing the Database Smart Flash Cache can be useful if you need to measure the performance of rewritten queries or a suite of queries from identical starting points.
28.2.3 Post Upgrade Steps
Perform steps to view the upgraded database.
After upgrading the database, check the DBA_MINING_MODELS view in the upgraded database. The newly upgraded mining models must be listed in this view.
After you have verified the upgrade and confirmed that there is no need to downgrade, you must set the initialization parameter COMPATIBLE to 12.1.
28.2.4 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 terminates.
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;28.3 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.
Related Topics
28.3.1 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
28.3.2 Options for Exporting and Importing Mining Models
Lists options for exporting and importing mining models.
Options for exporting and importing mining models are described in the following table.
Table 28-1 Export and Import Options for Oracle Data Mining
28.3.3 Directory Objects for EXPORT_MODEL and IMPORT_MODEL
Learn how to use directory objects to identify the location of the dump file set.
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;
Related Topics
28.3.4 Using EXPORT_MODEL and IMPORT_MODEL
The examples illustrate various export and import scenarios with EXPORT_MODEL and IMPORT_MODEL.
The examples use the directory object dmdir shown in Example 28-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 28-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 28-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 28-3 Importing the Models Back Into DM1
The models that were exported in Example 28-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_SAMPLEExample 28-4 Importing Models Into a Different Schema
In this example, the models that were exported from dm1 in Example 28-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_SAMPLEExample 28-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/expimpRelated Topics
28.3.5 Importing From PMML
You can import Regression models represented in Predictive Model Markup Language (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 PMML. The models must be of type RegressionModel, either linear regression or binary logistic regression.
Related Topics
28.4 Controlling Access to Mining Models and Data
Understand how to create a Data Mining user and grant necessary privileges.
28.4.1 Creating a Data Mining User
Explains how to create a Data Mining user.
A Data Mining user is a database user account that has privileges for performing data mining activities. Example 28-6 shows how to create a database user. Example 28-7 shows how to assign data mining privileges to the user.
Example 28-6 Creating a Database User in SQL*Plus
-
Log in to SQL*Plus with system privileges.
Enter user-name: sys as sysdba Enter password: password -
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
USERSandTEMPtablespace are included in the pre-configured database that Oracle ships with the database media.USERSis used mostly by demo users; it is appropriate for running the sample programs described in "The Data Mining Sample Programs".TEMPis the temporary tablespace that is shared by most database users.Note:
Tablespaces for Data Mining users must be assigned according to standard DBA practices, depending on system load and system resources.
-
To login as
dmuser, type the following.CONNECT dmuser Enter password:password
Related Topics
See Also:
Oracle Database SQL Language Reference for the complete syntax of the CREATE USER statement
28.4.1.1 Granting 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 the following example assign a set of basic data mining privileges to the dmuser account. Some of these privileges are not required for all mining activities, however it is prudent to grant them all as a group.
Additional system and object privileges are required for enabling or restricting specific mining activities.
Example 28-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;
READ or 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;
28.4.2 System Privileges for Data Mining
Learn different privileges to control operations on mining models.
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 the following table control operations on mining models.
Table 28-2 System Privileges for Data Mining
Example 28-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;
Related Topics
28.4.3 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 the following table control operations on specific mining models.
Table 28-3 Object Privileges for Mining Models
Example 28-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;
28.5 Auditing and Adding Comments to Mining Models
28.5.1 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.
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
COMMENTstatements
28.5.2 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:
-
"Auditing Oracle Data Mining Events" in Oracle Database Security Guide for details about auditing mining models
-
"Monitoring Database Activity with Auditing" in Oracle Database Security Guide for a comprehensive discussion of unified auditing in Oracle Database
-
"About the Unified Auditing Migration Process for Oracle Database" in Oracle Database Upgrade Guide for information about migrating to unified auditing