Saturday, December 21, 2013

BIWA Summit 2014

The BIWA Summit 2014 is on from January 14th-16th, and is located in the Oracle Conference Center, at Oracle Head Office, in Redwood City (CA, USA). This conference is organised by a very dedicated and experienced group of people, including some very senior people in Oracle who are responsible for various analytics offerings from Oracle.

I presented that this conference last January (2013), and I’ve been tempted into presenting again in January (2014).


The conference has been expanded with more parallel tracks, a hands-on track, and a meet the experts/presenters session. So lots and lots more content and learning experiences.

I will be giving two presentations. The first one is on how Universities in the UK are using Oracle Data Miner and OBIEE to manage their Student Churn. I gave this presentation at Oracle Open World (Sept, 2013) along with Tony Heljula from Peak Indicators.  This time (Tuesday 14th @10am) I’ll be giving the presentation on my own. My second presentation is a demonstration of how you can use Oracle Data Miner to do Sentiment Analysis using a sample data set from Kaggle (Wednesday 15th @11:15am). I’ve given this presentation a couple of times already and the feedback that I keep on hearing is ‘I didn’t know you could do that in Oracle’. So it is an alternative to using Endeca, R and any of the other tools that we keep on hearing about. Instead we can just use SQL.

If you come to one of my presentations make sure you ask me for one of my Oracle Data Scientist conference ribbons.  I got these made up for Oracle Open World and there was lots of interest in them.


I’ve agreed to take part in the meet the experts/presenters. This is were attendees at the conference can sign up for a 15 minute 1-to-1 slot with one of the experts/presenters. I’ll be available for this from 3pm on Wednesday 15th. If you would like to sign up for one of these slots then there will be a sign up sheet at the conference. I will be hanging out at the conference for most of the 2.5 days, so do make sure you say hello at some stage.

The full agenda is live (subject to change of course) and can be found by clicking on the image below


Hopefully I’ll see you there.

Friday, December 13, 2013

Upgrading to SQL Dev 4 & Oracle Data Miner 4

The production release of SQL Developer 4 and Oracle Data Miner 4 has just been released. If you are like me you will want to upgrade and start using this latest release. For me I particularly want to be using the new Oracle Data Miner 4.  Over the past (almost) 6 months I’ve been working with the Early Adopter versions (EAs) with some degree of frustration. So hopefully it will be all working now.

To download the production version of SQL Developer 4 that include Oracle Data Miner go to here.

The following are the steps that I followed to get SQL Developer installed and to migrate my Oracle Data Miner Repository.  I’m running a 12.1c Oracle Database.

1. Download and unzip the SQL Developer software. Go to the \sqldeveloper folder to locate the sqldeveloper.exe file. I created a shortcut on my desktop for this. When ready then run this file.

2. As SQL Developer is opening you will get the typical splash screen and at some point you will be asked about migrating your preferences from your previous release. In my case I’m migrating from EA1. I select Yes.


After a few more seconds SQL Developer should open with all your previous settings.


3. Now to update and migrate your existing Oracle Data Mining Repository to the new versions. To start this process, to to the Tool Menu and then select Data Miner –> Make Visible


This will open the Oracle Data Miner Connections tab and the Workflow Jobs tab. If you don’t make do this step then your Oracle Data Miner workflows may not run.

4. Double click on one of your schemas in the Data Miner Connection tab.


5. Before you upgrade your repository it is advisable to take a full backup of your database, and to export your workflows. Just in case anything might happen during the Repository upgrade. I cannot stress this enough, because during a previous upgrade my repository got wiped and I had to rely on my backups.

5. The version of the repository will be check and if it needs updating then you will get the following window. I’m migrating from EA1 so you might get a slightly different messages. It all depends on what version you were previously using. Select Yes.


6. Next you will need to give the SYS password (or talk nicely to your DBA). Then you will get a warning about disconnecting your session from the repository. Click OK.


Then you can click on the Start Button


Everything should finish after a few minutes.


7. Open one of your workflows and run it to make sure all is OK.


Based on my initial few hours of working with the production version of SQL Developer 4 and Oracle Data Miner 4 is that it seems to run a lot quicker than the Early Adopter versions.

Watch out for some blog posts over the coming weeks about some of the new features that are available in SQL Developer 4.  Like my previous blog posts, the new posts will be how-to type of articles.

Wednesday, December 11, 2013

Running PL/SQL Procedures in Parallel

As your data volumes increase, particularly as you evolve into the big data world, you will be start to see that your Oracle Data Mining scoring functions will start to take longer and longer.  To apply an Oracle Data Mining model to new data is a very quick process. The models are, what Oracle calls, first class objects in the database. This basically means that they run Very quickly with very little overhead.

