Showing posts with label OTN. Show all posts
Showing posts with label OTN. Show all posts

Tuesday, October 11, 2016

OTN Appreciation Day : My favourite thing from OTN #ThanksOTN

This blog post is my contribution to the OTN Appreciation Day, the brain child of Tim Hall (read his blog post here).

For my contribution, I'm going to write about something that is a bit different to what most people will be writing about. Most people will be writing about some feature of the Oracle Database or maybe their favourite tool.

I'm not going to do that. What I'm going to write about is something that OTN does for use Developers, DBAs, etc.

Basically OTN has done so much over the years to help developers in a multitude of different ways.

Apart from the support that OTN gives me as an Oracle ACE Director (Thank you!), one of my favourite things that OTN makes available to us are the VirtualBox Pre-built Developer VMs.


These pre-built VMs allow us developers to go play with the technology, to learn how to use it, to follow tutorials, to see how various software applications work together, etc all within a virtual machine.

I bet that (almost) everyone reading this blog and taking part in the OTN Appreciation Day will have used one or more of the virtual box prebuilt VMs.

Why is this a good thing? How would you like trying to install all this software from scratch? Not me. Typically for me when performing an install I usually mess something up. If this happens often enough then you may just get frustrated with what you are trying to do and just give up on it. The result will probably be you giving a negative review to your employer.

But the pre-build VMs take the pain of installing (sometimes) large and complex software is taken away from you and allows you to dive straight into using the software. I also really love that the VMs come with tutorials, decent data sets, example applications built using the software, and demonstrations on how to get each of these working together.

If you mess anything up, then you can just re-import the VM and start all over again. When you are finished using the VM and testing the software, all you need to do is to delete the VM. You latop, desktop or where ever you have installed the VM is left clean with no partially uninstalled files, etc.

Each of us will have our favourite VMs. For most people the Developer Day VM is fantastic. It you are a beginner or an experienced developer I would bet most people will have a copy of this VM and are probably using it as their personal Oracle Database sever.

For me, I'm also a regular user of the Oracle Big Data Lite VM and the OBIEE Sample Application VM.

For OTN Appreciation day, I haven't talked about a Database feature. Instead I've talked about something that OTN has done for us, the developer, DBA, etc community. I'd like to thank OTN for supporting the community by providing these VirtualBox pre-built VMs for us to use. You have saved me/us many, many, many hours/days/weeks/months over the years.

BTW. I'm looking forward to the VM with the 12.2c Database.

Friday, February 13, 2015

My OTN 2 Minute Tech Tip: Predictive Queries

A few days ago I recorded a 2-minute tech tip with Bob Hubbard of OTN.

My topic was on Predictive Queries which are a new feature in the Oracle 12c Database.

The challenge was to talk about the topic within 2 minutes. That is a lot harder than you time. Believe me.

Check out the video on the Bobs OTN 2-Minute Tech Tip channel or click on the link below.

It was fun doing this and hopefully I get a chance to do another video with Bob.

Here is a screen capture of when things were being recorded.

OTN tech tip

Sunday, August 24, 2014

Ice Bucket Challenge

Yes I was nominated by Ronald Vargas in Costa Rica to take up the challenge.

Here is the video.

My nominees are Mark Rittman, Heli Helskyaho and Chet Justice (aka Oracle Nerd)

You have 24 hours to take up the challenge and to post a video for all to see :-)

Tuesday, August 19, 2014

OUG Mexico

