ODM 11g R2
Today I gave my VirtaThon presentation on the new Oracle Data Miner 11gR2 tool.
It was an interesting experience as VirtaThon was a virtual conference. The organisation and administration of the conference was excellent.
I had over 25 participants for my presentation, including Carolyn Hamm who has written a book on using Oracle Data Miner 10g. She seemed to enjoy my presentation as she was asking for more at the end, but we had run out of time.
The presentation was an unusual but interesting experience. All the participants were muted, so I could not hear anyone or be asked questions as the presentation progressed. I was not able to judge the body language or facial expressions, for me to work out how the presentation was going.
I was sitting in my living room when giving the presentation and spent almost an hour talking to myself. At time the concentration levels dipped and I have to refocus and used some visualisation to help me concentrate.
The presentation was divided into 2 parts. The first part was a presentation consisting of some background to ODM, how to get setup and running with ODM, and finally a discussion of some of the new features. This first part took approx. 30 minutes which surprised me as during my rehearsals it was talking 16 minutes. The second part of the presentation was a demo of using ODM to create a workflow, generating a classification model and then applying this model to some new data. During my rehearsals this was taking approx. 40 minutes.
I only had 50-55 minutes for my VirtaThon presentation so after my presentation I had 20-25 minutes for the demo. So I had to get through the demo quickly and I had to cut out a discussion of how the data exploration functionality in ODM can be used to get an insight into the data before you start using the data mining features. I will put together a blog post and video of this in a couple of weeks time that will explain it in more detail.
I managed to finish at 49 minutes, which left 6 minutes for questions. There was only a couple of questions, plenty of Thank You’s along with Good Presentation, which is always good to hear.
Thank you to everyone who attended the presentation and to the organisers of VirtaThon.
I will be giving a presentation on the Oracle Data Miner New Features at the online conference VirtaThon, on Monday 18th July.
VirtaThon is a FREE 6 day conference with 2 parallel sessions with world leading speakers on Oracle Java and MySQL.
Previously attendance at the conference cost $100, which was good value considering the quality of the speakers. But this year it is Free.
The VirtaThon conference runs from 16th July to 21st July
The schedule is available at
To sign up to attend some or all of the sessions go to
Attend4FREE! Jul 16-21: 6 Days of Expert+ Sessions #VirtaThon The Online Conference for the Oracle, Java & MySQL Domains http://bit.ly/ehlaV9
As with all development environments there will be need to move your code from one schema to another or from one database to another.
With Oracle Data Miner 11gR2, we have the same requirement. In our case it is not just individual procedures or packages, we have a workflow consisting of a number of nodes. With each node we may have a number of steps or functions that are applied to the data.
Exporting an ODM (11gR2) Workflow
In the Data Miner navigator, right-click the name of the workflow that you want to export.
The Save dialog opens. Specify a location on you computer where the workflow is saved as an XML file.
The default name for the file is workflow_name.xml, where workflow_name is the name of the workflow. You can change the name and location of the file.
Importing an ODM (11gR2) Workflow
Before you import your ODM workflow, you need to make sure that you have access the the same data that is specified in the workflow.
All tables/views are prefixed with the schema where the table/view resides.
You may want to import the data into the new schema or ensure that the new schema has the necessary grants.
Open the connection in ODM.
Select the project under with you want to import the workflow, or create a new project.
Right click the Project and select Import Workflow.
Search for the XML export file of the workflow.
Preserve the objects during the import.
When you have all the data and the ODM workflow imported, you will need to run the entire workflow to ensure that you have everything setup correctly.
It will also create the models in the new schema.
Data encoding in Workflow
All of the tables and views used as data sources in the exported workflow must reside in the new account
The account from which the workflow was exported is encoded in the exported workflow e.g. the exported workflow was exported from the account DMUSER and contains the data source node with data MINING_DATA_BUILD. If you import the schema into a different account (that is, an account that is not DMUSER) and try to run the workflow, the data source node fails because the workflow is looking for USER.MINING_DATA_BUILD_V.
To solve this problem, right-click the data node (MINING_DATA_BUILD_V in this example) and select Define Data Wizard. A message appears indicating that DMUSER.MINING_DATA_BUILD_V does not exist in the available tables/views. Click OK and then select MINING_DATA_BUILD_V in the current account.
I have created a video of this blog. It illustrates how you can Export a workflow and Import the workflow into a new schema.
Make sure to check out my other Oracle Data Miner (11gR2) videos.
Over the past few days I’ve been trying to install Oracle Apex 4 on my 11.2g database. I say trying as I’ve made a number of attempts with no success. I started with the install instructions that come with Apex 4. Generally Oracle installs and install instructions have improved greatly since the 6, 7 and 8i versions.
I had high hopes of an easy install (as indicated by the various Oracle Apex books), but no matter what version of the install instructions I found there always seemed to be a step missing.
I finally came across one set of instructions that worked for me. The following steps are what I performed to get Apex 4 working.
1. Download Apex 4 from OTN to the directory
2. Unzip the Apex 4 download file. It will create the directory
3. Login into SQL*Plus as SYS with SYSDBA
4. Run the Apex 4 install script
c:\apex_download\Apex\apexins.sql SYSAUX SYSAUX TEMP /i/
where SYSAUX is the tablespace for Apex, TEMP is the temporary tablespace and /i/ is needed for possible upward compatability
This steps can take up to 30 minutes to run
5. Load the Apex images into the database.
– Got to the c:\apex_download\Apex directory.
– Log into SQL*Plus as SYS with SYSDBA
– run @apxldimg.sql
– You will be asked to enter the directory for the images. Make sure that you enter the correct directory, otherwise it will not work. In my case it is
6. Run the Configuration script. This will set up the XDB HTTP connection details.
– enter the port number : 8080
7. Unlock the required schema
SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;
SQL > ALTER USER XDB ACCOUNT UNLOCK;
SQL > ALTER USER APEX_040000 ACCOUNT UNLOCK;
SQL > ALTER USER FLOWS_FILES ACCOUNT UNLOCK;
SQL > ALTER USER APEX_PUBLIC_USER ACCOUNT UNLOCK;
8. Open Apex. Open your browser and enter
there is a default workspace created
Workspace = internal
Username = admin
Password = admin
6. Change the password. The fist time you login you will be prompted to change the password. The new password needs to have a number, upper and lower case characters and one special character
7. To get the the Apex Admin page
Before you can start using the Oracle Data Miner features that are now available in SQL Developer 3, there are a few steps you need to perform. This post will walk you through these steps and I have put together a video which goes into more detail. The video is available on my YouTube channel.
I will be posting more How To type videos over the coming weeks and months. Each video will focus in one one particular feature within the new Oracle Data Mining tool.
So following steps are necessary before you can start using the ODM tool
Set up of Oracle Data Miner tabs
To get the ODM tabs to display in SQL Developer, you need to go to the View menu and select the following from the Data Miner submenu
- Data Miner Connections
- Workflow Jobs
- Property Inspector
Create an ODM Schema
There are two main ways to create a Schema. The first and simplest way is to use SQL Developer. To do this you need to create a connection to SYS. Right click on the Other Users option and select Create User.
The second option is to use SQL*Plus to create the user. Using both methods you need to grant Connect & Resource privileges to the user.
Create the Repository
Before you can start using Oracle Data Mining, you need to create an Oracle Data Miner Repository in the database. Again there are two ways to do this. The simplest is to use the inbuilt functionality in SQL Developer. In the Oracle Data Miner Connections tab, double click on the ODM schema you have just created. SQL Developer will check the database to see if the ODM Repository exists in the database. If it will create the repository for you. But you will need to provide the SYS password.
The other way to create the repository is to run the installodmr.sql script that in available in the ‘datamining’ directory.
example: @installodmr.sql USER TEMP
Create another ODM Schema
It is typical that you would need to have more than one schema for your data mining work. After creating the default Oracle schema, the next step is to grant the schema the privileges to use the Data Mining Repository. This script is called
example: @usergrants.sql DMUSER
Hint: The schema name needs to be in upper case.
IMPORTANT: The last grant statement in the script may give an error. If this occurs then it is due to an invalid hidden character on the line. If you do a cut and paste of the grant statement and execute this statement, everything should run fine.
If you want to demo data to be created for this new ODM schema then you need to run
example: @instdemodata.sql DMUSER
All of these scripts can be found in SQL developer directories