Transparent Data Encryption (TDE) in 12c and RTFM Carefully


I keep thinking I’m moving forward with this Oracle database 12c stuff, but around every corner there is another surprise. A few days ago I was setting up a demo for Transparent Data Encryption (TDE) in 12c using my existing articles (10g, 11g). That’s when I noticed things had changed, so I had to use an 11g instance for the demo and make a note to revisit TDE for 12c…

On revisiting the subject, I saw that the encryption key management has changed in 12c. What’s more, if you are using the multitenant option it is a bit different again. That resulted in this article.

While I was working through this I was getting some freaky results, which were driving me mad. Whilst trying to figure out that I noticed I had two PDBs of the same name under a single listener. I had created two test instances (cdb1 and cdb2), each with a PDB called “pdb1”. There is a sentence in the docs to say this is not a good idea, which resulted in this little article.

So it turns out that TDE works fine, provided you are not an idiot. :)

The moral of the story is RTFM carefully, because sometimes a single sentence can make all the difference!



Statistics Collection Enhancements on Oracle Database 12c


I’ve been having a play around with the enhancements to the statistics collection in 12c. I’ve put together this top-level post with links to all my other articles on this subject.

Here are the new articles it links to.

It also links to some of the stuff I put out previously for the Adaptive Query Optimization functionality, as that is statistics related.

In a totally unrelated incident, I wrote this thing about the new READ object privilege, but forgot to mention it on the blog.

The journey continues…



Flashback Data Archive Enhancements in Oracle Database 12c


Having played around with Flashback Data Archive in 11g, I figured I would get through the 12c enhancements pretty quickly. I didn’t account for the fact I’m a donkey and can’t see the wood for the trees. Luckily, I know some people who aren’t stupid and they gave me a nudge in the right direction, allowing me to spot my silly mistake. Thanks Bjoern and Connor:)

So after lots of wasted time, here is the article.

For the most part, it’s an evolution, but the new bits are pretty darn cool. I guess a lot of people will focus on two main things:

  • It is now a free feature, provided you don’t use compression, available in all versions. The change to use no compression by default has been back-ported to, so it’s free there too, which is nice!
  • The contents of contexts can now be stored in the flashback archive, so you can have access to the USERENV and custom context values that were set in the session when the DML was processed. This makes it possible to replace all those crappy old audit triggers with FDA!

There’s some other stuff in there that’s kind-of nice too. I think it’s worth checking it out, especially at its new price. :)




Adaptive Query Optimization in Oracle Database 12c


Over the holiday period I finally decided to tackle the Adaptive Query Optimization stuff in Oracle 12c, which resulted in these articles.

Adaptive Query Optimization is really a collection of different features, hence the links page to bring them all together. Some of the features are just renamed and slightly modified versions of stuff from previous releases. Other bits are totally new. I’ve sat through a number of talks about this stuff over the last few years and the sound-bites make it seem quite obvious and straight forward. That’s because the individual pieces, for the most part, are quite straight forward. The trouble comes when they all start to interact, at which point things can get quite confusing. While I was doing the demos for the features, I had to chop and change quite a bit. Sometimes things didn’t happen when I thought they should. Other times things did happen when I thought they shouldn’t. I think much of it was down to my misunderstanding, but that just goes to show how tricky some of this can be… That’s without adaptive cursor sharing and SQL plan management thrown into the mix…

As I keep saying, these articles will probably get revised over time as I get a better understanding of this stuff. I have a feeling I won’t really get to grips with it until we are live on 12c for a number of projects and people start throwing real situations at me to investigate.




Fedora 21 : Oracle 11g and 12c Installations


Fedora 21 has arrived and it’s now delivered in three focussed flavours (Workstation, Server and Cloud). This of course resulted in the usual articles from me.

As always, read the warnings before you start down this path.

From an Oracle installation perspective, it’s almost identical to Fedora 20. I chose to use the server flavour and install the “MATE Desktop” package group. I suspect others may prefer to start with the workstation release. Either way it should be fine.

As I suspected, switching my main desktop from Fedora to the MacBook means I care significantly less about this release than before, but I still have some upgrades I’ll need to plug through.



Oracle Database 12c : EXPAND_SQL_TEXT, APPROX_COUNT_DISTINCT, Session Sequences and Temporary Undo


While I was away on the OTN APAC tour, I wasn’t really able to sink my teeth into anything, so inspired by Connor McDonald‘s session in Perth, I decided to write up a few bits and pieces in my free moments in the hotels and planes (when I had enough elbow room to type).

Here is what I’ve neatened up so far.

I’ve got a couple of other things that are part done, which will no doubt be coming out over this week…



Pattern Matching (MATCH_RECOGNIZE) in Oracle Database 12c


