Learning is not a spectator sport

Complete listing of posts

  • The Fractional Part of a TIMESTAMP. Be careful.

    The Fractional Part of a TIMESTAMP. Be careful.

    This discovery came in from an AskTom customer but I figured it’s worth passing on to any PL/SQL programmers out there. The FF element of the format mask is for the fractional part of a second, and thus only applies to the TIMESTAMP series of data types. A timestamp can contain up to 9 digits Read more

  • Implementing dynamic partitions AND subpartitions

    Implementing dynamic partitions AND subpartitions

    One of the cool things to come out of the partitioning option many moons ago was the feature known as interval partitioning. As a quick reprise/refresher, in times gone by, many a DBA has had that fateful call in the middle of the night because someone had tried to insert a row in a partitioned Read more

  • DBMS_OUTPUT … A (formatting) love story

    DBMS_OUTPUT … A (formatting) love story

    What happens when you want to serenade your partner with a romantic ditty, but you’re a database professional as well? Well obviously you would put those lyrics to paper in the only way you know how to as a true database geek…and that of course is DBMS_OUTPUT! But needless to say, your hopes of impressing Read more

  • My poor developers! PLSQL is going to be so hard on them!

    My poor developers! PLSQL is going to be so hard on them!

    So I started all of this by talking about how PLSQL is going to give you a performance boost, just by moving some of that SQL code into the database and out of your Java (Rust, Go, Javascript, Python, insert any app tier language here). People then called me out on my benchmark setup, so Read more

  • A little bit of PLSQL and a little bit of network

    A little bit of PLSQL and a little bit of network

    So after my post yesterday about PL/SQL being a no-brainer to get an easy boost to application performance, there was an interesting collection of responses on social media from both sides of the fence. From the “the database is the universe” camp, there was criticism that the 30% benefit of PL/SQL was in fact understating Read more

  • A little bit of PLSQL can be a huge win

    A little bit of PLSQL can be a huge win

    I rarely blog about PLSQL for a very simple reason. It quickly becomes like Halloween night because all the ghouls and goblins come out to play . No matter what I post, I immediately get inundated with a swag of comments and tweets about “stored procedures are evil”, “PLSQL is vendor lock-in”, “Server side code Read more

  • DBMS_METADATA for INDEXES

    DBMS_METADATA for INDEXES

    Just a quick tip on using DBMS_METADATA that caught me out the other day. If you have a table and you wish to get the DDL that is “related” to the table, for example object grants or audit settings etc, then there is a GET_DEPENDENT_DDL API to do this. For example, to get the object Read more

  • Enforcing DEFAULT values

    Enforcing DEFAULT values

    Handling default values on columns has been an age old problem that we solved in 12c with extensions to the default clause. Originally the issue with a DEFAULT setting was that is was only ever considered if the column was not specified at all in the INSERT statement. For example, here’s a subset of the Read more

  • Block recovery a lot easier than I thought

    Block recovery a lot easier than I thought

    I tweeted this snippet the other day because to be honest I was just overjoyed about being able to get the travel laptop out of its case and fire it up again. In every virtual Office Hours session and every virtual event I’ve done in the past two years, I’ve urged people to take whatever Read more

  • When re-ordering columns is warranted

    When re-ordering columns is warranted

    Nearly 10 years ago(!!!) I blogged about how you can toggle columns to invisible and back again as a means of re-ordering the columns in the table. Of course, this is purely a logical re-ordering of the columns, and the post was mainly meant as a bit of a laugh, but I see references to Read more

  • Don’t run tight on PGA

    Don’t run tight on PGA

    The database on my laptop seemed slow this morning. Normally I would put this down to the fact that I’ve got 30 million browser tabs open and never seem to be able to close them down , but today things seemed especially slow, even just navigating around with SQL Developer etc. So I threw a Read more

  • After you clone your pluggable database

    After you clone your pluggable database

    Recently I cloned one my (pluggable) databases to a throwaway copy so that I could do some testing of an upcoming application deployment against comparable data volumes to make sure that the deployment would go smoothly. The clone process worked fine; that’s one of the things I enjoy about pluggable databases, just single CREATE command Read more