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

Thursday, June 7, 2012

Review of Oracle Magazine–May/June 1996

The headline articles for the May/June 1996 edition of Oracle Magazine was an introduction to the Oracle Universal Server and how it can be used to give a flexible architecture for your growing organisation


Other articles included:

  • Oracle Magazine goes interactive with the launch of the The initial site had Oracle Magazines from 1994 and 1995, along with subscription information, a Q&A area and a WebMaster comic strip
  • There was a preview of Larry’s Network Computer (NC). It was supposed to be a low cost computing appliance optimised to operate on the internet and other highly distributed networks such as corporate LANs, is designed to provide users with simple, economical and robust communications and access to information. The NC will include a Web Terminal, ISDN Video Phone, Set-top Box, Two-way Pager and a Personal Digital Assistant.
  • Oracle Developer/2000 and Designer/2000 Release 1.3 is announced
  • There is a review of how Cisco standardised on using Oracle 7 and how they went about the selection and implementation of Oracle Applications including financial, manufacturing and human resources applications.
  • Integrating the WWW and Oracle Order Entry. Companies can now have an instant presence to the world but also, by examining the web-server activity logs, they gain the ability to see who the buyers are and who just browses

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.

Monday, June 4, 2012

OTN Developer Days–Dublin 12th to 14 June

The OTN Developer Days events return to the Oracle Dublin office in East Point this month from the 12th to the 14th.

These are free events, but places are limited, and allow you to get some hands-on training with these tools. Depending on the day and the topic there is a mixture of lecture and workshop, to just being a hands-on workshop.

12th June – Golden Gate 11g, Oracle Data Integrator 11g and Enterprise Data Quality (full day : 9:45-17:00)

13th June – Partitioning and Advanced Compression (9:45-13:00)

14th June – Unlocking the value of Oracle Database 11g Core Features (9:45-15:00)

These are free events and you will even get a free lunch from Oracle.

Monday, May 28, 2012

VM for Oracle Data Miner

Recently the OTN team have updated the ‘Database App Development’  Developer Day virtual machine to include Oracle DB and SQL Developer 3.1.  This is all you need to try out Oracle Data Miner.

So how do you get started with using Oracle Data Miner on your PC. The first step is to download and install the latest version of Oracle VirtualBox.


The next step is to download and install the OTN Developer Day appliance. Click on the above link to go to the webpage and follow the instructions to download and install the appliance. Download the first appliance on this page ‘Database App Development’ VM. This is a large download and depending on your internet connection it can take anything from 30 minutes to hours. So I wouldn’t recommend doing this over a wifi.

When you start up the VM your OS username and password is oracle. Yes it is case sensitive.

When the get logged into the VM you can close or minimise the host window


There are two important icons, the SQL Developer and the ODDHandsOnLab.html icons.

The ODDHandsOnLab.html icon loads a webpage what contains a number of tutorials for you to follow.


The tutorial we are interest in is the Oracle Data Miner Tutorial. There are 4 tutorials given for ODM. The first two tutorials need to be followed in the order that they are given. The second two tutorials can be done in any order.

If you have not used SQL Developer before then you should work through this tutorial before starting the Oracle Data Miner tutorials.

The first tutorial takes you through the steps needed to create your ODM schema and to create the ODM repository within the database. This tutorial will only take you 10 to 15 minutes to complete.

In the second tutorial you get to use the ODM to build your first ODM model. This tutorial steps your through how to get started with an ODM project, workflow, the different ODM features, how to explore the data, how to create classification models, how to explore the model and then how to apply one of these models to new data. This second tutorial will take approx. 30 to 40 minutes to complete.

It is all very simple and easy to use.

Wednesday, May 23, 2012

The First Oracle Magazine–Volume1 Issue 1

In my last blog post I reviewed the contents of the March/April 1996 edition of Oracle Magazine. While doing this I noticed on the Editors Pages, Julie Gibbs gave a review of the very first Oracle Magazine from 1987.

Here is the front cover of the first Oracle Magazine. I’ve scanned the editors page, containing the review. Just click on the image below.


The first edition had just 12 pages of content.

Here is the extract from the editors page March/April 1996:

