Scott/Tiger in Oracle

Almost everyone starting out with Oracle have come across the schema SCOTT.

So who was SCOTT?

Bruce Scott was employee number 4 at Oracle back in the 1970s and was part of the team responsible for architecting and writing version 1, 2 and 3 of the Oracle Database.

So he set up the first schema for testing and demo-ing how to use SQL in the Oracle Database. The EMP and DEPT tables and the data that we still see in this schema is still based on the original data that he created.

How did he come up with the TIGER password ?  Tiger was the name of his cat!!!

Sten Vesterli of Scott/Tiger Group

Bruce co-founded Gupta Technology (now known as Centura Software) in 1984 with Umang Gupta, and later became CEO and founder of PointBase, Inc.

Call for paper for Oracle Scene-Winter 2012 Edition

The call for papers is now open for Oracle technical papers for publication in the UKOUG Oracle Scene magazine.

The submission date for completed papers is 24th August. 

To get more information of paper guidelines and submission details go to,

http://www.ukoug.org/what-we-offer/oracle-scene/

The Winter edition will be published online and in print format around the end of October. This will be in time for the EPM & Hyperion, JDE and UKOUG 2012 conferences. So this is a chance to get your message across to these communities.

Did you get a presentation accepted for the UKOUG annual conference or were you disappointed ?  Maybe you could consider writing a paper based on your presentation and submit it for consideration.

How about advertising in Oracle Scene. Over the past couple of editions we have had a significant increase in readership, with readership from countries around the world.

Over the past few years Oracle Scene has moved from being a regional User Group magazine to having a readership in 30+ countries around the world.

Why am I writing this post ?  I’m a deputy editor of Oracle Scene

Review of Oracle Magazine–July/August 1996

The headline articles for the July/August1996 edition of Oracle Magazine was on how to balance security and communication in a distributed world, extending Oracle power objects applications and automating Oracle tuning

Oracle articles included:

• Oracle released three of its products on the web. These included Oracle Web Customers, Oracle Web Suppliers and Oracle Web Employees. They aimed to help make it possible for companies to conduct secure business transactions over the internet and corporate intranets. They also shipped Oracle Workflow to help support the implementation of these new products
• Oracle Express Analyzer, an object-oriented reporting and analysis tool had its second release
• UBS Bank implements the Oracle based operational accounting system, with over 800,000 input records daily and over 3,000 cost centre reports that needed different levels of summarisation. The new application allows the executives to view information in virtually any format choosing from 120,000 multi-level, multi-view reports.
• The Egyptian Stock Exchange and Capital Market Authority implements a new trading system build on Oracle
• Don Burleson in his article on Automating Oracle Tuning gives a number of scripts that would assist the DBA in finding out what is going on in the database. So instead of purchasing some expensive tools, all you needs was these scripts UTKBSTAT/UTLESTAT.

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.

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 ?

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.

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

http://www.oracle.com/us/dm/34862-splashpage-1438215.html

Tom Kyte is in Belfast 16th April

The Oracle User Group has organised for Tom Kyte the famous Oracle evangelist to come Belfast to give a one day seminar.

The seminar will be in the Hilton in Belfast.

Some of the topics to be covered on the day include:

• 5 things you probably didn’t know about SQL
• 5 thing you probably didn’t know about PL/SQL
• All about metadata: why telling the database about your schema matters
• What is New and Improved and Coming in Oracle Application Development
• All about Oracle Database Security.

All of this will followed by a 1 hour Ask Tom session, where you will have your chance to ask the man himself anything about the Oracle database.

This is a FREE event. What more could you ask for!

Check out the OUG website for more details and how to register for this event.

http://www.ukoug.org/events/tom-kyte-seminar-and-asktom-live/

Don’t forget to use the OUG Ireland twitter tag  #oug_ire

Oracle Magazine–Summer 1994

The headline articles in the Summer 1994 edition of Oracle Magazine included Objects in Motion on how to reap the benefits of OO technology, building a business case for Objects, working with Objects and some basics of OO programming.

As part of the articles on Objects, there was an interview with Steve Jobs who was an Object Evangelist at NeXT.

Apart from the articles on Objects we also had articles on:

• System Level Roles in Oracle 7: Going beyond Connect, Resource and DBA. In Oracle 7 the DBA can not create role groups.
• Customising Oracle Reports with PL/SQL: This article explores the different triggers that now exist in Oracle Report, where we can now use PL/SQL to perform pre-processing of data before it is displayed in the report output. The triggers included Before Runtime Parameter Form, Parameter Validation, After Runtime Parameter Form, Before Report (executes), Object level triggers, Between Each Page and After Report
• How Nintendo is using Oracle 7 client/server to stay ahead with providing faster and easier access to sales and marketing information.

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.

ODM–Attribute Importance using PL/SQL API

In a previous blog post I explained what attribute importance is and how it can be used in the Oracle Data Miner tool (click here to see blog post).

In this post I want to show you how to perform the same task using the ODM PL/SQL API.

The ODM tool makes extensive use of the Automatic Data Preparation (ADP) function. ADP performs some data transformations such as binning, normalization and outlier treatment of the data based on the requirements of each of the data mining algorithms. In addition to these transformations we can specify our own transformations.  We do this by creating a setting tables which will contain the settings and transformations we can the data mining algorithm to perform on the data.

ADP is automatically turned on when using the ODM tool in SQL Developer. This is not the case when using the ODM PL/SQL API. So before we can run the Attribute Importance function we need to turn on ADP.

Step 1 – Create the setting table

CREATE TABLE Att_Import_Mode_Settings (
  setting_name  VARCHAR2(30),
  setting_value VARCHAR2(30));

Step 2 – Turn on Automatic Data Preparation

BEGIN
   INSERT INTO Att_Import_Mode_Settings (setting_name, setting_value)
   VALUES (dbms_data_mining.prep_auto,dbms_data_mining.prep_auto_on);
  COMMIT;
END;

^{Step 3 – Run Attribute Importance}

BEGIN
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name => 'Attribute_Importance_Test',
    mining_function  => DBMS_DATA_MINING.ATTRIBUTE_IMPORTANCE,
    data_table_name  > 'mining_data_build_v',
    case_id_column_name => 'cust_id',
    target_column_name  => 'affinity_card',
    settings_table_name => 'Att_Import_Mode_Settings');
END;

Step 4 – Select Attribute Importance results

SELECT *
FROM TABLE(DBMS_DATA_MINING.GET_MODEL_DETAILS_AI('Attribute_Importance_Test'))
ORDER BY RANK;

ATTRIBUTE_NAME       IMPORTANCE_VALUE       RANK
-------------------- ---------------- ----------
HOUSEHOLD_SIZE             .158945397          1
CUST_MARITAL_STATUS        .158165841          2
YRS_RESIDENCE              .094052102          3
EDUCATION                  .086260794          4
AGE                        .084903512          5
OCCUPATION                 .075209339          6
Y_BOX_GAMES                .063039952          7
HOME_THEATER_PACKAGE       .056458722          8
CUST_GENDER                .035264741          9
BOOKKEEPING_APPLICAT       .019204751         10
ION

CUST_INCOME_LEVEL                   0         11
BULK_PACK_DISKETTES                 0         11
OS_DOC_SET_KANJI                    0         11
PRINTER_SUPPLIES                    0         11
COUNTRY_NAME                        0         11
FLAT_PANEL_MONITOR                  0         11

Update on Exalytics Pricing

In my previous blog post (Exalytics : How much will it cost me ?) I gave an outline of the pricing you might expect for an Exalytics machine.

The final pricing that I gave of approx $3+M was based on the per processor licencing.

Yesterday (24th Jan) the Oracle Business Intelligence blog by Manan, included the pricing based on the per user licences.

The following is a breakdown of the Exalytics pricing based on the minimum 100 user licencing.

Licence Costs (100 users)

Exalytics machine = $135,000

TimesTen = $300 x 100 users = $30,000

BI Foundation Suite = $3,675 x 100 users = $367,500

Giving a grand total of $532,500.

Support Costs (100 users)

But we need to add the annual support costs to this.

Exalytics machine support = $29,700.

TimesTen support = $66 x 100 users = $6,600

BI Foundations suite = $809 x 100 users = $80,900

Total support costs (100 users) = $116,500

First year & on-going costs costs

Total first year cost for an Exalytics machine = $532,500 + $117,200 = $649,700

