Learning is not a spectator sport

Complete listing of posts

  • MERGE and ORA-8006

    MERGE and ORA-8006

    I’m sure there will be a slew of post-Kscope wrap up posts coming out into the blogosphere, so in lieu of that, and the the fact that I’m just stuck in an airport waiting for a flight, I’ll offer something slightly more technical. I did a post a while back about a curious error “unable… Read more

  • SMON_SCN_TIME and ORA-8161? Digging deeper

    SMON_SCN_TIME and ORA-8161? Digging deeper

    In the recent versions of the Oracle database, we’ve had the ability to convert between a System Change Number (SCN) and the approximate time to which that SCN pertains. These functions are unsurprisingly called SCN_TO_TIMESTAMP and TIMESTAMP_TO_SCN. The only potential misnomer here is that even though the functions are called “timestamp” and return a datatype… Read more

  • Kscope Sunday will be awesome

    Kscope Sunday will be awesome

    Yeah yeah I know. What kind of dufus cut-pastes a giant image into their blog post. That would be ….. me But the reality is, there is just soooooo much going on this year on the Sunday before the “official” start of the Kscope conference, it was too hard to condense it into a few… Read more

  • With and without WITH_PLSQL within a WITH SQL statement

    With and without WITH_PLSQL within a WITH SQL statement

    OK, let’s be honest right up front. The motivation for this post is solely to be able to roll out a tongue twisting blog post title . But hopefully there’s some value as well in here for you if you’re hitting the error: ORA-32034: unsupported use of WITH clause First some background. A cool little… Read more

  • Advice on fragmentation and shrinkage

    Advice on fragmentation and shrinkage

    If you have performed some sort of data cleanup or similar on a table, then the deleted space will be reused by future insertions. But if that cleanup was the last task you were performing on that table, ie, you were not expecting a lot of new data to ever come in again, or you… Read more

  • Generic data models … generic applications … ugh

    Generic data models … generic applications … ugh

    There’s a hesitation to publish this example, because publishing it may be interpreted as an endorsement of this approach and it certainly isn’t. Over the years there have been plenty of articles describing the long term pain that typically comes from generic data models. Here’s a few to whet your appetite. https://rodgersnotes.wordpress.com/2010/09/21/muck-massively-unified-code-key-generic-three-table-data-model/ https://www.red-gate.com/simple-talk/opinion/opinion-pieces/bad-carma/ https://asktom.oracle.com/pls/asktom/asktom.search?tag=query-on-design But… Read more

  • DBMS_JOB – the joy of transactions

    DBMS_JOB – the joy of transactions

    This is a followup to yesterdays post on DBMS_JOB and is critical if you’re upgrading to 19c soon. Mike Dietrich wrote a nice piece last week on the “under the covers” migration of the now deprecated DBMS_JOB package to the new Scheduler architecture. You should check it out before reading on here. Mike’s post concerned… Read more

  • DBMS_JOB – watching for failures

    DBMS_JOB – watching for failures

    I had a friend point this one out to me recently. They use DBMS_JOB to allow some “fire and forget” style functionality for user, and in their case, the jobs are “best efforts” in that if they fail, it is not a big deal. So whilst this may sound counter-intuitive, but if you rely on… Read more

  • Better optimizer plans for adhoc search

    Better optimizer plans for adhoc search

    In a perfect world, the optimizer would reach out from the server room, say to us: “Hey, lets grab a coffee and have a chat about that query of yours”. Because ultimately, that is the task we are bestowing on the optimizer – to know what our intent was in terms of running a query… Read more

  • Generating INSERT scripts that run fast!

    Generating INSERT scripts that run fast!

    Most developers tools out there have some mechanism to unload a table into a flat file, either as CSV, or Excel, and some even allow you to unload the data as INSERT statements. The latter is pretty cool because it’s a nice way of having a self-contained file that does not need Excel or DataPump… Read more

  • Nostalgia and choosing your in-flight movie

    Nostalgia and choosing your in-flight movie

    First thing to note on this post. No tech content in this one. Just some nostalgia. Couple of days ago, I was flying from Perth to Dubai on my way to APEX Connect in Bonn. Because this is an 11hour hell in a death tube flight I settled in to my standard sleepless task of… Read more

  • Partition loading in direct mode

    Partition loading in direct mode

    Direct mode insert using the APPEND hint is a cool piece of technology that lets you load bulk data into a table very quickly and efficiently. Obviously there are a number of implications of doing so which you can read about here, but the one that catches most people out is the that you are… Read more