Learning is not a spectator sport

Complete listing of posts

  • Optimizer curiosity in 12.1.0.2

    For almost as long as I can remember, the optimizer has had a nifty little trick when you (in effect) try to combine two different usage models within a single SQL.  To explain that, I’m referring to the common scenario of: “If a bind value is provided by the caller, then use it, otherwise it… Read more

  • Cross-platform database migration

    Last weekend, we faced one of the larger challenges that we’ve faced in my time at my current client.  Migrate multiple database systems, across multiples sites, all to new hardware (with new endian format), new database version (12.1.0.2)…and of course, try to do it with as small a disruption to the service as possible. We… Read more

  • Datapump TTS quick tip

    I was trying to transport a large number of tablespace datafiles from one database to another, and impdp kept crashing with “PL/SQL: numeric or value error: character string buffer too small.” It turns out there is a limit on what you can put on a single line in a parameter file. Hence if you have… Read more

  • From Product X to SQL Developer

    I recently worked at a company that used “Product X” for all of it SQL and PL/SQL activities.  There’s no real need to reveal what “Product X” is, because this isn’t a post about whether as a product it was good, bad or somewhere in between.  “Product X” met the needs of the Oracle developers… Read more

  • dropping tablespaces and queues – not happy companions

    grrr… SQL> drop tablespace MY_TSPACE including contents; drop tablespace MY_TSPACE including contents * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-24005: Inappropriate utilities used to perform DDL on AQ table MY_SCHEMA.MY_QUEUE_TABLE You would think that if you ask to drop a tablespace, then you’re pretty confident that you want all… Read more

  • temporary undo in 12c

    This feature seems a no-brainer once you’re on 12c. After all, why would you want your global temporary tables to be hammering away at your redo logs. With that in mind, my initial tinkering with the feature had me getting ready for a “blog rant” because it did not seem to work. Let’s see how… Read more

  • NUMBER data type…what harm can it do ?

    There’s a somewhat sour discussion going on based attached to the video at https://www.youtube.com/watch?v=jZW-uLb52xk Whether you agree or disagree with the video or the comments, or (sadly) the animosity in them, it does lead to an interesting bit of investigation when it comes to data types with arbitrary precision, which was stumbled upon by a… Read more

  • Avoiding the COMMIT bomb!

    I’m an Oracle dinosaur, so I like using SQL Plus.  Its simple, fast, comes with every version and installation and platform, and I’m very familiar with it.  (And who knows, it might still be at the forefront of the Oracle development teams!  http://www.slideshare.net/hillbillyToad/sqlcl-overview-a-new-command-line-interface-for-oracle-database ) But there is one important thing I always take care of… Read more

  • (OT) an idea for Easter (and well…every day)

    I don’t travel as frequently as some people do, but with OpenWorld and various Oracle conferences each year, I definitely see my fair share of the inside of a plane and a hotel room.  To pass the time on flights, I try to read, but when you’re reading on a work-based trip, it’s a case… Read more

  • Edition based redefinition – an apology

    In April 2008 (wow, does time fly!) I used the following picture in my “11g features for Developers” presentation at the Australian Oracle User Group conference. I think the picture is from the movie “Indiana Jones and the Last Crusade”… where they sought the Holy Grail. I used the picture because I said that Edition… Read more

  • In-memory – can you REALLY drop those indexes ?

    Its not a hard sell to convince anyone that a whopping huge great chunk of compressed column-based memory is going to assist with those analytic-style queries. The In-memory option seems a natural fit, especially for those people where the bottleneck in their infrastructure is the capacity to consume data from storage. What perhaps a more… Read more

  • WHEN OTHERS … sometimes just not enough

    We currently are working on a bug with Oracle Support with dbms_stats in 12.1.0.2. That may be the subject of a latter post, but in a nutshell, here’s the issue SQL> exec dbms_stats.gather_table_stats(‘MY_SCHEMA’, ‘MY_SCHEMA’); BEGIN dbms_stats.gather_table_stats(‘MY_SCHEMA’, ‘MY_SCHEMA’); END; * ERROR at line 1: ORA-21700: object does not exist or is marked for delete Now obviously… Read more