Oracle Scratchpad

June 18, 2013


Filed under: Delphix — Jonathan Lewis @ 11:23 am BST Jun 18,2013

If you’ve been keeping an eye on my Public Appearances page you’ll know that I am scheduled to go on line with Kyle Hailey for a second (more technical) discussion about Delphix and virtual databases on 19th June (tomorrow). If you haven’t registered, there’s still time to do so. It’s scheduled for 5:00 pm (BST), which makes it 9:00 am in San Francisco.

Update 1: Delphix have got 10 copies of Oracle Core to give away and they’ve decide to give one to every 10th registrant (until stocks run out) for the webinar.

Update 2: Over the last few days Kyle Hailey has been writing a short series comparing the commonest technologies currently available for Virtual Databases (or “Thin cloning”):

April 4, 2013

Delphix Overview

Filed under: Delphix — Jonathan Lewis @ 9:04 pm BST Apr 4,2013

Update: Here’s the link to the recording of the webinar

I’ll be online tomorrow morning (Friday 5th, 9:00 Pacific time, 5:00 pm UK) in a webinar with Kyle Hailey to talk about my first impressions of Delphix, so I thought I’d write up a few notes beforehand.

March 22, 2013

Delphix Debrief

Filed under: Delphix — Jonathan Lewis @ 7:24 am BST Mar 22,2013

I’ve had my week in Palo Alto with the Delphix people. I really don’t know where the time went to – but I had a lot of interesting conversations with a number of very able people; plenty of time to experiment; and I’ve even been able to install Delphix on my laptop (as a 64-bit Open Solaris 10 VM under VMWare). I liked the product, and I was impressed with the team they’ve got working on it.

We’ve pencilled in an intial online webinar for Friday 5th April which will probably feature an informal chat where Kyle Hailey and I talk about my impressions of the product and what I’ve done with it so far. Then, in about a month’s time, we’ll have a more technical discussion.

In the meantime, it occurred to me that my next blog post ought to be about LOBs as this would be a nice way to introduce you to one of the key ideas behind DxFS (the Delphix-extended file system that’s based on ZFS).

Update 22nd March

The link to register for the online conversation is now up. We’re timetabled for 5th April 9:00 am PDT (which is currently 4:00 pm GMT, but it will be 5:00 pm BST by then.)

February 6, 2013


Filed under: Delphix — Jonathan Lewis @ 7:19 pm BST Feb 6,2013

If you’re a regular follower or my blog you may recall Kyle Hailey and the joint webinar we did nearly two years ago on “Visual SQL Tuning” covering an approach I’ve written about in the past and a product that he developed at Embarcadero to automate the work that I’d been doing by hand and eye.

Kyle has now moved on to Delphix, and has become involved with another really interesting piece of technology – database virtualization. How do you supply a terabyte sized database to five different development teams without using up 5TB of disc space ? Create an operating environment that keeps one master copy of the database while maintaining a set of (small) private files for each team that hold private copies of the blocks that have been changed by that team – and that’s just one feature of the product.

The product is sufficiently interesting (plus I have a healthy regard for Kyle’s opinions) that I’ve accepted an invitation to go over to California for a few days next month to experiment with it, see what it can do, try to stress it a bit and so on. The people at Delphix are so confident that I’ll be impressed that they’re going to let me do this and then write up a blog telling you how things went.

Have a browse around their documentation and if you’re interested add a suggestion to the comment telling me what you’d like me to test, and how, and I’ll see if I can fit it into my timetable (no promises – but if you come up with interesting ideas I’ll see what I can do).

March 10, 2014

Duplicate database

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 6:19 pm BST Mar 10,2014

One of the people attending my seminar in Munich last week has emailed me some details about a nasty little surprise you may get if you’re thinking about TSPITR (tablespace point in time recovery), and happen to have a few materialized views in your database.

You might have wanted to use the “duplicate target database”  from rman with the “skip tablespace” option to create a duplicate database, excluding tablespaces that you’re not interested in, if you needed to recover a particular tablespace (or set of tablespaces) to a specific point in time.  (Once you’ve done that you can extract the data that you want and then discard the duplicate database). But there’s a requirement for recovery is that the set of tablespaces should be “self-contained”; but what does “self-contained” mean ?

Amongst other things it means that none of the tablespace you skip should contain materialized views. Note carefully, that’s not saying the schema you’re interested in shouldn’t have created any materialized view, or the tablespace you want to duplicate contains a base table for a materialized view in another table; it really does mean – if you’ve got ANY materialized view ANYWHERE in the database, you have to duplicate those tablespaces as part of the process.

