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.

Thursday, October 3, 2013

Review of OOW13–Part 2

This is part 2 of my review of OOW13 and it looks at some of the announcements that were made and were of interest to me. There were many-many more announcements. Here is the link to my OOW13-Part1 post.

In-Memory Database, combining the benefits of row and columnar storage, both will run at the same time in the database, and all of this will be in-memory. When a query is issued the database will decide which version of the data it will access to retrieve. The typical row level storage will be used for OLTP type queries and the columnar storage for DW type queries. All of the processing, updates and maintenance of this will be done seamlessly in the background, including the syncing of data between the row and columnar versions. No new coding or recoding of your applications is needed. All that is involved to enable this feature is a setting in the database. Oracle claim you will see 1000x performance with queries and 2x for transaction.  This will be available in 12.1.2 and will be an extra licence cost.

Database-as-a-Service will be a full Database-as-a-Service with your own instance. A range of support/offerings will be available ranging from basic infrastructure and DB, to managed and managed for maximum availability.

Everything-as-a-Service, or it seems that way. In addition to the full database as a service, it seemed that they announced every piece of software that they have is available as a service. Basically anything you want is available with different levels of support from self managed up right up to a full managed service and everything in-between.

Oracle Backup, Logging and Recovery appliance was announced. This appliance is designed and built to receive, capture and store backups (including incrementals) for your database server, whether it is located in a private or public cloud. The appliance will backup your database server in real-time and not just from the last backup.

Microsoft and Oracle. Yes Microsoft presented as part of the keynote presentation on Wednesday. Microsoft presented about their Oracle offerings on the Azure Cloud platform. They are offering the Oracle Database on Microsoft servers and also on Linux servers.

Monday, September 30, 2013

Roundup of OOW13–Part 1

Oracle Open World 13 finished up last Thursday and I’ve send most of the time since either traveling back to Ireland, trying to re-adjust to Irish time and most importantly spending some time with the family.

I want to use this blog post to say ‘thank you’ to a large number of people. I will have another blog post on some of the important announcements (for me) at Oracle Open World.

I had 2 presentations at OOW and I also assisted at a hands-on lab. These were spread out over Monday to Wednesday, with one presentation/lab on each day. It was fun and many thanks to the people who came to these sessions. I had many conversations during the remainder of OOW with some of the people who attended my sessions.

I would like to thank Lillian, Vikki and all the OTN team for making all the arrangements, organizing events and lots and lots of things that happened at OOW. The Oracle ACE Programme paid for my flights and accommodation while I was at the ACE Directors briefing at Oracle HQ before OOW and during OOW.

I’ve heard stories about the ACE Directors briefing over the years. This year was my first one and all I can say is that it lived up to the stories and more. Over 2 (long-ish) days we had a number of SVPs, VPs, and other very senior people from various divisions of Oracle come into the briefing and openly tell us what is happening with the products, what will be announced at OOW and what will be coming over the next 6 to 12 months. They told us all of this under NDA, so we cannot talk about any of it until Oracle has publically announced it. We also had Thomas Kurian EVP talk to us for just over an hour. He was very impressive.  In my next blog post I will talk about some of the announcements that were made during OOW.

Thank you to DIT for making the necessary arrangements and facilitating  me to be away for this prolonged trip.

There were many social events during OOW and on some evening there was 3 different events for me to go to. Unfortunately I wasn’t able to get to all of them. I met up with lots of people from countries like Norway, Belgium, Holland, UK, Austria, Australia, Canada, Brazil, Ireland and the USA.  Many thanks to RittmanMead and Peak Indicators for the meals, and lots of user groups and various Oracle countries for the drinks.

On my last night at OOW I went to my first baseball game between the Giants and the Dodgers, with Roel Hartman from Holland. This was a great night of entertainment and I would highly recommend going to a baseball game. The Giants won.

As always I’m sure that I have missed some people off this list. I do apologise and I’ll blame the jet lag for it Smile

OOW is great for all the conference swag you can get. I tried not to get too carried away with it, but the photographs below will give you some idea of what I collected.


Tuesday, September 24, 2013

Adding Oracle Data Miner to OBIEE

