Setting Up Oracle Data Miner 4.0

Overview

    Purpose

    This tutorial covers the process of setting up Oracle Data Miner 4.0 for use within Oracle SQL Developer 4.0.

    Time to Complete

    Approximately 15 mins.

    Introduction

    The Oracle Data Miner 4.0 graphical user interface (GUI) is included as a free extension of Oracle SQL Developer, version 4.0. In order to use the Oracle Data Miner GUI to perform data mining, you must complete the following three setup tasks:

    1. Create a database user account for data mining
    2. Create a database connection within SQL Developer for the data miner user
    3. Install the Oracle Data Miner Repository

    Notes:

    • You do not need any experience with SQL Developer in order to perform the required steps.
    • The first and last task may be accomplished either by using SQL Developer 4.0, or by running scripts at command line.

    Software Requirements

    The following is a list of software requirements:

    • Have access to or have Installed:
      • Oracle Database:
        • Minimum: Oracle Database 11g Enterprise Edition, Release 2 (11.2.0.1) with Data Mining Option.
        • Preferred: Oracle Database 12c Enterprise Edition, Release 12.1 with Advanced Analytics Option.
      • The Oracle Database sample data, including the SH schema.
      • SQL Developer 4.0

    Suggested Prerequisite

    Before starting this tutorial, you should consider the following:

    • Although SQL Developer 4.0 is certified with Oracle Database 11g, Release 2, the tutorials in this series have been developed against Oracle Database 12c, Release 1.
    • In addition, a wide range of new features associated with Oracle Database 12c are used in subsequent tutorials. Therefore, it is strongly suggested that you use Oracle Database 12c.

Create a Data Miner User Account

    SQL Developer is a client to the Oracle Database software. Before you can perform actions such as creating a user, you must connect to the database from SQL Developer.

    To connect to the database, you will define an administrative (SYS) connection in SQL Developer, and then use that connection to create the data miner user.

    Perform the following steps.

    To open SQL Developer, double-click sqldeveloper.exe in the directory where you unzipped SQL Developer, as shown below. Alternatively, you can create a desktop shortcut for the file.

    Note: If you are prompted to import settings from an earlier version, click Yes if you have been using an earlier version of SQL Developer and want to preserve those settings.

    In the SQL Developer Connections tab, right-click Connections and select New Connection from the pop-up menu.

    In the New / Select Database Connection dialog box:

    A. Enter the following parameters:

    • Connection Name: admin
    • Username: sys
    • Password: The Administrative password for your database
    • Connection Type: Basic
    • Role: SYSDBA
    • Hostname: The host name of your database server (localhost if the database is installed on your PC)
    • Port: Enter the appropriate port number (1521 is the default)
    • SID or Service name:
      • The SID for your database, such as orcl
      • The Service name, such as orcl.<domain>

    NOTE: In Oracle Database 12c release 1, the option to install a pluggable database is turned on by default. If you selected this option for your installation, you must use the pluggable database name as part of a Service name for your connection, rather than using a SID.

    Connection example: Local database installation without the Container Database option (using SID)

    Connection example: Database installation with Container Database option selected (using Service name)

    B. Click Test to test the connection.

    Note: If the connection is successful, Status (just above the Help button) changes to "Success".

    C. Click Connect to save the connection and to also establish a connection to the database.

    Next, create a database account for the data mining user.

    A. In the SQL Developer Connections tab, expand the admin connection.

    B. Then, right-click the Other Users node and select Create User from the pop-up menu, like this:

    C. In the Create User window, select the User tab and specify a username, password, default tablespace, and temporary tablespace for the user account.

    Notes: The data miner user name can be any name you choose and there can be multiple user accounts in a single database. In addition, an account can also be shared by multiple users through the use of proxy user accounts.

    In the example below, the parameters include:

    • User Name: dmuser
    • Password: Create a password of your choice
    • Default Tablespace: USERS
    • Temporary Tablespace: TEMP

    Note: Your available tablespaces may differ if you are using an existing database that does not have the standard default tablespaces,

    D. Still in the Create User window, select the Granted Roles tab and click the check box in the Granted Column for CONNECT.

    E. Still in the Create User window, select the Quotas tab and click the check box to set the default USERS Tablespace to Unlimited.

    F. Click Apply to create the account. Then, click OK in the resulting "Successful" window.

    G. Select the SQL tab to view the SQL code and results.

    H. Click Close to dismiss the Create User window.

