Learning is not a spectator sport

Complete listing of posts

  • Level up your Zoom game

    Level up your Zoom game

    When Zoom first came into the conversation, we used it for remote meetings but a certain pandemic suddenly brought Zoom into a much sharper focus, and many of us are now using it for all parts of our job. So whilst my job has always meant a large percentage of my time was delivering content Read more

  • "TWP" – technical white paper

    "TWP" – technical white paper

    Over the years, as I used to read various Oracle white papers I observed two things. 1) They were often PDFs prefixed with the acronym “twp”, which I assume (without confirmation) stands for “technical white paper” 2) They were often hard to find more than once Typically I hence would either bookmark them, or download Read more

  • Pi day 2021

    Pi day 2021

    As friend Bruno put it so eloquently… …but every day is a good day to do some SQL.  So here’s some SQL pi calculations I’ve tweeted out on this day over the years, that I thought I would capture here for posterity. SQL rocks. SQL> with t(n,p,s) as 2 ( select sqrt(2) n, sqrt(2)/2 p Read more

  • New podcast episode! Block internals

    New podcast episode! Block internals

    I need to be honest right up front here. I’ve aimed for the skies and only time will tell if I’ve fallen short . Can it be done? Can you delve into the hex dumps, the byte headers, the acronyms all wrapped up in the magic that is an Oracle Database block with no slides, Read more

  • DBMS_METADATA – hitting its limit

    DBMS_METADATA – hitting its limit

    I was helping out a customer today with what they thought was a bug in DBMS_METADATA. They were using it to unload a complete set of DDL scripts for their schema, and their schema is big. Not big in the sense of data volume but in the number of database objects contained in the schema. Read more

  • Extended VARCHAR2 and the hidden LOB

    Extended VARCHAR2 and the hidden LOB

    In 12c, there was much rejoicing when VARCHAR2 columns could be extended from beyond their current 4000 byte limit to 32767. But whatever the limit is, an Oracle block is an Oracle block and there are limits to what you can fit inside it. Hence an extended VARCHAR2 column is stored as a CLOB under Read more

  • ALL_ARGUMENTS missing information?

    ALL_ARGUMENTS missing information?

    I made a interesting discovery earlier this week when I was doing some analysis for some AskTom database procedures when prepping some regression tests. Here is a quick summary so that you won’t get the surprise I got. In 11g, if you create a packaged type and procedure that references that type, for example as Read more

  • Free space requirements for ALTER TABLE

    Free space requirements for ALTER TABLE

    One of the very cool features from 12.2 onwards is that the vast majority of segment operations that require rewrite of the existing data can be done online without interruption to service. But whether an operation is being done online or not, since DDL operations need to be atomic, the general rule is that when Read more

  • The podcast is go!

    The podcast is go!

    A few weeks back, I posted that I would give podcasting a trial. I’m thrilled to see that the response has been very supportive, with plenty of downloads and listens, so (as you hear at any keynote launch nowadays). “Today, I’m proud to announce…” …that the podcast is now available on the popular podcast platforms. Read more

  • Expanded TYPE definitions for PL/SQL 21c

    Expanded TYPE definitions for PL/SQL 21c

    There has always been that odd conflict between the language you use for SQL versus the language you use for PL/SQL when it comes to expressing what appears to the be the same thing. PL/SQL: Do want a datatype that contains two elements? Use a RECORD SQL> declare 2 type MY_EMPS is record ( 3 Read more

  • Counting business days between 2 dates

    Counting business days between 2 dates

    One of the most commonly hit questions on AskTom is how to count the number of work days (Mon => Fri) between a start and end date range. This is not a particular tough problem to solve since we have easy access to the day of the week via TO_CHAR, thus simply cycling between the Read more

  • Blockchain tables are here in 19c!

    Blockchain tables are here in 19c!

    Slow down there….Take a breath. Because if you’ve read the title of this post and you’re already logging on to your 19c database, I need to you STOP.  Before you go anywhere, please read this post first. OK … lets proceed, now that you’re not going to end up with a table you can’t drop! Read more