But as the data volumes increase you will start to see that your Apply process or scoring the data will start to take longer and longer. As with all OLTP or OLAP environments as the data grows you will start to use other in-database features to help your code run quicker. One example of this is to use the Parallel Option.

You can use the Parallel Option to run your Oracle Data Mining functions in real-time and in batch processing mode. The examples given below shows you how you can do this.

Let us first start with some basics. What are the typical commands necessary to setup our schema or objects to use Parallel. The following commands are examples of what we can use

ALTER session enable parallel dml;

You can force parallel operations for tables that have a degree of 1 by using the force option.


alter session force parallel query PARALLEL 2

You can disable parallel processing with the following session statements.


We can also tell the database what degree of Parallelism to use



Using your Oracle Data Mining model in real-time using Parallel

When you want to use your Oracle Data Mining model in real-time, on one record or a set of records you will be using the PREDICTION and PREDICTION_PROBABILITY function. The following example shows how a Classification model is being applied to some data in a view called MINING_DATA_APPLY_V.

column prob format 99.99999
SELECT cust_id,
FROM   mining_data_apply_v
WHERE  rownum <= 18

   CUST_ID       PRED      PROB
---------- ---------- ---------
    100574          0    .63415
    100577          1    .73663
    100586          0    .95219
    100593          0    .60061
    100598          0    .95219
    100599          0    .95219
    100601          1    .73663
    100603          0    .95219
    100612          1    .73663
    100619          0    .95219
    100621          1    .73663
    100626          1    .73663
    100627          0    .95219
    100628          0    .95219
    100633          1    .73663
    100640          0    .95219
    100648          1    .73663
    100650          0    .60061

If the volume of data warrants the use of the Parallel option then we can add the necessary hint to the above query as illustrated in the example below.

SELECT /*+ PARALLEL(mining_data_apply_v, 4) */
FROM   mining_data_apply_v
WHERE  rownum <= 18

If you turn on autotrace you will see that Parallel was used. So you should now be able to use your Oracle Data Mining models to work on a Very large number of records and by adjusting the degree of parallelism you can improvements.

Using your Oracle Data Mining model in Batch mode using Parallel

When you want to perform some batch scoring of your data using your Oracle Data Mining model you will have to use the APPLY procedure that is part of the DBMS_DATA_MINING package. But the problem with using a procedure or function is that you cannot give it a hint to tell it to use the parallel option. So unless you have the tables(s) setup with parallel and/or the session to use parallel, then you cannot run your Oracle Data Mining model in Parallel using the APPLY procedure.

So how can you get the DBMA_DATA_MINING.APPLY procedure to run in parallel?

The answer is that you can use the DBMS_PARALLEL_EXECUTE package. The following steps walks you through what you need to do to use the DMBS_PARALLEL_EXECUTE package to run your Oracle Data Mining models in parallel.

The first step required is for you to put the DBMS_DATA_MINING.APPLY code into a stored procedure. The following code shows how our DEMO_CLASS_DT_MODEL can be used by the APPLY procedure and how all of this can be incorporated into a stored procedure called SCORE_DATA.

create or replace procedure score_data

  model_name => 'DEMO_CLAS_DT_MODEL',
  data_table_name => 'NEW_DATA_TO_SCORE',
  case_id_column_name => 'CUST_ID',
  result_table_name => 'NEW_DATA_SCORED');


Next we need to create a Parallel Task for the DBMS_PARALLEL_EXECUTE package. In the following example this is called ODM_SCORE_DATA.

-- Create the TASK

Next we need to define the Parallel Workload Chunks details

 -- Chunk the table by ROWID
The scheduled jobs take an unassigned workload chunk, process it and will then move onto the next unassigned chunk. 
Now you are ready to execute the stored procedure for your Oracle Data Mining model, in parallel by 10.

   l_sql_stmt   varchar2(200);
   -- Execute the DML in parallel
   l_sql_stmt := 'begin score_data(); end;';
                                  parallel_level => 10);

When every thing is finished you can then clean up and remove the task using



NOTE: The schema that will be running the above code will need to have the necessary privileges to run DBMS_SCHEDULER, for example

grant create job to dmuser;

Tuesday, December 3, 2013

Non-running workflows in ODMr 4 EA3

If you are brave enough to be using the early adopter releases of ODMr you may have run into the issue with your workflows not running.

When you go to run your workflow you will get the following window and nothing else happens.


