Learning is not a spectator sport

Complete listing of posts

  • Oracle Database 12c Release 2 is here !

    Since OpenWorld 2016 when we first saw some of the cool features in Oracle Database 12c Release 2, many IT professionals out there have been exploring the release via our various cloud offerings, but if your organization has not yet embraced the cloud, then March 2017 is a great month for you !  Because you… Read more

  • Better to be safe than sorry…

    I’ve always been worried about taking a script that is fine to run in my non-production environments (in particular a DROP script) and accidentally running it in a Production environment, shortly followed by the typing up of a fresh resume to look for a new job once the mistake is discovered The standard means of… Read more

  • DDL triggers – interesting results

    This question came in on AskTom, yielding a very interesting result when it comes to DDL triggers. To set the scene, I’ll first create a table called T which is just a copy of SCOTT.EMP SQL> create table scott.t as select * from scott.emp; Table created. SQL> desc scott.t Name Null? Type —————————————————————– ——– —————–… Read more

  • Duplicate constraints are impossible right ?

    Here’s a very simple example of a table called PARENT being a (surprise surprise) parent in a referential integrity relationship to a (drum roll for my choice of name) CHILD table SQL> create table parent ( p int, constraint PAR_PK primary key (p) ); Table created. SQL> create table child ( c int, 2 p… Read more

  • The real question is … why are you NOT blogging

    Colleague Jeff Smith published an interesting post the other day about his “rules and regulations” for blogging, but the overriding theme (Ed: – this is my opinion, I’m not speaking for Jeff)  was that the “what” he blogs about was – anything he’s passionate about, and the “when” was – whenever felt inspired to do… Read more

  • Partition count for interval partitioned tables

    When dealing with a RANGE partitioned table, the defined partitions dictate all of the data that can be placed into the table. For example, if I have a SALES table as per below SQL> create table SALES 2 ( cal_year date, 3 txn_id int, … … 24 ) 25 partition by range ( cal_year )… Read more

  • AskTom TV

    Sometimes at conferences I get asked – “How do you know all of the answers for the AskTom questions?” I’d love to say “Because we’re super smart” but the reality is, we’re just like anyone else with a passion for database technology.  We like to research things, apply our skills and use our experience to… Read more

  • 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