We (Gorcan, Glen, Debra and I) arrived into Mexico around 10pm and had a few minutes wait for our local user group contact to meet us. They had arranged transportation to our hotel.
2014 08 08 07 35 022014 08 08 07 35 07
The next morning (Friday) was the day of the OUG Mexico conference. We were collected from the hotel and taken to the conference venue. Our transport had to made a number of trips to/from the conference venue to cater for all the speakers, so some of us arrived in the middle of the opening keynote by Noel Portugal from Oracle.
2014 08 08 09 32 302014 08 08 09 32 35
After lunch we had a group photo of all the speakers.
2014 08 08 13 56 07
My 2 sessions were on in the afternoon so I got to relax a bit and hang out with some of the other speakers. My first session was on Oracle Data Mine and my second session was on Oracle R Enterprise. Just like in Costa Rica I had a good attendance and again they seemed to enjoy the presentations as they were laughing along at my attempts at jokes :-)
2014 08 08 16 17 432014 08 08 16 17 42
The Mexico conference was on Friday the 8th August and this was an important day as my book was officially available that day. Just like in my previous countries I had a copy of my book to give away. Here is a photo of me with the winner.
2014 08 08 15 31 462014 08 08 17 10 57
I had the last session of the day. Some of the sessions before this had over ran and I was under a bit of pressure to finish up my last presentation by 5pm. So apologies if this presentation seemed rushed.
Also many thanks to everyone who came up to me afterwards for a chat, to have your photo taken with me and to get my autograph! :-)
Then it was time to catch a taxi to my next hotel in Mexico city centre. This would be my 6th hotel in 6 days :-(
After checking into the hotel it was time to go for dinner. Renne Antunez had invited some of the speakers to his house for dinner. These was a very enjoyable evening and for me marked the end of my OTN tour. Many of the other speakers were going to over countries over the next week.
2014 08 08 21 18 102014 08 08 22 01 432014 08 08 21 17 43
Many thanks to Renne and his wife for hosting this dinner, especially as it was Renne's wife birthday. I hope he bought her a good present.
The next morning (Saturday) I was up at 5am to get a taxi to the airport and my long long travels home, from Mexico to Newark to Dublin getting home on Sunday morning at 8am.
A big thank you to the organisers of the OTN Tour and to each of the countries that invited me to present at their conference. I really enjoyed the experiences and hopefully I can join in another OTN tour sometime soon. Also a special thank you to Vikki in OTN for sorting out all the funding and everything else.
The finals thank you goes out to my travel companions for the tour, Gorcan, Debra and Glen. Without your companionship throughout the week it wouldn't have been as much fun.

Monday, August 18, 2014

OUG Costa Rica

After the OUG Panama conference we arrived later that night in San Jose the capitol of Costa Rica. The whole emigration, luggage pick up and customs was the smoothest experiences I have ever experienced at an airport. All was done in a matter of minutes. All the booths for emigration and customs was open and staffed. When was the last time you have ever seen this before. It was a very positive start to our short couple of days in Costa Rica. We had arranged for our hotel to have a taxi pick us up and sure enough the driver was there with a good clear sign. From time of landing to being in my hotel room, which was a 20 minute drive, took no more than 1 hour.

The next morning (Tuesday) we (Debra, Glenn, Gurcan and I) headed out to do some exploring of San Jose on foot. Our first task was to get some breakfast but none of us had any local currency. Debra volunteered to use one of the enclosed ATM machines to get some money. But there was some issue with the machine and she got locked into the cubical. At the same time she got a phone call from her bank in the UK. Her ATM transaction was flagged as a potential fraudulent transaction. I most say Debra's bank was very quick to contact her and to make all the necessary changes so that she could withdraw some money. After which we had a local breakfast.

2014 08 05 09 09 28

Then we sent the next 3 hours walking around San Jose.

2014 08 05 08 25 322014 08 05 09 44 572014 08 05 17 05 56

Then it was back to the hotel in the early afternoon as the local Oracle User Group had arranged a mini bus to take us up to a hotel that was near the conference location in San Carlos. We had been told that the journey would take 2 hours. Well after 2 hours of driving up hill we were told we were only just over half ways there. It was a long long journey but the views were really beautiful. We had a 10 minute break at little village with a nice church and a really cool garden in front of it.

2014 08 05 19 03 502014 08 05 19 04 552014 08 05 19 03 00

After anther hour we arrived at the hotel, checked in, and we met up with some other presenters for some dinner. Then it was time for bed as we were being collected at 7:30am.

On Wednesday morning we got collected and taken to the conference venue which happened to be a local university. The User Group had proved a bus service from San Jose to the conference venue and there was a sizeable number of industry people mixed in with some of the university students.

2014 08 06 09 21 242014 08 06 09 22 262014 08 06 09 25 51

My first presentation was in the auditorium just after the opening keynote. This has to be one of finest rooms I've seen and presented in. Even better we had a HD projector. This has to be a first, so I had no resolution issues with running my VMs. I had a really good turn out for my presentation and the photo below shows only some of those who attended.

2014 08 06 10 00 09

In the after noon I had my 2nd and 3rd presentations. For my last presentation all the seats were taken and there was even some people standing. There was a lot of people who came to all my presentations which I was delighted with.

2014 08 06 13 09 572014 08 06 15 55 252014 08 06 15 55 26

After my last presentation I had a raffle for a copy of my book. There seemed to be a lot of interest in this :-). Here is a photo of me with the winner of the book and someone else who was having a read of it afterwards.

2014 08 06 16 59 472014 08 06 17 17 18

This was a very enjoyable conference and the attendees at my sessions were laughing at some of my jokes or maybe they were laughing at me. Anyway I took it that they were enjoying my presentations. For the first time, I had people come up to me after my presentations to have their photography taken with me and to ask me for my autograph.

Here is a picture of the Oracle ACE Directors who presented at the OUG Costa Rica conference.

2014 08 06 17 30 38

We were told that our bus journey the day before was the scenic route so that is why it took 3 hours. As soon as the conference was finished we got back on the mini bus to be taken back to our hotel in San Jose. Theoretically it should have been a much quick journey as we were going back the quickest route. This time it took 2 hours 40 minutes.

On Thursday at 2:45am we were woken by a 4.7M earth quake. The room was shaking and everything was shaking in it including me. After breakfast on the Thursday morning, Debra had arranged for us to go on a tour of a Coffee plantation and factory. This coffee tour was hilarious and the best tours I have ever been on. Our tour guides were a comedy double act :-)