To get passed this you will need to kill SQL Developer using the task manager or equivalent.

So how do you stop this from happening so that you can get your workflows to run. The simple solutions is that you need to have the workflow tab open for the workflow to run correctly.

To do this you need to make Oracle Data Miner visible, by selecting Tools from the menu, then Data Miner and finally Make Visible


Then you will need to go to the View menu option, then select Data Miner and then Workflow Jobs


Now your workflows will work and complete.

Hopefully this will be fixed in the production release of ODMr 4 (SQL Developer 4)

Thursday, November 28, 2013


The UKOUG TECH13 conference is starting on Sunday and this year things a bit different. In previous years there was one big conference that covered (almost) everything and this conference had been in Birmingham.

This year Birmingham has been replaced by Manchester and the conference will focus on Database, Development with a few bits and pieces from other areas. The full agenda can be found here.

Also this year the OakTable people are back but they will be in a different venue from the main conference. The OakTable World UK conference will be across the road in the Premier Inn and be on for two days on the Monday and Tuesday. Check out the agenda here

When I’m not in the Manchester Central conference centre attending some excellent presentations, you will find me over at the Premier Inn. I expect there will be a lot of people running between the two venues over the Monday and Tuesday.

On the Wednesday morning I will be giving a presentation on how you can perform sentiment analysis using the Oracle Advanced Analytics Option (aka using Oracle Data Mining). I’ve tried to keep the number of slides to a minimum for this presentation, so most of the time will be taken up with a live demo of how to build a Sentiment Analysis Model using the Oracle Data Miner tool, then we jump into SQL*Plus and I will show how you can use the Sentiment model using SQL. The final part of the presentation I will show how you can then add the Sentiment model to OBIEE.

So lots of demos and code examples.  Fingers cross it all works on the day Smile

I’ll also be making a little announcement about something that I have been working on recently and should be available in Spring 2014 Smile

Tuesday, November 26, 2013

New Oracle Magazines for my Collection

You may or may not know that I have been collecting Oracle Magazines. It all began back when I had my very first job as a graduate consultant with Oracle in Ireland. A few days ago I received a package that contained the following Oracle Magazines.  You will see that the list contains the very first edition and the second edition. Up to now I’ve just had an electronic copy of the first edition.

Here is the full list of the Oracle Magazines that were in the package.

- Volume 1, Number 1 : A New Dimension in Customer Support & Oracle version 5.1 Released
- Fall 1987, Volume 1, Number 2 : Enhancing Cellular Communications Oracle Helps Canada go Mobile
- Winter 1990, Volume IV, Number 1 : Focus on Financial Services & Systems Integration
- Spring 1990, Volume IV, Number 2 : Focus on Pharmaceuticals & National Language Support
- Summer 1990, Volume IV, Number 3 : Focus on Public Sector & Networking the Network
- Summer 1991, Volume V, Number 3 : Focus on Telecommunications & Open Systems Standards
- Winter 1992, Volume VI, Number 1 : Manufacturing Technologies & Massively Parallel Systems
- Spring 1992, Volume VI, Number 2 : Smart Cities Integrating Old and New, & Interview with Next Computer’s Steve Jobs
- Summer 1992, Volume VI, Number 3 : Mapping the Human Blueprint & Oracle version 7
- Fall 1994, Volume VIII, Number 4 : Systems Management & Oracle 7.1. Parallel Everything
- January/February 1995, Volume IX, Number 1 : Data Warehouse
- July/August 1995, Volume IX, Number 4 : Distributed Enterprise & Oracle release 7.2
- September/October 1995, Volume IX, Number 5 : Mission Critical Applications & Optimal Flexible Architecture
- November/December 1995, Volume IX, Number 6 : Managing Change & Client.Server Administrator Security
- January/February 1996, Volume X, Number 1 : Untangling the Web & Tuning Oracle 7 for Optimal Performance
- March/April 1997, Volume XI, Number 2 : Platform Diving & Performance Tuning with Oracle Expert.

My Oracle Magazine Collection can be found here. You will find links to my blog posts that summarize the contents of some of the previous editions. This is a work in progress project and at some point I will have summarized them all. On my Oracle Magazine Collection page I have a PDF for the very first Oracle Magazine from June 1987.

I’m now very close to having the full collection.  If you have any of the magazines that I’m missing and you are willing to donate them, let me know Smile

Friday, November 15, 2013

2014 Oracle User Group Conferences

Over the past few days I've put together a list of Oracle User Group Conferences from around the World that will be on in 2014.

This list is not complete and I’m sure I have left out some very well known conferences.  If I have left a conference out let me know. If you are running a conference let me know and I will add these to the list.