Plus on going annual support costs of $117,200 in year 2 and subsequent years.

Discounted Costs

If you are one of the lucky customer who can If I use the same discounts, as I did in my previous blog post, of 25% discount on hardware and 60% discount on the software, we get:

Year 1 cost of : ($135,000*0.75) + ($397,500*0.40) = $260,250

So it might be possible to get an Exalytics machine for $260+K, plus annual support costs.

OUG Ireland 2012 Conference & Re-launching the SIGs in Ireland

The schedule of presentations for the 2012 Oracle User Group Ireland conference has just been released.

This year the conference will be in the Dublin Convention Centre.

There are 5 parallel streams with sessions running all day. Key notes presentations will be given by Eileen O’Mara from Oracle Ireland (given in the morning) and the key note at the end of the day will be given by Mogen Norgaard from Miracle AS in Denmark and founder of the OakTable Network.

There will be 7 Oracle ACE Directors and 3 Oracle ACEs presenting at the conference in addition to other speakers from Ireland, UK and Netherlands. Here is the full list of Oracle ACEs that are presenting

Debra Lilley, ACE Director
Mark Rittman, ACE Director
Roel Hartman, ACE Director
Simon Haslam, ACE Director
Frits Hoogland, ACE Director
Mogens Norgaard, ACE Director
Lonneke Dikmans, ACE Director
Brendan Tierney, ACE
Marcin Przepiorowski, ACE
Grant Ronald, ACE

It is also FREE to attend. So go ahead an book yourself a place for a full day Oracle training.

To register your place – Click here

The agenda for the conference can be found here – Click here

There are a few important web and social media links that might be of interest for all Oracle people in Ireland.

Web :       www.oug.og/ireland
Twitter :   @oug_ire
                @oug_ire2012 For the 2012 conference related tweets
LinkedIn : http://www.linkedin.com/groups?home=&gid=3441701

Re-launching the SIGS in Ireland

Over the past few years the User Group SIGs have been a bit quiet here in Ireland. Except for the BI (&EPM) SIG, which has been getting a good attendance since it launched back in 2009.

In particular the Oracle Technology SIG basically ceased to exist over the past 5 or 6 years, since the days when John Knox and Tagdh Cashman were running it.

At the conference it is hoped that to get some people to volunteer to get this SIG back up and running again. Maybe we could get 3 SIG events each year.  What do you think ?

Drop me an email if you have any questions regarding the SIGs in Ireland (I’m deputy chair of the BI & EPM SIG).Hopefully

I’ll see you there on the 21st March.

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.

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

What version of the Oracle Database do I need ?

Oracle has recently made available a very useful webpage that that lists the functionality available for each version of the 11g Database. So before you decide which version of the database to purchase, check out this webpage.

http://www.oracle.com/us/products/database/product-editions-066501.html

Oracle Big Data & Analytics Sessions at UKOUG Conference

There are a number of BIG Data and Analytics presentations at the UKOUG Conference in Birmingham (4th Dec – 7th Dec).

I’ve worked my way through the agenda grids for each day of the conference and I’ve come up with the following list.  If you are interested in BIG Data and Analytics these presentations are a must see

Monday	12:15-13:15	Exadata Live – Graham Wood – Hall 7A
5th Dec
Tuesday	9:00-10:00	Big Data-Are you ready – Mark Townsend – Hall 1
6th Dec	10:10-10:55	Who’s afraid of Analytic Functions – Alex Nuijten – Hall 5
	11:15-12:15	Analysing Your Data with Analytic Functions – Carl Dudley – Hall 9
	16:40-17:40	Mobile Analytics using OBIEE 11g – Jon Mead – Exec Room 1
Wednesday	9:00-10:00	Oracle 11g Database Automatic Parallelism – Joel Goodman – Hall 9
7th Dec	15:20-16:05	How to Deploy your Oracle Data Miner in a Live Environment - me

Oracle Ireland: Data Centre Transformation Event 7th December

Oracle in Ireland is hosting a session called Data Centre Transformation on 7th December (9:30-13:00), in the Guinness Storehouse, St James Gate, Dublin 8.

The agenda for this session is