The picture you see on this page is of the first cover of Oracle Magazine-Yolume I, Number I, June 7987. Yes, we are celebrating our tenth anniversary this year. Ten years may not seem like much in other industries, but in high tech, it's a veritable lifetime. Companies and products have come and gone-where ate you now, VisiCalc? How about the PC jr? And who knew in 1987 that the Internet would be the dominant topic of the high-tech press in 1996?

What was in the first issue of Oracle Magazine? Here's a sampling of articles in that 12-page fledgling publication: New Network Expands Customer Support (24-hour online support was introduced June I, 1987);
Oracle Version 5 .1 Released; Oracle RDBMS Now Available on Wang VS; Oracle Exceeds First HaIf Forecast (revenues for the first half totalled almost $46million); UniForum: Site of Oracle UNIX Announcements (at the time, Oracle ran on more than 20 platforms, including new
UNIX ports to NCR, Sun, DEC Ultrix,Sequent, Altos, and Plexus); SQL Declared Standard Language by ANSI; Double DEC Awards for Oracle (Digital Review's Target Awards gave Oracle first place for "Best Database Management Product" and the No. I rating in the "Digital News 50").

Some people say that Oracle Magazine existed before 1987. Oracle did have a newsletter type publication.

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.

Tuesday, May 22, 2012

Review of Oracle Magazine–March/April 1996

The headline articles for the March/April 1996 edition of Oracle Magazine was Oracle’s first or early articles on Data Warhousing, including DW Architectures, what Oracle tools you can use, multi-dimensional analysis, Oracle Express and future directions of data warehouses.


Julie Gibbs, the editor of Oracle Magazine, wondered ‘What will be hot in 2005?’. Some of her predictions/suggestions were:

  • Will Larry Ellison’s NC provide every home with a $500 internet box
  • What will be the 3 biggest software companies and were any of them around in 1995
  • How many people will use the internet everyday
  • Will the internet be censored ? How and by whom ?
  • Or will the internet be passe and will virtual reality be a reality
  • What will be the size of the largest data warehouse
  • Will Apple still exist
  • Will you be reading your magazines in print or online
  • Will your company have a woman CEO
  • How many people will be telecommuting
  • Will every desktop have built in video conferencing so that you can talk to your coworkers

Other articles included:

  • Oracle Interoffice Suite was released and comprised Messaging, Document and Workflow servers based on Oracle 7.3. The product provided groupware functions, such as electronic mail, messaging, scheduling, directory services, document management, workflow and conferencing.
  • Oracle 7.3 new features included Oracle Enterprise Manager, Oracle Software Manager, SQL*Net 2.3, advanced replication and Oracle ConText.
  • How to rename your database. It is not always optimal for a database to keep the name it was born with. A step by step guide is given on how to do this without loosing any data!
  • A case study is presented from NeXT Computer on how to audit and clean up your Oracle Applications data as you prepare to upgrade to Release 10. These included:
    • Review Usernames and unused responsibilities
    • Unused menus and menu options
    • Are outdate concurrent requests being purged
    • Unused printers
    • Identify cluttered production libraries
    • Unused custom concurrent processes
    • Unused database objects
    • Inactive vendors and invalid distribution sets
    • Unused payment terms
    • Closed bank accounts
    • Protecting your budgets
    • Obsolete journal sources
    • Invalid price lists
    • Unbooked orders and unclosed orders
    • Unused payment terms, transaction types, units of measure and inactive sales people
  • How to design a database for OLAP. Most of the following steps still stand today for designing your star-schemas
    • Define the question (business function/area)
    • Use Normalized logic
    • Identify Dimensions
    • Create Hierarchies
    • Identify Attributes
    • Identify Measures
    • Add Calculations
  • There was a review of the very first Oracle Magazine that was published in June 1987.  Watch this space, as I will be posting the details soon.

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.

Wednesday, April 25, 2012

Oracle Magazine-May/June 1995

The headline articles for the May/June edition of Oracle Magazine included one of the first articles on Data Centers,using the prebuilt packages in PL/SQL and how to use object-oriented programming techniques in Oracle Forms 4.5


Other articles included:

Oracle 7 Internet Access Kit also called the Oracle World Wide Web Interface Kit. Developers could use this kit developers can create links between web servers and Oracle 7 databases. The Oracle WWW Interface Kit included:

  • Oracle’s Web-Oracle-Web (WOW) PL/SQL gateway to web servers
  • Oracle’s PL/SQL editor and development tool for creating dynamic PL/SQL
  • PERL, a freeware interpreting programming language
  • ORAPERL, an extension to PERL for use with Oracle 7 databases
  • ORAYWWW, a PERL gateway
  • WORA, a user-friendly database browser written in Pro*C
  • DECOUX, a post-processing gateway that inserts query results from a Oracle 7 database into HyperText Markup Language (HTML) documents
  • Navigator, a collection of PERL scripts and one Pro*C module that enables administrators to quickly configure HTML forms for end user queries
  • Text Search System (TSS), an indexing and free text search system
  • MORE, a collection of Pro*C gateways for maintaining an information repository
  • HotMetal, a HTML editor

Fee access to Oracle ConText. Oracle customers were offered an opportunity to try ConText before you buy. According the the article Oracle ConText is a revolutionary linguistic analysis program that automatically generates back-of-book indexes and reduces the volume of text to a summary.

Ken Jacobs gives the second part of his in-depth article on the latest release of Oracle 7.1 and Oracle 7.2 databases. Ken was vice president of Product Planning and has been working with Oracle since version 2 of the database.

Mike Ault has an article on using DBMS_PIPES PL/SQL package, how to access the shared pool using the DBMS_SHARED_POOL PL/SQL package and how to use the DBMS_OUTPUT procedure.

How big is your database ?  Oracle conducted a survey of all its customers to see what was the typical size of their databases and how many users each database would have. The following diagrams gives the results of this survey and compares the 1994 results with the results from 1993. We can see that there was a bit of a jump on the size of the databases but the number of users increased significantly



So must customers had databases in the 2GB to 10GB. How things have changed. If the survey was conduced for 2012 what results would be get ?

In 1995, Oracle Open World took place in Yokohama, Japan between 18th and 19th January. It had 130 seminars and sessions.  In 2012 OOW will have over 500 sessions and will run over 5 days!

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.

How big was your Oracle Database in 1993 & 1994

I’m in the middle of writing my summary of the May/June 1995 edition of Oracle Magazine (that blog post is coming soon). There was a one article about a survey that Oracle conducted of its customer on how big their databases were and the number of users for their databases.

The follow diagrams gives the summary results of these surveys.

We can see that there was a bit of a jump on the size of the databases but the number of users increased significantly



So must customers had databases in the 2GB to 10GB. How things have changed. If the survey was conduced for 2012 what results would be get ?

Does anyone know if Oracle has published similar survey results in the last few years ?

Thursday, April 12, 2012

Oracle Magazine–March 1995

In 1995 we have a change to the frequency of publication of Oracle Magazine. It is not published every 2 two months with 6 editions each year, as it is still the case.

The headline articles in the March/April 1994 edition of Oracle Magazine included Integrating Unstructured Information, Minimizing Client/Server Network Traffic with Oracle Forms 4.0, Relational Objects and how the Canadian Postal Service was using Oracle Technology to deliver mail on time.


Other articles include:

Dynamic SQL Comes to PL/SQL: Introduces us to the DBMS_SQL package. A useful quick reference one pager is given giving the details of each of the 15 procedures and functions, including, BIND_VARIABLE, CLOSE_CURSOR, COLUMN_VALUE, DEFINE_COLUMN, EXECUTE, EXECUTE_AND_FETCH, FETCH_ROWS, IS_OPEN, LAST_ERROR_POSITION, LAST_ROW_COUNT, LAST_ROW_ID, LAST_SQL_FUNCTION_CODE, OPEN_CURSOR, PARSE, VARIABLE_VALUE.

Personal Oracle 7 for Windows gets released and it now has a graphical administration tool to allow us to manage the database, including users, backup and recovery managers along with export/import and SQL*Loader and Oracle Objects for OLE. The hardware requirements included 8Mb of RAM and less than 30Mb of hard-disk space. How things have changed!!!. It was capable of running on Window 3.1 and was Windows 95 ready.

Rollback segments in in Oracle 7 allows use to now effectively manage our transactions. It gave the following recommendations:

  • Create a separate tablespace for rollback segments for administrative and monitoring purposes
  • Se the size of the tablespace according to the number and storage parameters of the rollback segments
  • Place your rollback segment tablespace on a separate disk device, if possible. Consider total I/O activities and spread I/O over available disk devices
  • Create one rollback segment for every four concurrent transactions
  • Don’t create more than 50 rollback segments
  • Create smaller rollback segments for OLTP applications. This increases the likelihood that rollback segments will be cached in SGA according to the least recently used algorithm, resulting in performance gain
  • Create larger rollback segments for decision support or back processing applications
  • Set initial to you average transaction size, set OPTIMAL to four to eight times larger than INITIAL

Oracle Magazine goes digital and interactive with a CD-ROM containing every issue of published in the previous two years and also contained a map of Oracle offices worldwide.

To view the cover page and the table of contents click on the above image or click here.

My Oracle Magazine Collection can be found here. You will find links to my blog posts on previous editions.

Thursday, April 5, 2012

OTN Workshop Days in Dublin 17-

Oracle in Ireland have arranged a number of FREE Oracle Technology Network Hands on Workshops.

17th April : Database Firewall

18th April : Oracle Real Application Testing

19th April : Database 11g R2 New Features

20th April : Business Integration using Oracle SOA Suite 11g

All the workshops are in the Oracle offices in East Point, in Dublin.

To register for these events

Tuesday, March 20, 2012

Oracle Magazine–Winter 1994

The headline articles of the Winter 1994 edition of Oracle Magazine included topics on how an energy company migrated to client/server, towards the paperless office (it never really happened!!), Business Processing Reengineering and document management.


There was a noticeable number of articles and advertisements on reporting tools. The tools mentioned included Oracle Browser version 2.0, Oracle Data Query, Impromptu, Intelligent Query (IQ), Visual CyberQuery, R&R Report Writer, ReportSmith and SQL Assist

There was also an article called ‘An Elegant Approach to Report Generation’ and looked at how you could take the ASCII file produced by a SQL report and load it into a WordPerfect template.

We have the first mention of on Oracle Certification. ‘Database administrators and application developers can now become Oracle Certified to demonstrate their skill and competence in using Oracle systems. ……. Upon successfully completing the exam – which covers such areas as using SQL and Oracle Forms, tuning applications, and administrating and Oracle database – students will be awarded with the title of Oracle Certified Administrator and/or Oracle Certified Application Developer’.

Oracle announces the launch of their Real-Time Support System. Unlike My Oracle Support we have today, back in 1994 you needed a modem and remote communications package that could emulate a SQL*Forms support terminal type.

There was an article, by Kevin Loney, on how you could add Help documentation to your Oracle Forms applications using Oracle*Text Retrieval 2.0.

Finally there was an article by the IOUG about their International Oracle User Week in 1993. This event was held in the Orange County Convention Center in Orlando FL, where there was an attendance of 3,300 participants. This conference has since been expanded and is now call COLLABORATE and will be in Las Vegas in 2012 on 22-26 April (I wish I could be there).

To view the cover page and the table of contents click on the above image or click here.

My Oracle Magazine Collection can be found here. You will find links to my blog posts on previous editions.

Wednesday, March 7, 2012

Oracle Magazine–Fall 1993

The headline articles of the Fall 1993 edition of Oracle Magazine included topics on Multimedia Servers, Free Space Defragmentation and Geographic Information Systems.


Apart from the head line articles most of the other articles were about product announcements/updates and about how certain types of companies were using IT.

The main technical article was by Kevin Loney on freeing up free space in an Oracle 6 database using defragmentation. This was a popular topic for Oracle version 6 and 7, when disk space was expensive. It is less of an issue today.

There was an article on the Digital Highway and in Multimedia Servers. These were early indicators of Larry’s investing in the Video On-Demand servers.

The first maintenance release of Oracle 7 database was announced (Oracle 7.1), with enhancements centred around:

  • Parallel Operations : performance increases, splitting of query execution, data loading and index creation tasks and execute them concurrently on multiple CPS. Permits multiple sessions running SQL*Loader using the fast bulk load path
  • Application Development : Users can now embed PL/SQL functions in SQL statements and reference those functions in SQL expressions as if they are build-in.
  • Administration : Tablespaces can be placed in read-only mode. Provides a parallel recovery mechanism.
  • Standards Compliance : Oracle 7.1 is compliance with the SQL92 Entry Level standard. The ORDER BY clause can not reference SELECT list items names with a column alias.

Some of the products discussed in articles included Pro*C version 2.0, SQL*Module version 1.0, OracleWare, Oracle Graphics version 2.0, SQL*Net version 2 on MVS, SQL*Connect to SQL/400 and Oracle Card 2.0.

Apart from these product related articles, some others of interest included an advertisement for ERwin which was a product owned at that time by Logic Works.

There was a short article on the IOUG 12th Annual User Group Conference in September.

To view the cover page and the table of contents click on the above image or click here.

My Oracle Magazine Collection can be found here.

My previous posts on Oracle Magazine
  - Winter 1993
  - Fall 1992

Monday, March 5, 2012

Rewards received in the Post today

Today I arrived home from work to find that I had received 2 parcels.

In the first parcel was


Thank you Oracle ACE Programme and OTN.

In the second parcel was an IOUG Select Contributors T-shirt. I received this because I had an article in the Q1 2012 edition of IOUG Select, on Oracle Data Miner.  Thank you IOUG.

Monday, February 20, 2012

Oracle Magazine–Winter 1993

The lead articles in the first Oracle Magazine of 1993 (winter) were about how the Oracle database was being used in the Toronto Stock Exchange and in the Municipio de Panama of Panama City.


Other articles of interest included

  • Cary Millsap on the Optimal Flexible Architecture. Was this Cary’s first article in Oracle Magazine ? OFA provided a set of guidelines on how to install Oracle so that performance is not affected as your database environment grows. Cary gives 3 basic rules for creating and ODA compliant Oracle database (the full set is in the article).
    1. Establish and orderly operating system directory structure in which any database file can be stored on any disk resources (applicable to operating systems with hierarchical directory structures only).
    2. Separate groups of segments with different behavior into different tablespaces.
    3. Maximize database reliability and performance by separating database components across disk resources
  • There was 2 articles that looked at OODBMS. Oracle made their first purchase of an OODBMS company in 1994 and were trying to release their first OODBMS product in 1995. This never happened and their reverted to integrating some OODBMS features into the database instead.
  • Oracle Glue is released. This product has gone through a number of name changes since. What is it called now ?
  • There was a couple of articles discussing using UNIX or ALPHA server machines, when VLDBs were considered extremely large at 32GB. I remember there was one Bank who had the largest database in Ireland in 1993 and it was 4GB. I wonder what it is now.
  • Business Object announces the release of their DOS and Windows end user data access tool for relational database tool.
  • There was a number of adverts from companies providing tools and support for converting your Oracle Forms Version 2 to Forms 3


Click on the image above to view the PDF of the table of contents page.

My Oracle Magazine Collection can be found here.

Monday, January 9, 2012

New additions to my Oracle Magazine Collection

I put out a call a few months ago looking for help in completing my Oracle Magazine collection. I also had a letter in the Nov/Dec 2011 edition of Oracle Magazine looking for help.

The following people contacted me in December offering to send me some of my missing editions.

Lisa Dobson - Oracle DBA at Durham University and Vice President of UKOUG
Debra Lilley - President of UKOUG
Christian Antognini - Trivadis AG, Switzerland
Dan Vlamis - Vlamis Software Solutions, MO, USA

Many thanks for your donations.

It seems that people don’t keep their Oracle Magazines!

I’m now just missing a very small number of editions since 1992.  Can anyone else help ?

Does anyone have any Oracle Magazines from before 1992 ?

The following table gives the current collection (printed editions). The grey boxes are the editions that I’m still missing, and keep an eye on my Oracle Magazine page for updates.

oracle mag3

Friday, January 6, 2012

ODM 11gR2–Real-time scoring of data

In my previous posts I gave sample code of how you can use your ODM model to score new data.

Applying an ODM Model to new data in Oracle – Part 2

Applying an ODM Model to new data in Oracle – Part 1

The examples given in this previous post were based on the new data being in a table.

In some scenarios you may not have the data you want to score in table. For example you want to score data as it is being recorded and before it gets committed to the database.

The format of the command to use is

prediction(ODM_MODEL_NAME USING <list of values to be used and what the mode attribute they map to>)

prediction_probability(ODM_Model_Name, Target Value, USING <list of values to be used and what model attribute they map to>)

So we can list the model attributes we want to use instead of using the USING *  as we did in the previous blog posts

Using the same sample data that I used in my previous posts the command would be:

Select prediction(clas_decision_tree
20 as age,
'NeverM' as cust_marital_status,
'HS-grad' as education,
1 as household_size,
2 as yrs_residence,
1 as y_box_games) as scored_value
from dual;


Select prediction_probability(clas_decision_tree, 0
20 as age,
'NeverM' as cust_marital_status,
'HS-grad' as education,
1 as household_size,
2 as yrs_residence,
1 as y_box_games) as probability_value
from dual;


So we get the same result as we got in our previous examples.

Depending of what data we have gathered we may or may not have all the values for each of the attributes used in the model. In this case we can submit a subset of the values to the function and still get a result.

Select prediction(clas_decision_tree
20 as age,
'NeverM' as cust_marital_status,
'HS-grad' as education) as scored_value2
from dual;


Select prediction_probability(clas_decision_tree, 0
20 as age,
'NeverM' as cust_marital_status,
'HS-grad' as education) as probability_value2
from dual;


Again we get the same results.

Saturday, December 31, 2011

My first set of Oracle Products

I started working with Oracle back in 1993 and my first project involved working with Oracle 5, Forms 2.3 and for reports RPT.

The Oracle Database and tools were very simple back then, but there was lots of “features” to work around.

Check out this video, for a short demo of Oracle 5 and Forms 2

Friday, December 2, 2011

I’m an Oracle ACE

At 5:20pm today (Friday 2nd December), I received an email from the Oracle ACE program.  I had been nominated for the award of Oracle ACE.

“You have been chosen based on your significant contribution and activity in the Oracle technical community.  Like your fellow Oracle ACEs, you have demonstrated a proficiency in Oracle technology as well as a willingness to share your knowledge and experiences with the community.”

I am so honoured, considering the experts from around the world that are members of the Oracle ACE program.

The Oracle ACE Award is issued by the Oracle Corporation and the award is made to people who are know for their strong credentials in the Oracle community as enthusiasts, advocates and technical knowledge.

Wednesday, November 9, 2011

ODM–PL/SQL API for Exporting & Importing Models

In a previous blog post I talked about how you can take a copy of a workflow developed in Oracle Data Miner, and load it into a new schema.
When you data mining project gets to a mature stage and you need to productionalise the data mining process and model updates, you will need to use a different set of tools.

As you gather more and more data and cases, you will be updating/refreshing your models to reflect this new data. The new update data mining model needs to be moved from the development/test environment to the production environment. As with all things in IT we would like to automate this updating of the model in production.
There are a number of database features and packages that we can use to automate the update and it involves the setting up of some scripts on the development/test database and also on the production database.

These steps include:

  • Creation of a directory on the development/test database
  • Exporting of the updated Data Mining model
  • Copying of the exported Data Mining model to the production server
  • Removing the existing Data Mining model from production
  • Importing of the new Data Mining model.
  • Rename the imported mode to the standard name

The DBMS_DATA_MINING PL/SQL package has 2 functions that allow us to export a model and to import a model. These functions are an API to the Oracle Data Pump. The function to export a model is DBMS_DATA_MINING.EXPORT_MODEL and the function to import a model is DBMS_DATA_MINING.IMPORT_MODEL.The parameters to these function are what you would expect use if you were to use Data Pump directly, but have been tailored for the data mining models.

Lets start with listing the models that we have in our development/test schema:

SQL> connect dmuser2/dmuser2
SQL> SELECT model_name FROM user_mining_models;


Create/define the directory on the server where the models will be exported to.

CREATE OR REPLACE DIRECTORY DataMiningDir_Exports AS 'c:\app\Data_Mining_Exports';

The schema you are using will need to have the CREATE ANY DIRECTORY privilege.

Now we can export our mode. In this example we are going to export the Decision Tree model (CLAS_DT_1_6)

The function has the following structure

     filename IN VARCHAR2,
     directory IN VARCHAR2,
     model_filter IN VARCHAR2 DEFAULT NULL,
     operation IN VARCHAR2 DEFAULT NULL,
     remote_link IN VARCHAR2 DEFAULT NULL,

If we wanted to export all the models into a file called Exported_DM_Models, we would run:

DBMS_DATA_MINING.EXPORT_MODEL('Exported_DM_Models', 'DataMiningDir');

If we just wanted to export our Decision Tree model to file Exported_CLASS_DT_Model, we would run:

DBMS_DATA_MINING.EXPORT_MODEL('Exported_CLASS_DT_Model', 'DataMiningDir', 'name in (''CLAS_DT_1_6'')');

Before you can load the new update data mining model into your production database we need to drop the existing model. Before we do this we need to ensure that this is done when the model is not in use, so it would be advisable to schedule the dropping of the model during a quiet time, like before or after the nightly backups/processes.


Warning : When importing the data mining model, you need to import into a tablespace that has the same name as the tablespace in the development/test database.  If the USERS tablespace is used in the development/test database, then the model will be imported into the USERS tablespace in the production database.

Hint : Create a DATAMINING tablespace in your development/test and production databases. This tablespace can be used solely for data mining purposes.

To import the decision tree model we exported previously, we would run

DBMS_DATA_MINING.IMPORT_MODEL('Exported_CLASS_DT_Model', 'DataMiningDir', 'name=’CLAS_DT_1_6''', 'IMPORT', null, null, 'dmuser2:dmuser3');

We now have the new updated data mining model loaded into the production database.

The final step before we can start using the new updated model in our production database is to rename the imported model to the standard name that is being used in the production database.


Scheduling of these steps
We can wrap most of this up into stored procedures and have schedule it to run on a semi-regular bases, using the DBMS_JOB function. The following example schedules a procedure that controls the importing, dropping and renaming of the models.

DBMS_JOB.SUBMIT(jobnum.nextval, 'import_new_data_mining_model', trunc(sysdate), add_month(trunc(sysdate)+1);

This schedules the the running of the procedure to import the new data mining models, to run immediately and then to run every month.

Saturday, November 5, 2011

What Conference ? If I had the time and money

If I had lots of free time and enough money what conferences would I go to around the world. I regularly get asked for recommendations on what conferences should a person attend. It all depends on what you want to get out of your conference trip. Be is training, education, information building, networking, etc. or to enjoy the local attractions.

The table below is my preferred list of conferences to attend. All of the conferences below are focused on two main areas. The first area is Oracle  and the second area is that of Data Mining/Predictive Analytics.

I hope you find the list useful. If you can recommend some others let me know.

Month Conference


Annual Ireland Oracle Conference – Dublin, Ireland

Predictive Analytics World – USA (San Francisco)

Text Analytics World

Hotsos Symposium


Collaborate (IOUG Conference USA)

Enterprise Data World (USA)

Miracle OpenWorld (Denmark)


OUG Harmony (Finland)


Oracle Development Tools User Group Kaleidoscope (Kscope)

Data Governance – Summer Conference

Oracle Benelux User Group Conference


VirtaThon – Online Oracle Conference


ACM SIGKDD Conference on KDD & Data Mining


Oracle Open World – San Francisco, USA

Predictive Analytics World – USA (New York)

SAS Analytics Conference


TDWI World Conference

Data Governance – Winter Conference (USA)

Predictive Analytics World – UK

International Conference on Data Mining & Engineering (ICDMKE)

Australia Oracle User Group Conference

Germany Oracle User Group Conference (DOAG)


Annual UKOUG Conference – Birmingham, UK

IEEE International Conference on Data Mining (ICDM)

Oracle Open World Latin America

There is a lot of conferences in the October, November and December months. Some of these are on overlapping dates, which is a pity. Perhaps the organisers of some of these conferences. Also during the January and February months there does not seem to be any conferences in the areas.

If you would like to sponsor a trip to one or more of these then drop me an email Smile

Thursday, November 3, 2011

ODM 11.2 Data Dictionary Views.

The Oracle 11.2 database contains the following Oracle Data Mining views. These allow you to query the database for the metadata relating to what Data Mining Models you have, what the configurations area and what data is involved.


Describes the high level information about the data mining models in the database.  Related views include DBA_MINING_MODELS and USER_MINING_MODELS.

Attribute Data Type Description
OWNER Varchar2(30) NN Owner of the mining model
MODEL_NAME Varchar2(30) NN Name of the mining model
MINING_FUNCTION Varchar2(30) What data mining function to use
ALGORITHM Varchar2(30) Algorithm used by the model
CREATION_DATE Date NN Date model was created
BUILD_DURATION Number Time in seconds for the model build process
MODEL_SIZE Number Size of model in MBytes
COMMENTS Varchar2(4000)  
Lets query the my DMUSER2 data mining schema. This was created during a previous post where we exported some ODM models from schema and loaded them into DMUSER2 schema

SELECT model_name, 

-------------  ---------------- -------------------------- -------------- ----------
CLAS_SVM_1_6   CLASSIFICATION    SUPPORT_VECTOR_MACHINES                     3      .1515
CLAS_DT_1_6    CLASSIFICATION    DECISION_TREE                               2      .0842
CLAS_GLM_1_6   CLASSIFICATION    GENERALIZED_LINEAR_MODEL                    3      .0877
CLAS_NB_1_6    CLASSIFICATION    NAIVE_BAYES                                 2      .0459


Describes the attributes of the data mining models.  Related views are DBA_MINING_MODEL_ATTRIBUTES and USER_MINING_MODEL_ATTRIBUTES.

Attribute Data Type Description
OWNER Varchar2(30) NN Owner of the mining model
MODEL_NAME Varchar2(30) NN Name of the mining mode
ATTRIBUTE_NAME Varchar2(30) NN Name of the attribute
ATTRIBUTE_TYPE Varchar2(11) Logical type of attribute
NUMERICAL – numeric data
CATEGORICAL – character data
DATA_TYPE Varchar2(12) Data type of attribute
DATA_LENGTH Number Length of data type
DATA_PRECISION Number Precision of a fixed point number
DATA_SCALE Number Scale of the fixed point number
USAGE_TYPE Varchar2(8) Indicated if the attribute was used to create the model (ACTIVE) or not (INACTIVE)
TARGET Varchar2(3) Indicates if the attribute is the target

If we take one of our data mining models that was listed about and select what attributes are used by that model;

SELECT attribute_name,
from all_mining_model_attributes
where model_name = 'CLAS_DT_1_6';

------------------------------ ----------- -------- ---
AGE                            NUMERICAL   ACTIVE   NO
Y_BOX_GAMES                    NUMERICAL   ACTIVE   NO

The first thing to note here is that all the attributes are listed as ACTIVE. This is the default and will be the case for all attributes for all the algorithms, so we can ignore this attribute in our queries, but it is good to check just in case.

The second thing to note is for the last row we have the AFFINITY_CARD has a target attribute value of YES. This is the target attributes used by the classification algorithm.


Describes the setting of the data mining models. The settings associated with a model are algorithm dependent. The Setting values can be provided as input to the model build process. Alternatively, separate settings table can used.  If no setting values are defined of provided, then the algorithm will use its default settings.

Attribute Data Type Description
OWNER Varchar2(30) NN Owner of the mining model
MODEL_NAME Varchar2(30) NN Name of the mining model
SETTING_NAME Varchar2(30) NN Name of the Setting
SETTING_VALUE Varchar2(4000) Value of the Setting
SETTING_TYPE Varchar2(7) Indicates whether the default value (DEFAULT) or a user specified value (INPUT) is used by the model

Lets take our previous example of the 'CLAS_DT_1_6' model and query the database to see what the setting are.

column setting_value format a30
select setting_name, 
from all_mining_model_settings
where model_name = 'CLAS_DT_1_6';

SETTING_NAME            SETTING_VALUE                SETTING
----------------------- ---------------------------- -------
ALGO_NAME               ALGO_DECISION_TREE           INPUT
PREP_AUTO               ON                           INPUT
TREE_TERM_MINPCT_NODE   .05                          INPUT
TREE_TERM_MINREC_SPLIT  20                           INPUT
TREE_TERM_MINPCT_SPLIT  .1                           INPUT
TREE_TERM_MAX_DEPTH     7                            INPUT
TREE_TERM_MINREC_NODE   10                           INPUT