2014 08 07 09 42 482014 08 07 09 51 042014 08 07 10 09 56

After the tour it was back to the hotel and to get our taxi out to the airport. Did I mention how well operated the airport was. How often have you gone through security when all of the scanners were in operation and fully staffed. This meant that there was almost no queue and I was through security in no time at all. From check in to the departure gate took about 2 minutes.

Our (Glen, Debra, Gorcan and I) next stop was Mexico

Sunday, August 17, 2014

OUG Panama

The OTN Tour of Latin America has come to an end and I was very lucky to have been a part of Northern part of the tour.

My OTN Tour consisted of visiting 3 countries in one week and giving 8 hours of presentations. This blog post looks back at the OUG Panama conference and I'll have separate blog posts for each of the other countries.

My travels stared the Saturday before the OUG Panama conference which was on Monday 4th. My flights consisted of flying from Dublin to Newark and then onto Panama. All of that seemed to go smoothly until I reached Panama. After passing through emigration I was then in a very chaotic baggage hall. After about 1 hour of searching for my suit case I sought help from the airline. They were able to tell me that my suitcase was scanned off in Panama. So after another 30 minutes of searching for the suitcase with airline staff, there was still no sign of it. So a lost suitcase report was filed and off I went to meet the person who was collecting me from the airport. But there was no sign of them. I managed to find some wifi and sent a few messages looking for help and how could I get to my hotel. I got a quick reply and and 45 minutes later I was on my way to the airport.

This is the 3rd time that this particular airline has "lost" my suitcase.

I was very glad to reach the hotel but I had no change of cloths or toiletries with me :-( For all future travels that involve a flight change I will be packing some of these in my hand luggage.

On the Sunday morning, after several hours trying to contact the airline, I finally got approval from them to buy some items. It was then time to meet up with Gurcan Orhan and we headed out to explore Panama city on foot (and to buy me some things). We spent about 3 hours walking around exploring Panama city where we say lots of differences in living standards right beside each other. Then it was back to the hotel as the local user group had arranged a tour for us.

Our tour guide and one of the helpers for the user group was Melin took us to see the Panama canal, the old town and lots of other areas of Panama. We finished of the evening with a dinner in a traditional Panama restaurant. On getting back to the hotel my suitcase was there :-) but no communication from the airline about what happened.

2014 08 03 14 50 322014 08 03 16 17 072014 08 03 17 45 44

On the Monday morning I met up with Gorcan, Debra and Glen who were to be my travel companions for the week. There was a bus arranged to pick us up from the hotel and off we went to the conference venue which was in one of the Universities in Panama. We arrived at the venue very early and not many knew about the conference or where we should go. After a while we worked it and found the conference location.

2014 08 04 08 52 082014 08 04 15 10 56

Many thanks to the User Group in Panama for inviting us and for all the arrangements. There was plenty of helpers on hand.

My 3 sessions were not on until the afternoon so I hung out in a session by Gurcan and also one by Dan Morgan. Then a bit of hanging around with the conference people and talking about some Oracle tech stuff. For my 3 sessions I completely over ran on my first session and then for the last 2 sessions I was playing catchup. Especially for the last presentation as there was a bus booked to take us to the airport at 4pm. So I basically ran out of the presentation and straight into the bus to the airport. But before I did that I was able to give away the very first copy of my book. It wasn't released yet (it would be on the 8th Aug). I had a raffle for everyone who attended my sessions and here is a picture of me with the lucky winner.

2014 08 04 16 21 35 2014 08 04 08 46 19

We arrived at the airport, with a minor detour via the hotel, with lots and lots of time to spare for our flight. Our next location was Costa Rica.

Thursday, July 17, 2014

OTN Latin America (North) Tour 2014