Create a SQL Developer Connection for the Data Miner User

    In this topic, you create a SQL Developer connection for the data miner user.

    You can create this connection either by using the SQL Developer Connections tab or the Data Miner tab. In either case, the same New / Edit Database Connection dialog box appears. All saved SQL Developer connections appears in both tabs.

    Follow these steps to create a connection for the data miner user.

    In the SQL Developer Connections tab, right-click the Connections node and select New Connection from the pop-menu.

    In the New / Select Database Connection dialog box, enter the following parameters:

    • Connection Name: dmuser
    • Username:dmuser
    • Password: Enter the password you created for your data miner user.
    • Connection Type: Basic
    • Role: default
    • Hostname: The host name of your database server (localhost if the database is installed on your PC)
    • Port: Enter the appropriate port number (1521 is the default)
    • SID or Service name:
      • The SID for your database, such as orcl
      • The Service name, such as orcl.<domain>

    NOTE: In Oracle Database 12c release 1, the option to install a pluggable database is turned on by default. If you selected this option for your installation, you must use the pluggable database name as part of a Service name for your connection, rather than using a SID.

    Connection example: Local database installation without the Container Database option (using SID)

    Connection example: Database installation with Container Database option selected (using Service name)

    Still in the New / Select Database Connection dialog box:

    A. Click Test to test the Connection.

    Result: The Status prompt should display "Success".

    B. Click Connect.

    Result: The SQL Developer Connections tab now displays both connections, like this:

    Note: When you activate a connection from the SQL Developer Connections tab, a SQL Developer Worksheet window automatically opens for that user.

    Close the Start Page, dmuser, and admin Worksheet windows, like this:

    Next, you will install the Data Miner Repository.

Install the Data Miner Repository

    At this stage, you have created a database account for the data miner user (dmuser), and have created a SQL Developer connection for that user. Next, you install the Data Miner Repository in the database using an automated process.

    To accomplish this, you simply display the Oracle Data Miner tab (which looks similar to the SQL Developer Connections tab), and then connect to the data miner user from the Data Miner tab.

    Notes:

    • The Data Miner Repository installation routine automatically starts the first time that you activate a SQL Developer connection from the Data Miner tab.
    • Once the Data Miner Repository has been installed in the database, other data miner users may be granted the required privileges to the repository by an automated process that is similar to the installation routine examined here.

    Follow these steps:

    From the SQL Developer menu, select Tools > Data Miner > Make Visible, as shown here:

    Result: The Data Miner tab appears next to the SQL Developer Connections tab, like this.

    Note: You can also display the Data Miner tab by selecting View > Data Miner > Data Miner Connections.

    Dismiss the SQL Developer Connections tab by clicking its Close (X) icon. Now, only the Data Miner tab appears directly below the SQL Developer main menu.

    Next, add dmuser as a Data Miner connection by following these instructions:

    A. In the Data Miner tab, click the Add Connection tool (green "+" icon).

    Result: the Select Connection window opens.

    B. Select dmuser from the Connection list as shown here. And then click OK.

    Result: dmuser appears in the Data Miner tab.

    To begin the installation process of the Data Miner Repository:

    A. Double-click on dmuser.

    Result: A message tells you that the Data Miner Repository is not installed in the database, and asks you if you want to install the Repository.

    B. Click Yes to launch the installation process.

    In the Connection Information dialog box:

    A. Enter the Administrative password for the database.

    Note: You must be logged in as SYS to install the repository.

    B. Click OK to continue.

    In the Repository Installation Settings dialog box:

    A. Select the Default Tablespace and the Temporary Tablespace for the repository account named ODMRSYS.

    Notes:

    • The initial space requirement for the Data Miner Repository is 200MB; however, the tablespace grows as metadata is added.
    • For Oracle Database release 11.2.0.4 and above, the ODMRSYS repository account requires the use of a table space with SEGMENT_SPACE_MANAGEMENT set to automatic.
    • In our example, we select the default values, which are USERS for Default Tablespace and TEMP for Temporary Tablespace.

    B. Click OK to display the Install Data Miner Repository dialog box.

    In the Install Data Miner Repository dialog box:

    A. Click Start to begin the repository installation.

    Note: The Install Demo Data option is preselected (the demo data is used by subsequent Oracle Data Miner OBE tutorials).

    Notes:

    • The Installation takes approximately 10 minutes for remote database installations and 2 minutes for local database installations.
    • The scroll bar provides a visual indication of the installation process progress, as shown below.

    B. When the installation completes, you may optionally click Show Log to view the installation log.

    C. Click Close to dismiss the Install Data Miner Repository dialog box.

Summary

    In this lesson, you learned how to set up the Oracle Data Miner graphical user interface for use within SQL Developer, version 4.0.

    In this tutorial, you have learned how to:

    • Start SQL Developer
    • Create a SQL Developer Connection for the SYS account
    • Create a Data Miner database user account
    • Create a SQL Developer Connection for Data Miner User
    • Install the Data Miner Repository.

    You are now ready to perform data mining with the dmuser account. If you are new to Oracle Data Miner, or SQL Developer, version 4.0, We suggest you begin with the tutorial Using Oracle Data Miner 12c Release 1.

    Resources

    To learn more about Oracle Data Mining:

    Credits

    • Lead Curriculum Developer: Brian Pottle
    • Other Contributors: Charlie Berger, Mark Kelly

To help navigate this Oracle by Example, note the following:

Hiding Header Buttons:
Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
Topic List Button:
A list of all the topics. Click one of the topics to navigate to that section.
Expand/Collapse All Topics:
To show/hide all the detail for all the sections. By default, all topics are collapsed
Show/Hide All Images:
To show/hide all the screenshots. By default, all images are displayed.
Print:
To print the content. The content currently displayed or hidden will be printed.

To navigate to a particular section in this tutorial, select the topic from the list.