The table below is my initial list.  I've also posted this list to a new page called  OUG Confs. This can be accessed from the menu at the top of my webpage.  The plan is to keep this table updated (on the page/menu link) going forward into 2014, 2015 and beyond.

The only non Oracle User Group conference that I have included in the list is Oracle Open World.

Conference Name / Web Site
CFP Open
CFP Close
14-16 January
BIWA Summit
Redwood, CA, USA
5-7 February
RMOUG Training Days
Denver, USA
11th March
OUG Ireland
Dublin, Ireland
1st November, 2013
15th December, 2013
3-5 April
OUG Norway
Oslo, Norway
September, 2013
1st December, 2013
7-11 April
Las Vegas, USA
OUG Bulgaria
, Bulgaria
2-3 June
E4 2014 - Enkitec Extreme Exadata Expo
Dallas, Texas
Registration opens : 2nd January, 2014
Nov, 2013
31 Dec
5-6 June
OUG Finland
September, 2013
15th January
(Notification : 14th Feb)
22-26 June
Seattle, USA
15th October, 2013
27 Sept – 2 Oct
Oracle Open World
San Francisco, USA
Oak Table World
San Francisco, USA
UKOUG APPS Conference
, UK
OUG New Zealand
Australian OUG – INSYNC Conference
, Australia
30 Nov – 3 Dec
UKOUG TECH Conference
, UK
Oak Table World UK
, UK

Thursday, November 14, 2013

ODMr 4 EA3 : Repository Migration Failure

SQL Developer 4 Early Adaptor release 3 (EA3) was released just over a week ago. This EA3 release has over 500 bug or “feature” fixes since the EA2 release.

But there could be some new “features” introduced into the new EA3. This is the case with the Oracle Data Miner tool. One of the based features of using this tool is that a ODM repository is needed in the database. With each new release of the Oracle Data Miner tool there are typically some new features added to the repository. This will require an upgrade to be done to your repository.

For EA3 this repository upgrade steps does not seem to work. If you use automatically perform the upgrade it will not complete and will leave your repository in a locked state.

Fortunately Mark Kelly put up a notice about this on OTN along with how to fix it.

When EA2 came out just before OOW13, I performed the upgrade to EA2 on my laptop. During the EA2 upgrade the my ODM repository got recreated and I lost all my ODM work. As I was in the USA when this happened I did not have access to my backup and I had to wait until I got back home after OOW13 to recover my work.

So when EA3 was released I was not in a hurry to upgrade straight away. I waited just over a week. I was about to perform the upgrade and migrate my repository when I received the email notification of the post on the ODM OTN forum.  I had a lucky escape Smile

The “fix” involves a minor change to one of the scripts that comes with Oracle Data Miner (part of SQL Developer EA3) and just involves a cut and past from the post on the OTN forum.

I’ve make the suggested changes and I’m delighted to report that everything has worked without any problems. There are a number of “feature” fixes in ODMr EA3 which I’ve been able to test out so far.


Check out the OTN form post here.

I’ve now upgraded my ODM repositories on my and 12.1 databases and all seems to be good.


Monday, November 11, 2013

Dropping all your ODM model

This post is main a note to myself of a script to drop all my Oracle Data Miner models.

1. Check to see what models you have in your schema

SELECT model_name,

2. Drop all the ODM models

set serveroutput on
   cursor c_1 is SELECT model_name
                 FROM ALL_MINING_MODELS;
  FOR r_1 in c_1 LOOP
     dbms_output.put_line('Dropping model '||r_1.model_name);
     DBMS_DATA_MINING.DROP_MODEL(model_name => r_1.model_name);

Wednesday, November 6, 2013

Oracle 12c Book List for Santa

How many days are there to Christmas?

to_date('25/12/2011','DD/MM/YYYY') - trunc(sysdate) "How Many Sleep to Santa"
from dual;