Oracle Data Miner is a very powerful tool that provides advanced machine learning algorithms that are embedded in the Oracle database. By using Oracle Data Miner you do not have to use another tool, from another vendor, to do your data mining. You can do everything in the database, ensuring that the security of your data is maintained and use all the performance functionality that comes with the database.
To add to the advanced insights that you can get from using ODM, you can combine ODM with your OBIEE dashboards to gain a deeper level of insight of your data. This is the combining of data mining techniques and visualization techniques.
The purpose of this blog post is to show you the steps involved in adding an ODM model to your OBIEE dashboards. Lots of people have been asking for the details of how to do it, so here it is.
The following example is based on a presentation that I have given a few times (OUG Ireland, UKOUG, OOW) with Antony Heljula.
1. Export & Import the ODM model
If your data mining analysis and development was completed in a different database to where your OBIEE data resides then you will need to move the ODM model from ODM/development database to the OBIEE database.
ODM provides two PL/SQL procedures to allow you to easily move your ODM model. These procedures are part of the DBMS_DATA_MINING package. To export a model you will need to use the DBMS_DATA_MINING.EXPORT_MODEL procedure. Similarly to import your (exported) ODM model you will use the DBMS_DATA_MINING.IMPORT_MODEL procedure.
2. Create a view that uses the ODM model
You can create a view that uses the PREDICTION and PREDICTION_PROBABILITY functions to apply the import ODM model to your data. For example the following view is used to score our customer data to make a prediction of they are going to churn and the probability that this prediction is correct.
SELECT st_pk,
       prediction(clas_decision_tree using *) WITHDRAW_PREDICTION,
       prediction_probability(clas_decision_tree using *) WITHDRAW_PROBABILITY

3. Import the view into the Physical layer of the BI Repository (RPD)
The view was then imported into the Physical layer of the BI Repository (RPD) where it was joined on primary key to the other customer tables (we had one records per customer in the view). With the tables being joined, we can use the prediction columns to filter the customer data. For example filter all the customer who are likely to churn, WITHDRAW_PREDICTION = ‘N’
4.Add the new columns to the Business Model layer
The new prediction columns were then mapped into the Business Model layer where they could be incorporated into various relevant calculations e.g. % Withdrawals Predicted, and then subsequently presented to the end users for reporting
5. Add to your Dashboards
The Withdraw prediction columns could then be published on the BI Dashboards where they could be used to filter the data content. In the example below, the use has chosen to show data for only those customers who are predicted to Withdraw with a probability rating of >70%

Sunday, September 22, 2013

Oracle Magazine review–May/June 2000

The headline articles of Oracle Magazine for May/June 2000 were on the evolution of organisations into adopting a e-business model. It included 8 steps on evolving to e-business, how to use the Oracle Internet Platform, Web Portal and Java technology.
Other articles included:
  • Tom Kyte has an article on Oracle Availability Options and explains when to implement Oracle Parallel Server or replication or a standby database.
  • There is a new release of Oracle Discover 3i and Oracle Reports 6i that support XML and are part of the Oracle Intelligent WebHouse initiative.
  • Oracle licenced the mobile moddleware developed by Nettech System, to support Oracle’s steps into this field.
  • There is an overview of the IOUG-A Live! 2000 conference which is being held between 7-11 May in the Anaheim Convention Centre. Over 4,000 attendees are expected.
  • Kelli Wiseth gives and overview of Java 2, explaining the differences between J2SE and J2EE. The article also discusses how Java is part of the Oracle Internet Platform.
  • Steven Feuerstein gives the second part of his article on using Java Classes and Objects in the Oracle 8i database.
  • Richard Niemiec has an articles on Fundamental Tuning Goals and details the followings:
    • Allocate the right amount of memory for the Oracle instance.
    • Keep the right data in memory.
    • Find problem queries.
  • Kevin Loney had an article on how to protect your database from security threats. These included:
    • Guard your backups and development environments
    • Know your default user and applications accounts
    • Control the distribution of database names and locations
    • Use auditing effectively
    • Make password changes mandatory yet simple
    • Isolate your production database
  • Venkat Devraj talks discuss six storage tips for 24x7 availability
    • Know and understand RAID options
    • Choose your disk-array size with caution
    • Do not use read ahead caches for online transaction processing applications
    • Do not reply on write caches to eliminate I/O hot spots
    • Consider using multilevel RAIDS
    • Ensure that your stripe sizes are consistent with your OS and database block sizes
To view the cover page and the table of contents click on the image at the top of this post or click here.
My Oracle Magazine Collection can be found here. You will find links to my blog posts on previous editions and a PDF for the very first Oracle Magazine from June 1987.

Thursday, September 19, 2013

Nested Tables (and Data) in Oracle & ODM

Oracle Data Mining uses Nested data types/tables to store some of its data. Oracle Data Mining creates a number of tables/objects that contain nested data when it is preparing data for input to the data mining algorithms and when outputting certain results from the algorithms.  In Oracle 11.2g there are two nested data types used and in Oracle 12.1c we get an additional two nested data types. These are setup when you install the Oracle Data Miner Repository. If you log into SQL*Plus or SQL Developer you can describe them like any other table or object.





