Learning is not a spectator sport

Complete listing of posts

  • Take a COPY out of SQLcl’s book

    Take a COPY out of SQLcl’s book

    As the world continues to get smaller, it makes sense for all databases to be defined to handle more than just US7ASCII, which is why the default characterset for all recent versions of the Oracle database is AL32UTF8. In that way, we can handle the various challenges when it comes to languages other than english. Read more

  • Statistics on Load

    Statistics on Load

    One of the talks I gave recently on the Groundbreaker Yatra tour was on the changes that arrive with the optimizer when you move to 12c and above. We talked about changes to histograms, all of the adaptive features etc, but also, one of the simplest but coolest optimizer improvements when it comes to loading Read more

  • The APEX interface to the OOW catalog is back

    The APEX interface to the OOW catalog is back

    Yes indeed! Now that the dates and times are available for OpenWorld 2019, then it is naturally time for the best data searching, filtering and analysis tool on the planet to step up to the plate, enter the fray and …. hmmm… I’ve run out of metaphors 🙂 But of course, if you have data Read more

  • HIGH_VALUE (and other LONG columns) revisited

    HIGH_VALUE (and other LONG columns) revisited

    Just a quick post to ensure people don’t get caught out by a recent “trick” I saw on an AskTOM question for extracting the HIGH_VALUE out of it’s LONG storage in the dictionary to a more appropriate datatype. A reviewer (I’m sure in good faith) posted the following mechanism to extract the HIGH_VALUE SQL> create Read more

  • Same dog, different leash – functions in SQL

    Same dog, different leash – functions in SQL

    Let’s start with this somewhat odd looking result. I have an inline function that returns a random number between 0 and 20, and I call that for each row in ALL_OBJECTS and then I extract just those rows for which the generated random number is 10. Seems simple enough….but why do I get results for Read more

  • 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