There are a few Oracle 12c books available on Amazon (see below for  Here is my letter to Santa

Dear Santa

Could I have some of these books?  I've been good this year.








Monday, November 4, 2013

SQL Developer is dropping connections

I have SQL Dev 3.2.2 (32-bit) with the supplied JDK running on windows, in work and is rolled out to a large number of PCs. Sometimes we can have 10 users running SQL Developer against our 11.2 Database. But sometimes we can have 100 users using SQL Developer at the same time. They have all set up their connections to use the jdbc driver.

An error that has been occurring is that it SQL Developer is dropping connections to the database. The users get windows popping up that says

Your Database Connection has been reset. Any pending transactions or session state has been lost.

This typically happens when a user clicks to expand one of the objects under their schema in the Connections pane.

After searching and searching and a bit more searching it seem that this is being caused by and Out of Bands (OOB) error in the jdbc driver or on the network.  Our DB is set to be dedicated (not shared) so that is not what is causing the issue. So after all my searching, if the following statement is added to the sqldeveloper.conf file then SQL Developer should ignore the OOB errors.


What is really annoying about this error message is that this config file change seems to have worked for some users but for other users it has not. They are still getting their connections dropped when using SQL Developer, either while they are using their worksheets or when they click to expand/open a schema object.

Our next test is to roll out Oracle Client on the PCs and then get the users to connect to their schemas using the Oracle Client networking and a TNS entry. 

Will this work? Will this make the error go away? Is there a problem with using the jdbc connection? Is it picking up another jdbc driver on the PC? Is it a bug in SQL Developer? Will using Oracle TNS work? Is it a problem on the Server? Is it a problem with the Database (no errors are being reported)?

Lots of questions that need answering.

If you can answer some of these then let me know.

If/When I find out I will update this blog post.

Wednesday, October 30, 2013

OUG Ireland 2014 Call for Presentations is now open

The call for presentations at the OUG Ireland 2014 conference (or special event) is now open for submissions. Deadline is Sunday 15th December, 2013.

We hope to build on the previous successful events over the past few years, where we had seen the number of attendees double in two year.

The annual conference (or special event) will be back in the Conference Centre in Dublin, which is just a few minutes walk from the centre of Dublin.image

There will be a number of streams and these will include:
  • Technology / DBA
  • Business Intelligence
  • Development
  • Applications & E-Business
So there will be something for everyone and I’m sure there will be some sessions on 12c.
We are hoping to get some very well known names in the Oracle World to present at the conference and these people along should attach a large number of attendees Smile

If you are interested in Submitting a presentations then click on the image above or below and you will be taken the submissions webpage.
I’ve already submitted some presentations and although I’m on the conference committee, there is no guarantee I’ll have one selected Sad smile
I hope to see you there.

Saturday, October 19, 2013

Oracle Scene 12c Supplement

The autumn edition of Oracle Scene is now available (I’m the deputy editor) and if you are due to get a printed copy then you be receiving it really soon.

In addition to the main Oracle Scene magazine we had lots of extra 12c articles, so we decided to create a separate 12c Supplement. This is only available online.

There was article from Jonathan Lewis, Bryn, Llewellyn, Melanie Caffrey, Pete Finnigan, Bob Mycroft, Alex Nuijten and myself.

Check out this online edition by clicking on the image below


If you are using Oracle 12c or any of the related produce over the next past few months or next few month, why not write a short article telling us about your experiences. The next submission deadline is early January for the Spring edition.

Wednesday, October 9, 2013

How to Fix Odd Layout Behaviour in ODM v4 EA1 & EA2

If you have been using the last versions of the Oracle Data Miner tool that have been released as part of SQL Developer 4 EA1 and EA2, you may have noticed that the layout of your worksheets and other areas are inconsistent each time you open ODM.

This can be very frustrating as you have to rearrange the layout of the worksheets, property inspection, the ODM connections tab, etc, etc, etc.

This “feature” seems to be linked to when you installed the new version of the software. When you open SQL Dev 4 EA1 & EA2 you are asked if you would like to migrate your settings.  If you selected Yes for this then it is this that is causing the project.

How do you fix this?

1. Export your SQL Developer Connections. To do this go to the Connections tab and right click on Connections and select export from the menu


This will create a XML file.  Save this to your desktop so that you can find it easily later.

2. Locate the AppData directory for SQL Developer.  This really depends on your environment. If you are using Windows for your client then the AppData directory will be located somewhere live the following

C:\Users\<your username>\AppData\Roaming\SQL Developer

3. Rename the System4.0… Directory.  Located in the AppData/SQL Developer directory called some like  ‘system4.’  if you have installed and migrated to SQL Dev 4 EA2.  Rename this directory to another name e.g. ‘system4.’.  This effectively deletes all your setting for SQL Developer 4

4. Start SQL Dev 4.  When you start SQL Developer 4 EA1 or EA2 it will be like you are running the software for the first time.  But this time, when you are asked do you want to migrate your setting from an earlier version, select No.  This will create a clean system folder.

5. Import your Connections.  When SQL Developer opens you can right click on the connection link and select Import Connections (see image above). Then enter the name and location of the file you create in step 1 above.


After completing all of the above steps your unusual layout when you open ODM should now be fixed.