A Verifying Your Data Mining Installation

This appendix provides a set of SQL and PL/SQL commands that test whether or not the basic capabilities of data mining are present in the database. You can execute these commands directly in the database without downloading or installing any additional software.

Note:

These commands create and score several data mining models. These models do not perform meaningful data mining. They simply show whether or not model creation and scoring operations are working properly.

This appendix includes the following topics:

Connect to the Database
Create a Script or Execute the Commands Individually
Command Output

Connect to the Database

In SQL*Plus or SQL Developer, connect to the database as the system user. If you are not sure how to start these tools, see "Connect Application Development Tools".

CONNECT sys / as sysdba
Enter password: <sys_password>

Insure that the USERS and TEMP tablespaces are present in the database. These tablespaces are typically included by default and are always included in the starter database created during installation (See "Install Oracle Database"). You can check with a simple query.

select tablespace_name from dba_tablespaces;

Create a Script or Execute the Commands Individually

You can create a script by copying the commands in Example A-1 into a text file, saving the file with a .SQL extension, and invoking the script from the SQL command line.

SQL> @ script_name.SQL

Alternatively, you can copy each command from Example A-1 to the SQL command line and execute it individually.

Example A-1 Commands To Verify Data Mining Installation

--------------------------------------------------------
-- create a data mining user
--------------------------------------------------------
CREATE USER dm IDENTIFIED BY <dm_password> 
     DEFAULT TABLESPACE users 
     TEMPORARY TABLESPACE temp 
     QUOTA 20M ON users;

-- grant minimum privileges
GRANT create mining model TO dm;
GRANT create table TO dm;
GRANT create view TO dm;
GRANT create session TO dm;
 
--------------------------------------------------------
-- create a simple table for mining
--------------------------------------------------------
CONNECT dm/dm;
CREATE TABLE mt (p NUMBER, t NUMBER);
INSERT INTO mt VALUES (1,1);
INSERT INTO mt VALUES (2,2);
COMMIT;

--------------------------------------------------------
-- create models
--------------------------------------------------------
EXEC dbms_data_mining.create_model('MAI','ATTRIBUTE_IMPORTANCE','MT',null,'T');
EXEC dbms_data_mining.create_model('MAR','ASSOCIATION','MT','T');
EXEC dbms_data_mining.create_model('MNB','CLASSIFICATION','MT',null,'T');
EXEC dbms_data_mining.create_model('MSVM','REGRESSION','MT',null,'T');
EXEC dbms_data_mining.create_model('MKM','CLUSTERING','MT',null);
EXEC dbms_data_mining.create_model('MNMF','FEATURE_EXTRACTION','MT',null);

-- check the models
SELECT model_name, algorithm FROM user_mining_models;
 
--------------------------------------------------------
-- score models
--------------------------------------------------------
SELECT p,t,PREDICTION(MNB USING *) pred_nb,
          p,t,prediction(MSVM USING *) pred_svm,
          p,t,cluster_id(MKM USING *) clus,
          p,t,feature_id(MNMF USING *) feat
FROM mt;

--------------------------------------------------------
-- drop the models and data
--------------------------------------------------------
EXEC dbms_data_mining.drop_model('MAI');
EXEC dbms_data_mining.drop_model('MAR');
EXEC dbms_data_mining.drop_model('MNB');
EXEC dbms_data_mining.drop_model('MSVM');
EXEC dbms_data_mining.drop_model('MKM');
EXEC dbms_data_mining.drop_model('MNMF');
 
DROP TABLE mt PURGE;

Command Output

The output of the commands in Example A-1 is as follows.

User created.
 
Grant succeeded.
 
Grant succeeded.
 
Grant succeeded.
 
Grant succeeded.
 
Connected.
 
Table created.
 
1 row created.
 
1 row created.
 
Commit complete.
 
PL/SQL procedure successfully completed.
 
PL/SQL procedure successfully completed.
 
PL/SQL procedure successfully completed.
 
PL/SQL procedure successfully completed.
 
PL/SQL procedure successfully completed.
 
PL/SQL procedure successfully completed.
 
MODEL_NAME                     ALGORITHM
------------------------------ ------------------------------
MAI                            MINIMUM_DESCRIPTION_LENGTH
MAR                            APRIORI_ASSOCIATION_RULES
MKM                            KMEANS
MNB                            NAIVE_BAYES
MNMF                           NONNEGATIVE_MATRIX_FACTOR
MSVM                           SUPPORT_VECTOR_MACHINES
 
6 rows selected.
 
 
         P          T    PRED_NB          P          T   PRED_SVM          P
---------- ---------- ---------- ---------- ---------- ---------- ----------
         T       CLUS          P          T       FEAT
---------- ---------- ---------- ---------- ----------
         1          1          1          1          1 1.48008198          1
         1          3          1          1          1
 
         2          2          2          2          2 1.51991802          2
         2          2          2          2          1
 
PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Table dropped.