For a few years now I (and I'm sure you have too) have heard about and followed the various Oracle User Group tours that OTN arranges/facilitates. A tour consists of a number of Oracle User Groups in a region coordinating together to have their conferences organised so that they can get speakers from across the world to come and present.

For most presenters it involves lots of travel. So instead of them doing all that travelling to present at one conference, they can now extend their travels a little and present in a number of countries. Most of the speakers are Oracle ACE Directors and OTN is very generous with their support in that they pay for all the flights, transportation and hotels. Without the generous support of OTN these tours and perhaps many of the conference would not take place.

With envy I used to follow the various speakers on tweeter as they talked about their travels from country to country and their experiences of meeting the people and exploring the various countries. Yes their time in each country seemed to be limited but they always got to see and do so much.

Earlier this year there was an call for presentations for the various OTN Tours in 2014. I submitted 3 presentations that coverd Oracle Advanced Analytics Option (Oracle Data Mining and Oracle R Enterprise). I thought I didn't stand a chance given the speakers that have participated in previous years.

A couple of weeks ago I received an email saying that I had been accepted onto the OTN Latin America (North) Tour. So you can imagine my excitement. The full OTN Tour North leg covers a number of countries across central and south America and is over a 2 week period. Unfortunately I'm not able to be away for that long, so I was accepted for the conferences on the first week of the tour. This will include Panama, Costa Rica and Mexico :-)

Some of you might think this is a bit of a golly and a holiday. What I've discovered over the past week or more is that it will be far from that. There is a lot of work in preparing the presentations, giving the presentation, setting up live demos between presentation, various meetings with people at the conferences etc etc etc. Then there is all the travel, all the airports, all the airport transfers, all the overnights in hotels. Over the course of 7 days I will be staying 6 different hotels.

I have spent the last week just trying to arrange my flights and hotels. This also involved trying to coordinate with other speakers so that we can travel together as much as possible.

Here are the dates and the presentations that I will be giving at these conferences:

4th August : Panama (in Panama City)

     10:00-11:00 : Getting Started with Oracle Data Miner & Predictive Analytics

     11:00-12:00 : Combining the Power of R and in-Datbaase Data Mining. Running R in the Database. Seriously!

     13:00-13:40 : Sentiment Analysis Using Oracle Data Mining

6th August : Costa Rica (in San Carlos)

     10:00-11:00 : Getting Started with Oracle Data Miner & Predictive Analytics

     13:00-14:00 : Sentiment Analysis Using Oracle Data Mining

     16:00-17:00 : Combining the Power of R and in-Datbaase Data Mining. Running R in the Database. Seriously!

8th August : Mexico (in Mexico City)

     14:00-15:00 : Getting Started with Oracle Data Miner & Predictive Analytics

     15:00-16:00 : Combining the Power of R and in-Datbaase Data Mining. Running R in the Database. Seriously!

When the agenda for the conferences are available I will have another blog post with their details.

If you are at one of these conference do please say hello :-)

I've finally booked all my flights and hotels. Many thanks to my fellow ACE Director presenters for your research and sharing of travel plans. It looks like there will be a groups of us all travelling together.

Now the next challenge is to prepare the presentations and live demos (yes live demos).

I hope to blog about each of the conferences and my travels to/from each country. It really depends on what time I will have and access to the internet. Perhaps this is something I will try to do on my various plane flights or waiting at the airports. So watch out for these :-)

Updated with some stats on my travels

My travel plans for the OTN Latin America tour of user group conferences involves

  • 12,200 flying miles,
  • 29.75 of flying time,
  • way too many hours hanging around in airports
  • over 8 days
  • staying in 6 hotels
  • plus 1 over night flight,
  • giving 8 hours of presentations in 3 countries

Why do we do this? Because we love sharing with the Oracle User Groups around the world. I'm only doing 1 week of the tour. Some people are doing 2 weeks :-(

Monday, June 23, 2014

Oracle Magazine September/October 2000

The headline articles of Oracle Magazine for September/October 2000 were on e-Business Integration, including online healthy prescription for online retailing, streamlineing the pulp and fiber industries, and the health care industry. Plus there was lots and lots of articles and news items all on businesses delivering solutions via the internet.

Ora Mag 2000 Sept Oct

As this was the Oracle Open World edition (and you see the label on the cover saying Biggest Ever) you can imagine there was a LOT of advertisements and sponsored articles. The following of other articles below will not cover these and will only look at the main content articles.

Other articles included:

  • Tom Kyte's article is on Tips for Migrating, Indexing and Using Packaged Procedures. In his article he gives some tips for migrating to Oracle 8.1i. He also discusses some scenarios around creating (or not) indexes on foreign keys. He also looks at the scenario of compiling linked procedures and how the use of packages avoids the identified issues.
  • Do you remember the Internet File System. There was an article that gave an overview of this that was available in Oracle 8i and was capable of managing over 150 different file types.
  • Autodesk releaseed OnSite, an enterprise solution for bringing design and location based information to the point of work via mobile devices. Autodesk On Site used Oracle 8i Lite and the Palm OS platform to provide an interactive, two way communication environment between the mobile worker and the overall decision support system.
  • The Oracle Academic Initiative began in 1997. In 2000 Oracle donated software licences, support services and Oracle training material to 17 educational institutions valued at $60 million
  • There was page after page, after page of announcements and news from various Oracle Partners.
  • Douglas Scherer gives the first part of an article that looks at how you can use Oracle 8i interMedia for managing and deploying content rich data on the internet.
  • Managing Your Resources looks at some of the new Oracle 8i EE helps DBAs to define plan, assign users to groups and prioritise resource allocations.
  • With the release of Oracle 8.1.6 came the new Statspack. Connie Dialeris and Graham Wood give an overview of the main features of Statspack, providing some guidance on how to use it in a proactive manner and gives a step-by-step guide to how you can trouble shoot performance problems with Statspack.
  • The final article was on Oracle Warehouse Builder (OWB). This was an overview type of article and gave an overview of the main components and gives some guidelines for setting up some different types of integration.

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, March 27, 2014

Oracle BigDataLite version 2.5.1 is now available