9:00	Registration & Coffee
10:00	The 21st Century Data Centre, Delivered by Oracle Solaris – Mike Ramchand
10:30	Oracle Enterprise Manager 12c – John Caulfield, Solutions Director
11:00	Oracle Virtualised Systems (VM 3.0) – Dave Patterson, Oracle Hardware
11:30	Coffee Break
12:00	Transformative Oracle Storage Solutions – Neil Caughey, Oracle Storage Business Unit
12:30	Extreme Performance with Oracle Exadata and Exalogic – Brian Grant, Oracle Exalogic Business Development Manager

To book your place on this event email oracle.events@ketchumpleon.com

Or register by following this web link.

I wont be at this event as I’ll be presenting in the afternoon at the UKOUG conference in Birmingham.

How Many Sleeps to Santa

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

How Many Sleep to Santa
-----------------------
                     34

Call for Presentations : OUG Ireland Conference 2012

The call for presentations for the annual Oracle User Group Ireland conference has been posted in last few days.
The conference is planned for March 2012 and the venue will be picked over the next few weeks.
I’m on organising committee this year. It is hoped to have a number of parallel streams covering core Database Technology, BI (&EPM), Development (including Fusion).
If you are interested in presenting a short presentation of approx. 45 minutes (including time for questions), then you will need to submit your Topic and Abstract using the following link :  www.oug.org/Irelandpapers
The conference is not limited to presenters from Ireland and it is hoped to get a number of well known Oracle experts and Oracle ACEs to come to Dublin for the day.
What kind of topics are of interest. Well pretty much anything Oracle. We have all come across something interesting in our jobs that we could share, be it using a particular technique, new features, sharing experiences, best practices, product demos, etc
I’ve already submitted a presentation on Oracle Data Miner.
There is a Twitter hash tag for the Oracle Conference #oug_ire2012.  So add this to your Twitter tool to follow developments and announcements about the conference.
If you have any question about the conference drop me a email.

Tom Kyte Seminar Day–Dublin

On Wednesday 2nd December, I attended a full day of presentations given by Tom Kyte of Oracle (asktom.oracle.com). Tom covered a number of topics and these included some of his Oracle Open World presentations.

The topics that were covered included

• 5 things about SQL (OOW11)
• Database Option Packs
• 5 things about PL/SQL (OOW11)
• Q&A Ask Tom Session

All of these presentations can be downloaded from Tom’s website www.asktom.com.

Tom wont be presenting at the annual UKOUG conference in December, but he is hoping to be there next year (2012).

ODM PL/SQL API 11.2 New Features

The PL/SQL API interface for Oracle Data Miner has had a number of new features. These are listed below along with the new API features added with the 11.1 release.

• Support for Native Transactional Data with Association Rules: you can build association rule models without first transforming the transactional data.
• SVM class weights specified with CLAS_WEIGHTS_TABLE_NAME: including the GLM class weights
• FORCE argument to DROP_MODEL: you can now force a drop model operation even if a serious system error has interrupted the model build process
• GET_MODEL_DETAILS_SVM has a new REVERSE_COEF parameter: you can obtain the transformed attribute coefficients used internally by an SVM model by setting the new REVERSE_COEF parameter to 1

11.1g API New Features

• Mining Model schema objects: previous releases, DM models were implemented as a collection of tables and metadata within the DMSYS schema. in 11.1 models are implemented as data dictionary objects in the SYS schema. A new set of DD views present DM models and their properties
• Automatic and Embedded Data Preparation: previously data preparation was the responsibility of the user. Now it can be automated
• Scoping of Nested Data: supports nested data types for both categorical and numerical data. Most algorithms require multi-record case data to the presented as columns of nested rows, each containing an attribute name/value pair. ODM processes each nested row as a separate attribute.
• Standardised Handling of Sparse Data & Missing Values: standardised across all algorithms.
• Generalised Linear Models: has a new algorithm and supports classification (logistic regression) and regression (linear regression)
• New SQL Data Mining Function: PREDICTION_BOUNDS has been introduced for Generalised Linear Models. This returns the confidence bounds on predicted values (regression models) or predicted probabilities (classification)
• Enhanced Support for Cost-Sensitive Decision Making: can be added or removed using DATA_MINING.ADD_COST_MATRIX and DBMS_DATA_MINING_REMOVE_COST_MATRIX.