Learning is not a spectator sport

Complete listing of posts

  • Sequences in PL/SQL

    Sequences in PL/SQL

    Time to put another myth to bed. Way back in Oracle 9 (I think) when referencing a sequence from PL/SQL the requirement of getting a sequence value using a SELECT statement was lifted. Thus instead of coding SELECT MYSEQUENCE.NEXTVAL INTO MYVARIABLE FROM DUAL you could simply write MYVARIABLE := MYSEQUENCE.NEXTVAL ; However, it is important… Read more

  • The APEX data dictionary

    The APEX data dictionary

    Anyone who has used APEX will know that in addition to the Builder UI, there is a comprehensive data dictionary for APEX-prefixed views to expose all the metadata associated with APEX applications and workspaces.   SQL> select view_name 2 from dba_views 3 where owner = ‘APEX_240200’ 4 and view_name like ‘APEX%’ 5 order by 1;… Read more

  • Understanding REUSE STORAGE

    Understanding REUSE STORAGE

    I had a question from a customer recently about TRUNCATE versus TRUNCATE REUSE STORAGE. They pointed out that after a TRUNCATE REUSE STORAGE, the number of blocks for table dropped to zero which they were not expecting. Lets explain why zero blocks is expected, and does not imply that we ignored the REUSE STORAGE command.… Read more

  • Creating rows from nothing – time for something better

    Creating rows from nothing – time for something better

    Nine(!) years ago I published one of the first videos on my YouTube channel about quickly generating rows from the database and not much has really changed since then when it comes generating rows. Most of us are now familiar with the CONNECT FROM trick using DUAL. But if like me, you are sick and… Read more

  • JSON_OBJECT_T and the PGA

    JSON_OBJECT_T and the PGA

    Just a quick heads up for anyone using JSON_OBJECT_T in PL/SQL that was brought to my attention by an AskTOM member It looks like repeated use of JSON_OBJECT_T in an associative array can consume PGA until such point as you’ll hit errors. Here’s an example of a associative array of VARCHAR2 growing a large size… Read more

  • More zero outage goodies

    More zero outage goodies

    Yesterday I published a video about how to un-partition a table without any outage using a two step process, because no native one-step command existed to do it. But (spoiler alert) I also posted in that same video, that a one-step command did in fact exist, but it was not documented yet. You’ll be pleased… Read more

  • JDBC and the FetchSize – Part 2

    JDBC and the FetchSize – Part 2

    If you’re a user or follower of Hibernate, you probably saw Gavin King’s excellent post on the JDBC fetch size issue tackled from a Hibernate perspective. I posted some performance figures  on my original post, which demonstrated how you can dramatically improve the consumption rate of rows from the database with an appropriate fetch size.… Read more

  • JDBC, the FetchSize and staying up to date

    JDBC, the FetchSize and staying up to date

    When you are querying rows from an Oracle Database, a key element of the Oracle JDBC driver related to performance is an attribute known as the fetch size. For those unfamiliar with the fetch size, when you issue a FETCH for a single row from our JDBC driver, we will go grab a batch of… Read more

  • DataPump – niche fix

    DataPump – niche fix

    On a Office Hours session a while back, someone brought up this niche issue when use DataPump and you have “overlapping” indexes. What do I mean by “overlapping indexes”? Consider the following example.  Notice that I have two indexes (one implicitly create as part of the constraint definition, and one explicitly created with CREATE INDEX)… Read more

  • OPatch – The “File In Use” error that wasn’t

    OPatch – The “File In Use” error that wasn’t

    Over the holiday break I took the opportunity to apply the latest RU to some of my databases. On my 21c Windows instance, as I was running “opatch apply” I got the following error: X:\tmp\36878842>opatch apply Oracle Interim Patch Installer version 12.2.0.1.44 Copyright (c) 2025, Oracle Corporation. All rights reserved. Oracle Home : c:\oracle\product\21 Central… Read more

  • The year in numbers

    The year in numbers

    The numbers might be one or two off, because I’m not the kind of person who really sweats hard over the numbers. I’m much more interested in what content you find most beneficial for your own success, so if you have any particular requests for 2025, I’m always happy to take your comments on board.… Read more

  • Christmas Puzzle – The Solution

    Christmas Puzzle – The Solution

    A huge “Well done!” to all those people that made the realisation that we had to be storing the data in a datatype that can manipulate the data internally as it is stored. Thus datatypes like JSON and XMLTYPE became the obvious candidates. No-one matched exactly the way I constructed the problem, but for those… Read more