Learning is not a spectator sport

Complete listing of posts

  • 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

  • Raw partitions?

    Raw partitions?

    Here’s a quirky one for you. It can happen when you are dealing with a partitioned table where the partition key is defined as RAW. To be honest, I really can’t think of a reason why you ever want a table with a raw partition key. (If you have one, please let me know in Read more

  • Take care with automatic indexes

    Take care with automatic indexes

    This one came in via an AskTOM question. You need to be careful when disabling constraints that are underpinned by an index, especially when it comes to the storage for that index. Let’s take a look at a simple example. I’ll explicitly nominate the LARGETS tablespace for my index that will support the primary key. Read more

  • Capture all DDL run on a database instance

    Capture all DDL run on a database instance

    I posted a video a couple of days ago showing a trigger mechanism to customize the capture of DDL that is issued on your database. The aim here is to be more generous with letting developers execute DDL on their Development databases, whilst still having a thorough record of the changes that are occurring. Of Read more

  • PL/SQL – Don’t mix and match scope

    PL/SQL – Don’t mix and match scope

    Here’s a simple little PL/SQL block where we call an inner procedure PARAMETER_TESTER from its parent block. Pay particular attention to the parameter we pass to the procedure, and it’s value throughout the execution of that procedure. SQL> set serverout on SQL> declare 2 3 glob_var int := 0; 4 local_var int; 5 6 procedure Read more

  • Quick and easy masking

    Quick and easy masking

    I had a request from a client a while back regarding masking of data. They had an application with sensitive data in the Production environment (where access and audit were very tightly controlled) but the issue was how to respect that sensitivity in non-Production environments whilst still preserving full size data sizes for application testing. Read more

  • APEX Upgrade redux

    APEX Upgrade redux

    I posted about my APEX upgrade to 19 yesterday, and someone was quick to point out to me that they believed I hadn’t covered all of the steps. “What if your APEX instance needs to call web services?” they said. “You need to update your Access Control Lists.” I hadn’t thought of that, so I Read more

  • Application Express 19.1

    Application Express 19.1

    AskTOM moved to Application Express 19.1 without any major issues last weekend. That in itself is a nice endorsement for APEX, given that the AskTOM application dates back nearly 20 years to 2001, and predates even the existence of APEX. The only fix that we had to make was that AskTOM uses the static CDN Read more