Learning is not a spectator sport

Complete listing of posts

  • ROWNUM and ORDER BY

    Just a quick revisit on an old topic so I could link to this post in a Stack Overflow discussion. SQL> create table t ( x int ); Table created. SQL> SQL> insert into t 2 select dbms_random.value(1,1000) 3 from dual 4 connect by level <= 100; 100 rows created. SQL> SQL> select count(*) from… Read more

  • Choose your names wisely

    It all seems simple enough.  You pick a name for an object…and that is the only object that have than name right ?  For example: SQL> create table MY_UNIQUE_NAME ( x int ); Table created. SQL> SQL> create synonym MY_UNIQUE_NAME for MY_UNIQUE_NAME; create synonym MY_UNIQUE_NAME for MY_UNIQUE_NAME * ERROR at line 1: ORA-01471: cannot create… Read more

  • 18c merge partition online

    18c merge partition online

    One of the cool things in 18c is the ability to merge partitions without causing a service interruption.  Here’s a video demonstration of that in action: This is just an accompanying blog post to let you grab the scripts for the demo so that you can try this yourself on livesql.oracle.com, or any of the… Read more

  • 18c–If you can’t wait

    You’ve seen the tweet !! but perhaps the accompanying blog post has tempered your enthusiasm You might be thinking: “I’m not on Exadata – how can I play with 18c?” Well, there is still a way to get ahead of the game and skill up on 18c.  We’ve upgraded livesql.oracle.com to 18c, and we’ve pre-loaded… Read more

  • Execution plans on LiveSQL

    Execution plans on LiveSQL

    To protect the integrity of people’s data, and isolate sessions on LiveSQL, we lock down the environment.  Clearly if you are doing some testing with sensitive data, you don’t want an anonymous member of the user community mining V$SQL to see what commands you have been running.  Conversely, we want to allow people to perform… Read more

  • Oracle Database 18c

    Oracle Database 18c

    Yup…it’s arrived! New name obviously, because we’ve jumped to our new naming model to align with the calendar year as opposed to version number.  You might be thinking “So what?” but it’s a significant change in the way we getting software to customer.  Mike Dietrich blogged about this at length here so I won’t repeat… Read more

  • AskTOM Office Hours for DBA’s

    AskTOM Office Hours for DBA’s

    We had the first AskTOM Office Hours Q&A for Database Administrators yesterday.  Thanks to everyone that showed up, and thanks for the questions. If you missed it, you can catch a replay here Read more

  • NVL vs COALESCE

    NVL vs COALESCE

    Jonathan Lewis just published a blog post about NVL and COALESCE and the optimizer costings for each. There is also perhaps a significant difference between NVL and COALESCE in that the former seems to have an in-built optimization for handling bind variables and nulls.  Consider an application where users optionally pass in search criteria and… Read more

  • NULL’s vs NOT NULL’s and Performance

    NULL’s vs NOT NULL’s and Performance

    When it comes to giving the cost based optimiser the best possible chance to make the “right” decisions, many DBA’s are diligent in keeping statistics up to date, using histograms where appropriate, creating more indexes (or removing surplus indexes). However one often neglected area is that the the null-ness of columns also impacts the optimiser… Read more

  • Getting started…adding an account to use

    Getting started…adding an account to use

    If you’ve read my previous post about getting started with the Oracle database, then hopefully you now have your very own database installed and running, and you have a explored a little with the sample schemas using SQL Developer.  Perhaps now you want to venture out into your own database development, and for that, you… Read more

  • Those pesky LONG columns

    Those pesky LONG columns

    There was a time, many moons ago when CLOB, BLOB and BFILE did not exist as data types. So if you had anything longer than a few kilobytes of data to store, you had to use a LONG or a LONG RAW.  But those data types came with all sorts of restrictions and frustrations, and… Read more

  • Identity columns in 12c … just a sequence ?

    This question came to me over Twitter, so I thought I’d whip out a quick post on it   Yes, we do implement the IDENTITY column via a sequence.  (Digression – I think this is a smart thing to do.  After all, we’ve had sequences for 20 years, so we know how they work, they… Read more