The following two Nested data types are only available in 12.1c





These Nested data types are used by Oracle Data Miner in preparing data for input to the data mining algorithms and for producing the some of the outputs from the algorithms.

Creating your own Nested Tables

To create your own Nested Data Types and Nested Tables you need to performs steps that are similar to what is illustrated in the following steps. These steps show you how to define a data type, how to create a nested table, how to insert data into the nested table and how to select the data from the nested table.

1. Set up the Object Type

Create a Type object that will defines the structure of the data. In these examples we want to capture the products and quantity purchased by a customer.

create type CUST_ORDER as object
(product_id     varchar2(6),
quantity_sold  number(6));

2. Create a Type as a Table

Now you need to create a Type as a table.

create type cust_orders_type as table of CUST_ORDER;

3. Create the table using the Nested Data

Now you can create the nested table.

create table customer_orders_nested (
cust_id       number(6) primary key,
order_date    date,
sales_person  varchar2(30),
c_order       CUST_ORDERS_TYPE)
NESTED TABLE c_order STORE AS c_order_table;

4. Insert a Record and Query

This insert statement shows you how to insert one record into the nested column.

insert into customer_orders_nested
values (1, sysdate, 'BT', CUST_ORDERS_TYPE(cust_order('P1', 2)) );

When we select the data from the table we get

select * from customer_orders_nested;

---------- --------- ------------------------------
         1 19-SEP-13 BT

It can be a bit difficult to read the data in the nested column so we can convert the nested column into a table to display the results in a better way

select cust_id, order_date, sales_person, product_id, quantity_sold
from customer_orders_nested, table(c_order)

---------- --------- ------------------------------ ------ -------------
         1 19-SEP-13 BT                             P1                 2

5. Insert many Nested Data items & Query

To insert many entries into the nested column you can do this

insert into customer_orders_nested
values (2, sysdate, 'BT2', CUST_ORDERS_TYPE(CUST_ORDER('P2', 2), CUST_ORDER('P3',3)));

When we do a Select * we get

---------- --------- ------------------------------
         1 19-SEP-13 BT

         2 19-SEP-13 BT2

Again it is not easy to ready the data in the nested column, so if we convert it to a table again we now get a row being displayed for each entry in the nested column.

select cust_id, order_date, sales_person, product_id, quantity_sold
from customer_orders_nested, table(c_order);

---------- --------- ------------------------------ ------ -------------
         1 19-SEP-13 BT                             P1                 2
         2 19-SEP-13 BT2                            P2                 2
         2 19-SEP-13 BT2                            P3                 3

Friday, September 13, 2013

Upgrading ODMr and SQL Dev forEA2

The Early Adopter 2 of Oracle SQL Developer was released yesterday (Thursday 12th Sept). To install this new version of the Tool, including Oracle Data Miner, you can follow the instructions below

  • Go to the EA2 download page and download the EA2 release
  • Unzip the EA2 download
  • Create a new shortcut that point to the sqldeveloper.exe
  • Start SQL Developer EA2
  • You will be prompted for the location of the Java JDK. On my VM it was  C:\Program Files\Java\jdk1.7.0_25. 
  • Next you are prompted about importing your setting from the previous version. Select Yes.
  • After the setting have been imported SQL Developer will open and you are now able to enjoy

Oracle Data Miner

  • For Oracle Data Miner you need to make the option visible by selecting Tools->Data Miner->Make Visible. This will open the ODM connection tabs along with a couple of others. I’m running the following on a 12.1c database.
  • Open one of your ODM connections by double clicking on it.
  • ODM will check the version of the ODM repository in the DB. You will be prompted to upgrade the ODM repository to the latest version. Click on the Yes button


  • Enter the SYS password, or talk nicely to your DBA.


  • Then click the Start button to start the ODM repository upgrade


  • This will take anything from a minute to 10 minutes, depending on the location of the DB and your network.
  • When everything is finished you can close the window and start using Oracle Data Miner by opening an existing workflow or by creating a new one.

Friday, August 30, 2013

Oracle Magazine-March/April 2000

The headline articles of Oracle Magazine for March/April 2000 were focused on e-business. There was articles covering the typical issues in setting up an e-business, the technical environment and some reports from organisation who have used the Oracle tools.