Here’s the restriction note from MoS (note the exclamation mark – maybe the Oracle analyst was surprised too):

Restriction Note: 
You MUST NOT exclude 
- SYS-owned objects 
- or tablespaces with rollback segments, 
- nor tablespaces containing “MATERIALIZED VIEWS”! 

Implementation suggestion – always put materialized views (and materialized view logs, and indexes on materialized views) in their own tablespace(s), just in case one day you want to do a tablespace point in time recovery and find you’ve got a few bits of materialized views scattered all around your database.


When I first heard this comment I didn’t believe it (but take a look at MoS document ID: 1287276.1 if you’re finding it hard to believe). Naturally my engine of doom went into overdrive immediately after I was convinced and made me wonder what would happen in a 12c container database with several plugged databases; obviously the existence of a materialized view in one plugged database shouldn’t have any impact on TSPITR for another pluggable database – but I wouldn’t mind if someone tested the hypothesis and reported back what they found.


It’s just occurred to me that this type of TSPITR problem simply won’t exist if you’re using Delphix as part of your working environment.

March 9, 2013

Virtual DB

Filed under: Delphix — Jonathan Lewis @ 9:52 am BST Mar 9,2013

I’m heading off to Heathrow airport later on today to fly out to San Francisco for my week of  experimenting with Delphix. I’ve done a little preparation work, of course, including browsing around the Internet to read about related technologies. Some of the material I found was very interesting, so I thought I’d go publish a few of the links that might be useful to other people.

March 26, 2011

Public Appearances

Filed under: — Jonathan Lewis @ 12:43 pm BST Mar 26,2011
27th Sept – 2nd Oct San Francisco: I have been invited to do a 45 minute presentation at OpenWorld with the title Reading an AWR Report – the session ID CON5915, Wednesday 1st Oct, 16:45 – 17:30, Moscone South, Room 102 (seats ca. 635). I’ll also be speaking at the first of the two EOUC “Short Talks” sessions (12 Looks at Oracle Database 12c: EOUC Short Talks [session ID UGF8949] Sunday 28th Sept 14:30 – 15:15, Moscone South, Room 304) where I’ll be the opening speaker with 5 minutes on Upgrading to 12c – what will it break. For “off-piste” action I’ll be speaking about Calculating Selectivity at Oak Table World and I’ve also told the people from Delphix that I’ll be happy to make a brief appearance on their stand each day to say a few words about why I like their product.
14th – 15th Oct Munich (Germany): I’ll be running a two-day course covering “Writing Optimal SQL” and “Trouble-shooting and Tuning” under the University “Celebrity Seminars” banner. Further details at this URL
20th – 23rd Oct Nordic Tour: I’ll be joining the OTN Nordic Tour – which is going to be a whirlwind visit to Stockholm (Sweden), Oslo (Norway), Copenhagen (Denmark), and Helsinki (Finland) in that order. I’ve got two topics on the agenda: Understanding and Optimising Parallel Queries, and Reading an AWR Report. Further details at this URL.
5th – 6th Nov Istanbul, Turkey: I’ll be running the two-day seminar “Optimising Oracle – Foundations” for Oracle University as part of their “Celebrity Seminar” series. Further details at this URL.
18th – 20th Nov Nuremberg, Germany: DOAG annual conference 2014 – I’ve had two presentations accepted: “Basic Selectivity” and “Join Selectivity”.
7th – 10th Dec Liverpool: The UKOUG Tech Conference is in Liverpool this year; I’ve got three sessions (more like one short and one long, though) in the main conference and one on “Super Sunday”.Titles and (provisional) times are:

  • Calculating Selectivity:  Sunday 13:30 – 14:20
  • Five hints for Efficient SQL:  Tuesday 17:30 – 18:20
  • Fundamentals of Troubleshooting (without graphics) pt.1 Wednesday 09:00 – 09:50
  • Fundamentals of Troubleshooting (without graphics) pt.2 Wednesday 10:00 – 10:50

My presentation on “Understanding and Optimising parallel queries” is on the reserve list – so there’s always a chance that that will get in at the last minute.

15th Dec Munich (Germany): I’ll be running the one day course “Beating the Optimizer” under the Oracle University “Celebrity Seminars” banner. Further details at this URL

The Rubric Theme. Blog at


Get every new post delivered to your Inbox.

Join 4,013 other followers