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:
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;
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;
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.