I’ve spent the last couple of evenings playing with the new SQL pattern matching feature in Oracle 12c.

I’m doing some sessions on analytic functions in some upcoming conferences and I thought I should look at this stuff. I’m not really going to include much, if anything, about it as my sessions are focussed on beginners and I don’t really want to scare people off. The idea is to ease people in gently, then let them scare themselves once they are hooked on analytics. :) I’m thinking about Hooked on Monkey Fonics now…

At first glance the pattern matching seems pretty scary. There are a lot of options and as soon as you throw regular expressions into the mix it does make your head swim a little. After a couple of half-baked attempts, where I found convenient excuses to give in when the going got tough, I finally sat down and plugged through the docs. If you actually RTFM it is a lot easier than hoping to wing it. Who’da thunk it? :)

I’ve tried to keep the article really light. The docs are pretty good for this stuff (if you read them) and they have a lot of examples. I started adding more and more detail to the article, then chopped most of it out. There is no point regurgitating all the options when it is in the docs. Most of the examples I’ve seen before just talk about basic patterns, like V and W shapes, but it’s quite simple to do complicated stuff once you start playing. In fact it takes more time to set up the example data than it does to figure out the queries to bring it back.

In the near future I will be copy/pasting examples and adjusting them or just sitting with my article and the docs when trying to use this stuff. I think it’s going to take a long time before I can type this stuff from memory. Partly that’s because I can’t see myself having lots of cause to use it. I can’t think of any scenarios I’ve experienced where this would have been a natural fit. Having said that, I’ve never worked in things like stock markets, betting and stuff like that where I can imagine this sort of pattern matching is needed all the time. I seem to remember one person at a conference, who shall remain nameless, saying this feature was one of their drivers for upgrading to 12c. I wonder if that was for real or an exaggeration?

Anyway, if you need this sort of analysis, I think it’s worth checking out, but try to remember it’s not as scary as it first looks. :)



JSON Support in Oracle Database 12c (


I spent a bit of time at OpenWorld looking at the JSON support in Oracle Database 12c. I started to write some stuff about it on the plane home and I spent the last two mornings finishing it off. You can see the results here.

I’ve tried to keep it light, since the documentation does a pretty good job at explaining all the variations of the syntax. I’ve also avoided trying to teach people about JSON itself. There is loads of stuff about that on the net already.

For the most part I think the JSON support looks pretty cool. During the process of writing the articles I did notice a few of things that I thought might confuse.

  • Using dot notation to access JSON in SQL seems like a neat solution, but each reference results in a query transformation that may well leave you with a whole bunch of function calls littered around your SQL. The end result is probably not what you want. I think it is probably better to avoid it and write all the direct function calls yourself, so you know exactly what the optimizer will do.
  • Typically the query transformations of dot notation result in a JSON_QUERY function call, but the optimizer can substitute a JSON_VALUE call if there is an index that it can take advantage of. That can be a little confusing when you aren’t expecting it. Once again, it might be better to avoid dot notation so as not to confuse.
  • If you are careful, the indexing of JSON data is pretty straight forward, but if you aren’t aware of how the query transformations work or you forget how very small changes in function parameters affect index usage, you can chase your tail trying to figure out why you aren’t able to use your indexes.

Until the REST APIs are released, the only way you can use this stuff is from the server side, so it’s not really something you can hand out to developers who are expecting to use just another document store. I had a play with the REST stuff during a hands-on lab at OpenWorld and it looked kind-of cool. When it’s released I’ll write an article about it and run it by some of the folks at work to see how they think it compares to other document databases…



PL/SQL New Features in Oracle Database 12c


I recently put some more PL/SQL new features articles live.

I’ve also posted a top-level new features article.

This contains a number smaller features as well as links to other articles on the site that discuss some of the new features in greater depth.

I’ve got a couple of PL/SQL books I’ve got to read and review, but I’ve been holding back because I wanted to get my take on this subject written before I was influenced by others. I guess I don’t have that excuse any more. :)



Temporal Validity, ACLs, External Tables, SQL*Loader and more in Oracle 12c


Some more 12c articles have trickled out over the last few days.

I kind-of mentioned this next thing in a post a few weeks ago, but didn’t name names. :) While writing an article about the PDB logging clause in I noticed it didn’t work. I raised an SR with Oracle Support and they confirmed it was a bug. I was not planning to release the article until the bug was patched, but it came up in conversation recently and I decided it was better to release the article with a big fat warning on the top saying it doesn’t work, just so others are not as confused by this as I was. I’m still not sure it is the right thing to do, but what the heck…

When the bug is patched, I will revise the article and probably promote it to the front page of the website as a “new article”. For now it is lurking in the depths of my website. :)

The 12c journey continues…