Back at the end of January Oracle finally go round to releasing the updated version of the Oracle BigDataLite virtual machine. Check out my previous blog post of this.

Yesterday (27th March) I say on Facebook that a new updated versions of the BigDataLite VM was released. I must have missed the tweet and other publicity on this somewhere :-(

This is a great VM that allows you to play with the various Big Data technologies without the hassle of going through the who install and configuration thing.

If you are interested in this then here are the details of what it contains and where you can find more details.

The following components are included on Oracle Big Data Lite Virtual Machine v 2.5:

Oracle Enterprise Linux 6.4

Oracle Database 12c Release 1 Enterprise Edition (

Cloudera’s Distribution including Apache Hadoop (CDH4.6)

Cloudera Manager 4.8.2

Cloudera Enterprise Technology, including:

   Cloudera RTQ (Impala 1.2.3)

   Cloudera RTS (Search 1.2)

Oracle Big Data Connectors 2.5

   Oracle SQL Connector for HDFS 2.3.0

   Oracle Loader for Hadoop 2.3.1

   Oracle Data Integrator 11g

   Oracle R Advanced Analytics for Hadoop 2.3.1

   Oracle XQuery for Hadoop 2.4.0

Oracle NoSQL Database Enterprise Edition 12cR1 (2.1.54)

Oracle JDeveloper 11g

Oracle SQL Developer 4.0

Oracle Data Integrator 12cR1/

Oracle R Distribution 3.0.1

Go to the Oracle Big Data Lite Virtual Machine landing page on OTN to download the latest release.

Monday, March 3, 2014

OUG Ireland

The annual OUG Ireland Conference (or special event) will be on Tuesday 11th March.  Actually this year there are sessions spread over 2 days, for the first time ever in the 10+ year history of OUG Ireland. In addition to 2 days of sessions there are 7 streams of presentations on the Tuesday and then there is the RAC AttacK for the first time in Ireland.

The main conference event is on Tuesday 11th March in the DCC in Dublin. Things kick off at 9:20 with Debra Lilley welcoming everyone to the event. Then Jon Paul from Oracle in Ireland will do the opening keynote. Then we can break into the 7 streams with lots of local case studies and some well known speakers from around the world including many Oracle  ACEs and ACE Directors (my presentation is at 12:15).

The day ends up with 2 keynote presentations. There will be a keynote that will be focused on the App streams (Nadia Bendjedou, Oracle) and a separate keynote for Tech streams (by Tom Kyte).

Throughout the day there will be RAC Attack event. Look out for their tables in the exhibition hall. Again there will be some well known experts from around the world who will be on hand to help you get RAC setup and running on your own laptop, answer your questions and engage in lots of discussions about all thing Oracle. The RAC Attack Ninja will include Osama Mustafa, Philippe Fierens, Marcin Przepiorowski, Martin Bach and Tim Hall. Some of these are giving presentations throughout the day, so when they are not presenting you will find them at the RAC Attack table. Even if you are not going to install RAC drop by and have a chat with them.

On Wednesday 12th March the OUG Ireland Conference ventures into a second day of sessions. These sessions will be a full day of topics by Tom Kyte. This is certainly a day not to be missed. As they say places are limited so book your place today.

Click on the following image to view the agenda for the 2 days and to book your place on the 11th and 12th March.

I hope to see you there and make sure you say hello to me.

Wednesday, May 15, 2013

Review of Oracle Magazine-July/August 1999

The headline articles for the July/August 1999 edition of Oracle Magazine were focused on Business Intelligence and included topics on architectures, business plans, data integration, portals, dashboards, Oracle Express, data marts and data warehouses.


Other articles included:

  • 15 Rules for Enterprise Portals
    • Gear it to casual users
    • Use intuitive classifications and searching
    • Allow access to a publish/subscribe engine
    • Enable universal connectivity to information resources
    • Provide dynamic access to information resources
    • Set up intelligent routing
    • Integrate a business intelligence toolset
    • Use a server based architecture
    • Build in distributed, multithreaded services
    • Enable flexible permission granting
    • Append external interfaces
    • Provide programmatic interfaces
    • Establish internet security
    • Make it cost effective to deploy
    • Ensure that it can be customized and personalized
  • Oracle Application Server release 4.0.8 was available for beta testing and includes support for Enterprise JavaBeans. Java Servlets, Java Server Pages and allows developers to build robust self service applications quickly
  • Oracle and MapInfo joined forces to release an internet-based spatial-data analysis solution to help organizations to understand and visualize data and to identify patterns and customer trends
  • Oracle makes available Oracle iTV platform, that is a solution that makes it possible for broadcast, cable and telecommunications providers to deliver interactive services .
  • Nine tips for using Oracle Discover included:
    • Us the decode statement
    • Implement summary redirection
    • create optional conditions (filters)
    • use query statistics
    • perform regular maintenance on the query statistics tables
    • familiarize yourself with the EUL tables
    • make regular backups
    • modify registry settings
    • delete objects with care
  • Standardizing your interfaces. The first of a three part article on creating interfaces to the database. This article focused on showing how to setup and use UTL_FILE for loading data into and getting data out of the database.
  • Creating a Virtual Private Database in Oracle 8i describes how to approach such a project to implement fine grained access control and gives the following steps for setting up a VPD
    • create the application context
    • create a package that sets the context
    • create the policy function
    • associate the policy function with a table or view

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, April 25, 2013

Oracle Magazine-September/October 1999

The headline articles in the September/October 1999 edition of Oracle Magazine focused on how the Oracle technology can be used to educate staff and to keep their skills up to date. either on site or remote via on-demand training resources.


Other articles included:

  • Oracle announce that they have acquired Thinking Machine’s data mining business. This data mining product was called Darwin and is now called Oracle Data Mining. I will have a separate blog post for this announcement.
  • Oracle 8i Lite has shipped and comes with three component: Oracle Lite a single user (50K to 750K foot print), Web-to-Go allows users to access the same data and web applications both online and offline, iConnect that was a flexible architecture that enables reliable and scalable bi-directional synchronization of data and applications. Oracle 8i Lite was supported on MS Windows 95, 98 and NT, Windows CE, Palm OS and EPOC 32.
  • Oracle XML Parser for C and Oracle XML Parser for C++ are released and supports DOM and Simple API for XML (SAX) interfaces.
  • Oracle XML SQL utilities and XSQL Servlet facilitates the reading and writing of XML information from and to the Oracle database.
  • Siemens announce that they plan to build an Oracle 8i Applicance on its Primergy line of servers, based on Intel Pentium II Xeon processors.
  • Singapore Telecom’s Magix Server delivers the World’s first nationwide video on demand service. Their 12,000 subscriber were able to use a web-browser to select a video from the Megix Web side and SingTel automates the streaming of them to their computer.
  • Oracle 8i comes with some improvements in PL/SQL. These included Autonomous Transactions, Native Dynamic SQL, Invoker rights procedures, user-defined operators, new operators, bulk binds.
  • Part 2 of the article on exporting an Oracle Database to a Flat File. In this part of the article it looks at how you can use the UTL_FILE package.
  • How you can speed up query response times by using a Materialized Views. The article suggests the following steps to analyze the performance impact:
    • Configure the server parameters
    • Grant privileges to the appropriate schema
    • Create a materialized view
    • Refresh the optimizer statistics
    • Confirm that the materialized view is being used
    • Manually refresh a materialized view
  • Oracle introduces Oracle Log Miner to allow a DBA to analyze the REDO log files

Thursday, April 11, 2013

Part 1–Getting started with Statistics for Oracle Data Science projects

With all analytics or data science projects one of the first steps typically involves the extraction of data from various sources, merging the data and then performing various statistics.

The extraction and merging of the data is well covered by lots of other people blogging about how to use Oracle Data Integration (ODI), Oracle Warehouse Builder (OWB), among many others.

What I’m going to look at in this series of blog posts will be what statistical functions you might look at using in the Oracle and how to use them.

  • This the first blog post in the series will look at the DBMS_STAT_FUNCS PL/SQL package, what it can be used for and I give some sample code on how to use it in your data science projects. I also give some sample code that I typically run to gather some additional stats.
  • The second blog post will look at some of the other statistical functions that exist in SQL that you will/may use regularly in your data science projects.
  • The third blog post will provide a summary of the other statistical functions that exist in the database.

These statistical functions can be grouped into 2 main types. The first is the descriptive statistics that are available by using the DBMS_STAT_FUNCS PL/SQL package and then there is the extensive list of other SQL stats functions.  It is worth mentioning at this point that all these stats packages and functions come as standard in the database (i.e. they are FREE, you do not have to pay for an add on option for the database to use them). So once you have you Oracle database installed you can start using them. There is no need to spend money buying another stats package to do stats. All you need to know is some SQL and what the stats functions are.


One of the stats package that I use a lot is the SUMMARY function. This is part of the DBMS_STAT_FUNC PL/SQL package. This package calculates a number of common statistics for an attribute in a table. Yes that’s correct, it only gather statistics for just one attribute. So you will have to run it for all the numeric attributes in the table.

For does people who are familiar with the Oracle Data Miner tool, the explore data node produces a lot of these statistics that the SUMMARY function produces. See below for details of how to produce the Histograms.

The SUMMARY function has the following parameters


Although you will probably be running this this function on the data in your schema you still have to give the schema name. The table name is the name of the table where the data exists, the column name is the name of the column that contains the actual data you want to analyse, and the ‘s’ is the record that will be returned by the SUMMARY function that contains all the summary information.

An example of the basic script to run the SUMMARY function is given below. It will use the data that is available in the sample schemas and the views that where setup for the Oracle Data Mining sample schemas. The table (or in this case the view) that we are going to use is the MINING_DATA_BUILD_V. What we are going to do is to replicate some of what the Explore Node does in the Oracle Data Miner tool.

set serveroutput on

   s         DBMS_STAT_FUNCS.SummaryType;

   dbms_output.put_line('SUMMARY STATISTICS');
   dbms_output.put_line('Count  : '||s.count);
   dbms_output.put_line('Min    : '||s.min);
   dbms_output.put_line('Max    : '||s.max);
   dbms_output.put_line('Range  : '||s.range);
   dbms_output.put_line('Mean   : '||round(s.mean));
   dbms_output.put_line('Mode Count : '||s.cmode.count);
   dbms_output.put_line('Mode        : '||s.cmode(1));
   dbms_output.put_line('Variance    : '||round(s.variance));
   dbms_output.put_line('Stddev      : '||round(s.stddev));
   dbms_output.put_line('Quantile 5  : '||s.quantile_5);
   dbms_output.put_line('Quantile 25 : '||s.quantile_25);
   dbms_output.put_line('Median      : '||s.median);
   dbms_output.put_line('Quantile 75 : '||s.quantile_75);
   dbms_output.put_line('Quantile 95 : '||s.quantile_95);
   dbms_output.put_line('Extreme Count : '||s.extreme_values.count);
   dbms_output.put_line('Extremes      : '||s.extreme_values(1));
   dbms_output.put_line('Top 5 : '||s.top_5_values(1)||','||
   dbms_output.put_line('Bottom 5 : '||s.bottom_5_values(5)||','||


We can compare this to what is produced by the Explore Node in ODM



We can see that the Explore Node gives us more statistics to help us with understanding the data.

What Statistics does the Explore Node produce

We can see the actual SQL code that the Explore Node runs to get the statistics that are displayed in the Explore Node View Data window. To do this you will need to right-click on the Explore Node and move the mouse down to the Deploy option. The submenu will open and select ‘SQL to Clipboard’ from the list. Open a text editor and past the code. You  will need to tidy up some of this code to point it at the actual data source you want. You will get the following

SELECT /*+ inline */  ATTR, 

Where OUTPUT_1_23 is a working table that ODM has created to store intermediate results from some of its processing. In this case the Explore Node. You will need to change this to the ODM working table in your schema.

This query does not perform any of the statistics gathering. It just presents the results.

Creating our own Statistics gathering script – Part 1

The attribute names in the above SQL query tells us what statistics functions within Oracle that were used. We can replicate this statistics gathering task using the following script. There are four parts to this script. The first part gathers most of the common statistics for the attribute. The second and third parts calculate the Skewness and Kurtosis for the attribute. The final (fourth) part combines the first three parts and lists the outputs.

The one statistic function that we are not including at this point is the Histogram information. I will cover this in the next (second) blog post on statistics.

The following script has the data source table/view name included (MINING_DATA_BUILD_V) and the attribute we are going to use (AGE).  You will need to modify this script to run it for each attribute.

    basic_statistics AS (select (sum(CASE WHEN age IS NULL THEN 1 ELSE 0 END)/COUNT(*))*100 null_percent,
          count(*)    num_value,
          count(distinct age)   distinct_count,
          (count(distinct age)/count(*))*100     distinct_percent,
          avg(age)      avg_value,
          min(age)      min_value,
          max(age)     max_value,
          stddev(age)  std_value,
          stats_mode(age)   mode_value,
          variance(age)       var_value
        from   mining_data_build_v),
    skewness AS (select avg(SV) S_value
                 from (select power((age - avg(age) over ())/stddev(age) over (), 3) SV
                       from mining_data_build_v) ),
    kurtosis AS (select avg(KV) K_value
                 from (select power((age - avg(age) over ())/stddev(age) over (), 4) KV
                       from mining_data_build_v) )
SELECT null_percent,
from basic_statistics,


Part 2 – Lets do it for all the attributes in a table

In the code above I’ve shown how you can gather the statistics for one particular attribute of one table.But in with an analytics project you will want to gather the statistics on all the attributes.

What we can do is to take the code above and put it into a procedure. This procedure accepts a table name as input, loops through the attributes for this table and calculates the various statistics. The statistics are saved in a table called DATA_STATS (see below).

drop table data_stats;

create table DATA_STATS (
table_name VARCHAR2(30) NOT NULL,
column_name VARCHAR2(30) NOT NULL,
data_type VARCHAR2(106) NOT NULL,
data_length NUMBER,
data_percision NUMBER,
data_scale NUMBER,
num_records NUMBER,
distinct_count NUMBER,
null_percent NUMBER,
distinct_percent NUMBER,
avg_value NUMBER,
min_value NUMBER,
max_value NUMBER,
std_value NUMBER,
mode_value VARCHAR2(1000),
var_value NUMBER,
s_value NUMBER,
k_value NUMBER,
PRIMARY KEY (table_name, column_name));

This is one of the first things that I do when I start on a new project. I create the DATA_STATS table and run my procedure GATHER_DATA_STATS for each table that we will be using. By doing this it allows me to have a permanent records of the stats for each attribute and saves me time in having to rerun various stats at different points of the project. I can also use these stats to produces some additional stats or to produce some graphs.

He is the code for the GATHER_DATA_STATS procedure.

CREATE OR REPLACE PROCEDURE gather_data_stats(p_table_name IN varchar2) AS

   cursor c_attributes (c_table_name varchar2)
                       is SELECT table_name,
                          FROM user_tab_columns
                          WHERE table_name = upper(c_table_name);

   v_sql     NUMBER;
   v_rows    NUMBER;
   dbms_output.put_line('Starting to gather statistics for '||upper(p_table_name)||' at '||to_char(sysdate,'DD-MON-YY HH24:MI:SS'));

   FOR r_att in c_attributes(p_table_name) LOOP
      -- remove any previously generated stats
      v_sql := DBMS_SQL.OPEN_CURSOR;
      DBMS_SQL.PARSE(v_sql, 'delete from DATA_STATS where table_name = '''||r_att.table_name||''' and column_name = '''||r_att.column_name||'''', DBMS_SQL.NATIVE);
      v_rows := DBMS_SQL.EXECUTE(v_sql);
--dbms_output.put_line('delete from DATA_STATS where table_name = '''||r_att.table_name||''' and column_name = '''||r_att.column_name||'''');

      IF r_att.data_type = 'NUMBER' THEN
         dbms_output.put_line(r_att.table_name||' : '||r_att.column_name||' : '||r_att.data_type);

         -- setup the insert statement and execute
         v_sql := DBMS_SQL.OPEN_CURSOR;
         DBMS_SQL.PARSE(v_sql, 'insert into data_stats select '''||r_att.table_name||''', '''||r_att.column_name||''', '''||r_att.data_type||''', '||r_att.data_length||', '||nvl(r_att.data_precision,0)||', '||nvl(r_att.data_scale,0)||', count(*) num_value, (sum(CASE WHEN '||r_att.column_name||' IS NULL THEN 1 ELSE 0 END)/COUNT(*))*100 null_percent, count(distinct '||r_att.column_name||') distinct_count, (count(distinct '||r_att.column_name||')/count(*))*100 distinct_percent, avg('||r_att.column_name||') avg_value, min('||r_att.column_name||') min_value, max('||r_att.column_name||') max_value, stddev('||r_att.column_name||') std_value, stats_mode('||r_att.column_name||') mode_value, variance('||r_att.column_name||') var_value, null, null from '|| r_att.table_name, DBMS_SQL.NATIVE);
         v_rows := DBMS_SQL.EXECUTE(v_sql);

      ELSIF r_att.data_type IN ('CHAR', 'VARCHAR', 'VARCHAR2') THEN
         dbms_output.put_line(r_att.table_name||' : '||r_att.column_name||' : '||r_att.data_type);

         -- We need to gather a smaller number of stats for the character attributes
         v_sql := DBMS_SQL.OPEN_CURSOR;

         DBMS_SQL.PARSE(v_sql, 'insert into data_stats select '''||r_att.table_name||''', '''||r_att.column_name||''', '''||r_att.data_type||''', '||r_att.data_length||', '||nvl(r_att.data_precision,0)||', '||nvl(r_att.data_scale,0)||', count(*) num_value, (sum(CASE WHEN '||r_att.column_name||' IS NULL THEN 1 ELSE 0 END)/COUNT(*))*100 null_percent, count(distinct '||r_att.column_name||') distinct_count, (count(distinct '||r_att.column_name||')/count(*))*100 distinct_percent, null, null, null, null, stats_mode('||r_att.column_name||') mode_value, null, null, null from '|| r_att.table_name, DBMS_SQL.NATIVE);
         v_rows := DBMS_SQL.EXECUTE(v_sql);

-- dbms_output.put_line('insert into data_stats select '''||r_att.table_name||''', '''||r_att.column_name||''', '''||r_att.data_type||''', '||r_att.data_length||', '||nvl(r_att.data_precision,0)||', '||nvl(r_att.data_scale,0)||', count(*) num_value, (sum(CASE WHEN '||r_att.column_name||' IS NULL THEN 1 ELSE 0 END)/COUNT(*))*100 null_percent, count(distinct '||r_att.column_name||') distinct_count, (count(distinct '||r_att.column_name||')/count(*))*100 distinct_percent, null, null, null, null, stats_mode('||r_att.column_name||') mode_value, null, null, null from '|| r_att.table_name);
         when others then

         dbms_output.put_line('Unable to gather statistics for '||r_att.column_name||' with data type of '||r_att.data_type||'.');
      END IF;

   dbms_output.put_line('Finished gathering statistics for '||upper(p_table_name)||' at '||to_char(sysdate,'DD-MON-YY HH24:MI:SS'));

Then to run it for a table:

exec gather_data_stats('mining_data_build_v');

We can view the contents of the DATA_STATS table by executing the following in SQL*Plus or SQL Developer

select * from DATA_STATS;