Other articles included:

  • Oracle releases their Oracle XML Developer’s Kit (Oracle XDK), with support for a variety of programming languages. It included XML Parsers for Java, C, C++ and PL/SQL. XSL Processor, XML Class Generator, and XML Transviewer Java Beans.
  • Oracle 8i Lite for the Palm Computing and Psion EPOC operating systems is available.
  • Oracle acquires Carleton, who were innovators of data quality and mainframe data extraction software for customer focused data warehousing applications.
  • Oracle releases Oracle Fail Safe 3.0 which was used to protect Microsoft Windows NT applications and databases, and supported Oracle 7, 8 and 8i, Oracle Developer Server 6.0 Forms and Reports Servers, Oracle Application Server 4.0 and Microsoft Internet Information Server 4.0.
  • Steven Feuerstein has an article about getting started with Calling Java from PL/SQL and gives a simple example to illustrate how to do this. The necessary system privileges included JAVASYSPRIV for the DBA and JAVAUSERPRIV for those schemas who want to call the Java code
  • Graham Wood and Connie Dialeris give an overview of Statspack that was was released with Oracle 8.1.6. The article covered the various features, how to install it and how to configure the Snapshot Level & SQL Thresholds. The article also gave an example of how to use DBMS_JOB to automate the collecion of the statistics.
  • A Step-by-Step guide on how to use RMAN (that most of use know and love!), including the RMAN architecture, how to setup a backup, starting a backup and the all important step of recovering a backup.
  • With Oracle 7 came the ability to Clone a database. In this article it goes through the steps required to setup and clone a production database.

To view the cover page and the table of contents click on the image at the top of this post or click here.
My Oracle Magazine Collection can be found here. You will find links to my blog posts on previous editions and a PDF for the very first Oracle Magazine from June 1987.

Thursday, August 22, 2013

The 2013 Gartner Hype Cycle

The 2013 Gartner Hype Cycle is out and it can be interesting to compare the new graph with the ones from previous years. Particularly for my interests in Data Science, Big Data, Data Mining, Predictive Analytics and of course the Oracle Database.

Gartner Hype Cycles 2013

from 2012

from 2011

from 2010

from 2009

Tuesday, August 20, 2013

Schema Table Filtering for Oracle Data Miner

If you have been using Oracle Data Miner, that is part of SQL Developer 4 or SQL Developer 3, you will notice that your schema can get filled up with various tables that are created by your workflows. The following image gives an example.


These tables can include details of the various algorithms used and their settings, sample tables that were created using the various nodes, etc.  Basically they contain all the information that was setup by each node. Not every node in your workflow will create a table, but a lot do in particular if you have set the Cache or Sample in the Properties tab.

In most cases you do not need to be aware of or use most of these tables.

So How do I hide them, so that my schema table listing only shows me the main tables in my schema ?  By main tables, I mean the tables that you would expect to have in your schema before you started using Oracle Data Miner.

The answer to this question is to apply filters to your tables in SQL Developer. To do this go to your schema in the Connections tab. Expand to get the full list of schema objects and then right click on Tables. You should get a menu like the following.


Select Apply Filter from the menu and the Apply Filter window will open. Here you can create filters to apply to the tables in your schema.

To restrict Oracle Data Miner related table you will need to exclude tables that begin with, DM$ and ODMR$. The following image shows these filters.


When these filters are applied we only get our schema tables.


There are two additional filters you may want to consider. The first of these is for the tables that begin with OUTPUT. These are tables that are created when you build a node sends the outputs from running a model to a table, or some other scenario where the output is sent to a table. In reality this is bad naming and we should use a name that is more meaningful, and reflects the contents of the table. But sometimes you just want to spool the outputs to a table and the name is not important. I have an additional filter to not show these tables (see below).

With SQL Developer 4, Oracle Data Miner seems to generate IOTs, as we can see in the above image. Again another filter can be created to exclude these from the list.

Here is the full list of filters.


Tuesday, August 6, 2013

Depreciated ODM features in 12c

With the release of the Oracle 12c Database there has been some changes to the Advanced Analytics options. Most of the new features both in the database and in the ODM tool have been documented in previous blog posts.
But what has been removed from the Advanced Analytics Option and what is not longer supported.
The first of these is the Java API that Oracle supplied many, many years ago. They have been saying for a few years now and since the release of 11.2g that these Java APIs are no longer supported. Again the documentation states this and the demo scripts are not included in the latest SQL Developer 4.  Instead of using the Java APIs you can using the in-database SQL functions and procedures.
One of the in-database DM algorithms was the Adaptive Bayes Network (ABN). Although this was de-supported in 11.2g of the database is was still in the database. This was to give customers who were still using it time to migrate to using the other algorithms. In 12c the ABN algorithm is not in the the database.  Before you upgrade your 11.2.x Oracle database to 12c you will need to drop any ABN models that you have in your database