Learning is not a spectator sport

Complete listing of posts

  • Certification exams

    This is an update of a blog item I made nearly 15 years ago … but I think it still holds true Is doing the OCP exams worthwhile ? Yes, but not for the reasons you may be thinking. The OCP exams are a relatively cheap way of identifying possible weaknesses in your knowledge base… Read more

  • A little known ORDER BY extension

    Within a CONNECT BY statement, you can order siblings, that is, under a particular branch of the hierarchy, the child entries can be ordered, but you still preserve the hierarchy. SQL> select lpad(‘*’, level, ‘*’ ) || ename ename 2 from emp 3 start with mgr is null 4 connect by prior empno = mgr… Read more

  • Multiple partitions

    In 12c, one of the nice changes to come along is that partition maintenance operations can now be done on multiple partitions, for example ALTER TABLE t1 MERGE PARTITIONS p01, p02, p03, p04 INTO p0; ALTER TABLE SPLIT PARTITION p0 INTO (PARTITION p01 VALUES LESS THAN (25), PARTITION p02 VALUES LESS THAN (50), PARTITION p03… Read more

  • AskTom–help us to help you

    On AskTom, obviously you’re are going to be posting code and/or code output.  It’s probably going to be the most vital part of the question’s test case so that we can help you out. So please dont forget the “<code>” and “</code>” tags around your question. Otherwise we get this – it’s all wrapped and… Read more

  • Resolutions for 2016

    I remember at one of the first Mathematics lectures I attended at University, the lecturer demonstrated some principle (which I don’t recall, and certainly wouldn’t even comprehend now ), and then turned and said to the class: “Now THAT is a pretty exciting result”. Being fresh out of high school, and like most teenagers, thinking… Read more

  • New Years Resolution–test cases

    If the following email came across your desk “I have two tables EMP(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) and DEPT(DEPTNO,DNAME,LOCATION).  When I join them I’m getting multiple rows for the same employee” my question would be – how would you proceed ? What is the first thing you need to do to even begin to assist ? You need data. … Read more

  • Merry Christmas

    To the tune of “Let It Snow” Well, the response of the app is frightful, and users are getting spiteful. They claim the database is slow, “Make it go”, “Make it go”, “Make it go” It doesn’t show signs of locking, Yet the performance it still quite shocking, The CPU is right down low, but… Read more

  • How big is my offline datafile ?

    “Strange” things happen when you take a tablespace or its datafile(s) offline. SQL> create tablespace TS datafile ‘C:\ORACLE\ORADATA\NP12\TS.DBF’ size 20m; Tablespace created. SQL> alter tablespace TS offline; Tablespace altered. SQL> select bytes from dba_data_files where file_name = ‘C:\ORACLE\ORADATA\NP12\TS.DBF’; BYTES ———- We lose access to the file size. So how can you tell how large that… Read more

  • How do I stop the use of TRUNCATE

    Truncate is one of those commands that cannot be undone trivially…In fact, you might well be looking at a database recovery in order to get a truncated table back to a “full” state.  So often we’d like to bar the use of it.  Obviously, to truncate a table in another schema you need DROP ANY… Read more

  • The art of describing a problem

    Now that I’ve been answering AskTom questions for a while, there is obviously a huge amount of variety in the topics and the problems that people are encountering.  And it always feels good when I can help those people.  Chris and I don’t always manage to solve everyone’s problem (sometimes we just have to say… Read more

  • Highlights from UKOUG

    As my train whizzes past Conventry, *and* has wifi, it seems a good time to reflect on this years conference. I’ve always loved this conference.  I lived in the UK from 1998 to 2003, and a RDBMS SIG meeting was my first foray into presenting on an Oracle topic.  At the time, I thought I’d… Read more

  • CLOBs are not just big varchars

    We had a question on AskTom the other day, talking about comparing clobs.  (Paraphrasing) the question inquired as to why we had a DBMS_LOB.COMPARE function, when you can you just compare clobs directly, using the following example: SQL> create table T ( x clob, y clob ); Table created. SQL> create or replace 2 trigger… Read more