Learning is not a spectator sport

Complete listing of posts

  • Math and SQL: Two of my favourite things

    Math and SQL: Two of my favourite things

    There has always been a special place in heart for the Fibonacci sequence. Decades ago in high school when comparing the ratio of successive items, as any naive student would, I thought I had stumbled upon some wonderful discovery that would assure my place in the pantheon of mathematical greats. Of course, it was somewhat… Read more

  • APEX 20.2 comes to town (well, to my PC)

    APEX 20.2 comes to town (well, to my PC)

    As always, you should read the Installation/Upgrade manual from top to bottom before upgrading any piece of software, and be aware of all of the pre-requisites and processes.  But for me, my Application Express 20.2 upgrade was as simple as: Download Application Express Unzip to my target location sqlplus / as sysdba SQL> @apexins.sql SYSAUX… Read more

  • The weirdest reason to avoid SELECT *

    The weirdest reason to avoid SELECT *

    A quick Google or Bing search and you’ll find no limit to the number of articles on databases that tell you that using “SELECT *” is a terrible terrible thing to do. Your code will be more fragile.  It is bad for your data dictionary.  You’ll end up with conflicts when you join, the list… Read more

  • Why you keep getting "ORA-01653: unable to extend table"

    Why you keep getting "ORA-01653: unable to extend table"

    Space management used to be hard Way back in the good old days…In fact, before I continue, rest assured that whenever a database person says “good old days” what they really mean is a time totally warped by their imagination where they have managed to block out all of the horror stories and problems they… Read more

  • Compression default settings

    Compression default settings

    If you are loading a data warehouse or similar environment where your default approach would probably be to compress all objects because they are predominantly read only, you do not need to head back into your DDL scripts and add a “COMPRESS” clause to every table. You can set the default compression level at the… Read more

  • When can I use SQL Macros?

    When can I use SQL Macros?

    One of the very cool features we’ve been talking about for 20c is SQL Macros. But you no longer need to wait for a future release of the database to get access to all the goodness of SQL Macros. Why? Because much of the functionality has now been backported to 19c, and is also now… Read more

  • Age calculations – Just how old ARE you?

    Age calculations – Just how old ARE you?

    Any database application that stores information about people will very likely store a person’s date of birth as part of that data. Whether it be for marketing purposes, or part of the security checks for a password reset, or part of the authentication when the inevitable call centre calls you with their “latest, greatest offer”,… Read more

  • The CEIL function, dates and floating point numbers

    The CEIL function, dates and floating point numbers

    Oh yes indeed, I have dug deep into the well of cheesy blog post titles for this one . But hey, I have two teenage children so I figure that I have reached the age where you’ll need to permit me my share of terrible Dad-joke style puns. Consider the two dates below (both of… Read more

  • Docs hack to save you time!

    Docs hack to save you time!

    The tech world is a big and varied place, so whilst there are some database customers running on 19c and have applied the latest RUs etc to get them to (at of time of writing) 19.8, there are plenty of customers running on 18c, plenty running on 12.2, plenty on 12.1 and so forth all… Read more

  • DBMS_JOB and 19c – code changes needed

    DBMS_JOB and 19c – code changes needed

    Sep 2021: Important update at tail of this post Here’s a “gotcha” brought to my attention by one of our AskTOM readers. I’ve mentioned in the past that DBMS_JOB, having been deprecated in favour of DBMS_SCHEDULER, got a new lease of life in 19c because under the covers we translated calls to create a job… Read more

  • Frequency histogram – where did that estimate come from?

    Frequency histogram – where did that estimate come from?

    Frequency histograms in any version of Oracle are pretty cool things, so whenever you have a distribution of data that potentially skewed and the number of distinct values fall under the limit of how many histogram buckets you can have, then a frequency histogram is pretty much a no-brainer. In particular, if you don’t have… Read more

  • How to control resource usage on Autonomous

    How to control resource usage on Autonomous

    When you connect to your Autonomous Database, you get to choose from some predefined services. The services available depends on whether you are using a transaction processing (ATP) or a data warehouse instance (ADW) of the database, but for example, for an ATP database you get the following: Note: This is a screen of the… Read more