Learning is not a spectator sport

Complete listing of posts

  • Nulls are not stored in indexes … most of the time

    Nulls are not stored in indexes … most of the time

    This question got posed on Twitter today I answered briefly on Twitter, but I thought I should give it some “meat” with a blog post. Firstly, lets explore the commonly understood mechanism where in a conventional index, nulls are not stored and hence you can have multiple index entries (so to speak) where the value… Read more

  • Simple transaction benchmark on Oracle Database

    Simple transaction benchmark on Oracle Database

    Man, it gets my goat when people start pontificating nonsense on Twitter. Yeah, I know, I know, I shouldn’t get upset, because 99% of everything on social media is nonsense anyway, but it is when people roll out claims about database tech that I tend to get tetchy Today it’s that familiar old chestnut: “Constraints… Read more

  • When upgrading, choose 19c not 18c

    When upgrading, choose 19c not 18c

    I had someone say to me at an event recently: “We’re are going to upgrade to 18c, because 19c is new and is probably less stable”. Let me sum up that sentiment simply: It’s Wrong Now, don’t get me wrong. I am not claiming that every Oracle release is perfect, contains zero bugs, never has… Read more

  • GROUP BY better than DISTINCT for materialized views

    GROUP BY better than DISTINCT for materialized views

    One of the cool things with materialised* views in Oracle is their ability to be kept in sync with the source table(s) from which they are derived from, in real time or near real time. To achieve this, we typically employ mechanisms such as materialised view logs to capture modifications to the source tables, and… Read more

  • New LINESIZE settings for SQLPlus

    New LINESIZE settings for SQLPlus

    Anyone that has used SQL Plus since its inception will be familiar with this frustration: There is my window, with all of that lovely free space at the right hand side for the results of my query to flow into…but the output wraps anyway! This is because SQL Plus was born in a different era… Read more

  • Handling UTL_FILE_DIR and DIRECTORY objects

    Handling UTL_FILE_DIR and DIRECTORY objects

    A couple of years back (wow…time flies!) I made a video and a post about the de-support of UTL_FILE_DIR in 18c. This was good thing because the number of people opting for “utl_file_dir=*” in their init.ora file, and hence opening themselves up to all sorts of risks seemed to be large! (The post elaborates on… Read more

  • Checking status of the Database Scheduler

    Checking status of the Database Scheduler

    When the scheduler came into existence in Oracle 10g, there was a cool API call that could be used to temporarily turn the entire scheduler off.  That command was: SQL> exec dbms_scheduler.set_scheduler_attribute(‘SCHEDULER_DISABLED’,’TRUE’) PL/SQL procedure successfully completed. However, a quick check of the documentation in later releases, no longer makes any reference to this attribute: I… Read more

  • Avoid orphaned Lockdown Profiles

    Avoid orphaned Lockdown Profiles

    Most administrators are now aware of the lockdown profile facility in the Oracle database (available since 12c). They allow a more granular level of privilege control of components and functionality within the database. I’m not going to spend more time than that on what they do – this post is just to highlight a small… Read more

  • Getting the most out of in-memory – part 2

    Getting the most out of in-memory – part 2

    In the previous post, I described the importance of checking V$IM_SEGMENTS to ensure that the in-memory store is populated to have confidence that you are indeed getting the benefit of the feature. But even if the store has been populated, when it comes to virtual columns, additional care is needed to make sure you can… Read more

  • Getting the most out of in-memory

    Getting the most out of in-memory

    First of all … Happy New Year! This is my first post for 2020. Last year, I fell just short of 100 blog posts for the year – so this year, I’m starting early and hopefully I can crack the 100 mark! Anyway..onto the post. The in-memory option in the Oracle database can yield some… Read more

  • Why all pluggable database names should be unique

    Why all pluggable database names should be unique

    A quick tip just in time for Christmas I logged on to my database this morning, and things just didn’t look right. In fact, they looked down right alarming. All my objects were gone, my user account had the wrong password..It was almost as if I was connecting to a totally different database! That’s because… Read more

  • Listener log data mining with SQL

    Listener log data mining with SQL

    If you take a look at the log files created by the listener, there is obviously a nice wealth of information in there. We get service updates, connections etc, all of which might be useful particularly in terms of auditing security However, it also is in a fairly loose text format, which means ideally I’d… Read more