Learning is not a spectator sport

Complete listing of posts

  • The SQL Loader log file … as data

    I had an interesting AskTom question recently where the poster was using SQL Loader to load in tables, but wanted to be able to analyze the resultant log file after execution.  And of course, what better way to analyze..well…anything…than with a database and some SQL. So we need to be able to access the log Read more

  • How do I get my trace files on a db-as-a-service

    Yeah, sure it would be cool to crank up some big time powered VM’s in the cloud and let rip, but the reality is – if you’re starting out on a cloud exploration, you probably want to (initially at least) just dip your toes in the water and start with something small.  For example, if Read more

  • Haversine PL/SQL

    I didn’t see a PL/SQL version on https://rosettacode.org for the Haversine formula so here’s a version for anyone that needs it. SQL> create or replace 2 function p2p_distance( 3 p_latitude1 number, 4 p_longitude1 number, 5 p_latitude2 number, 6 p_longitude2 number) return number deterministic is 7 earth_radius number := 6371; 8 pi_approx number := 3.1415927/180; 9 Read more

  • Logwriter I/O

    If you are on any version of the database past 10.2.0.4, then savvy DBA’s may have noticed the following message popping up occasionally in their trace files Warning: log write time 540ms, size 444KB In itself, that is quite a nice little addition – an informational message letting you know that perhaps your log writer Read more

  • Extending Flashback Data Archive in 12c

    Flashback Data Archive (previously called Total Recall) has been around for a long time now. For those unfamiliar with it, (and by the way, if you are on Enterprise Edition, you should be familiar with it, because its a free feature), here is a very quick primer. Create a tablespace to hold your history, and Read more

  • Hack-a-Mongo

    I was reading an article today about how 10,000+ Mongo installations that are/were openly accessible on the internet have now been captured by ransomware, with nearly 100,000 other instances potentially vulnerable to the same issue. Now, since I’m an Oracle blogger, you may be inclined to think the post is going to jump on the Read more

  • AskTom takes another step forward

    For over 16 years, AskTom has been one of the most valuable resources available to developers and database administrators working with the Oracle Database.  With over 20,000 questions tackled and answered, along with over 120,000 follow up’s to additional queries, it remains an outstanding knowledgebase of Oracle assistance. And today, AskTom just got a whole Read more

  • What you "liked" last year…

    Well…when I say “liked”, what I mean is “the stuff you all clicked on a lot” last year. Whether you liked it or not will remain one of those great mysteries The top 6 posts from 2016 were: https://connormcdonald.wordpress.com/2015/11/25/ora-14758-last-partition-cannot-be-dropped/ https://connormcdonald.wordpress.com/2013/01/20/exchange-partition-those-pesky-columns/ https://connormcdonald.wordpress.com/2013/08/26/compressed-partitions-are-not-compressed-tables/ https://connormcdonald.wordpress.com/2014/04/12/pluggable-database-and-restricted-sessions/ https://connormcdonald.wordpress.com/2016/06/09/parsing-no-big-deal-eh/ https://connormcdonald.wordpress.com/2016/03/26/are-your-sql-plus-scripts-going-to-ell/ Nice to see a 12c post in there – 2017 is Read more

  • When local partitions….aren’t

    Let’s say I’ve got a partitioned table, and because New Year’s eve is coming around, I certainly don’t want to be called out at 12:01am because I forgot to add the required partition for the upcoming year . Since 11g, I can sleep easy at night by using the INTERVAL partition scheme. Here’s my table Read more

  • UKOUG 2016

    Just a little video montage of the fun and learning from UKOUG.  A great conference every year. I was fortunate enough to receive a Best Speaker award (from a 12c features talk given at the 2015 conference). I gave three talks, one of which was the Database Keynote for 2016 – I felt very privileged Read more

  • Multi-table insert

    An interesting question came through on AskTom recently.  The requirement was to perform a single pass through a source table, and load the data into three target tables. Now that’s trivially achieved with a multi-table insert, but there was a subtle “twist” on this requirement.  Each of the three target tables may already contain some, Read more

  • A 12.2 treat for the festive season

    We’re being asked to store more and more data, yet keep backup windows, query performance and the like unchanged, no matter how much we store. As a result, more and more database shops are needing to partition their data. The problem is – partitioning data is a significant restructure of the data, which thus incurs Read more