Learning is not a spectator sport

Complete listing of posts

  • Quick tip on Function Based Indexes

    For “normal” indexes, USER_IND_COLUMNS will contain the column name(s) for an index, but things (appear to) go astray when looking at function-based indexes. SQL> create table blah ( x varchar2(30)); Table created. SQL> create index blah_ix on blah ( upper(x)); Index created. SQL> select column_name from user_ind_columns 2 where index_name = ‘BLAH_IX’ 3 / COLUMN_NAME… Read more

  • A little known RI clause

    Most people are aware of this standard referential integrity control, namely you can’t delete the parent if children exist SQL> drop table PAR cascade constraints purge; Table dropped. SQL> create table PAR ( p int primary key, data int); Table created. SQL> insert into PAR 2 select rownum, rownum 3 from dual connect by level… Read more

  • Active and Inactive Sessions

    Most people are aware of the STATUS column in V$SESSION. If it’s ‘ACTIVE’ then that connection is in the process of consuming database resources (running a SQL statement etc). However, a lesser known column which is probably even more useful is the LAST_CALL_ET column. The name suggests the time since the last call, but the… Read more

  • Why PLSQL ?

    With Collaborate 2016 under way, there seems no better time to reflect on why PL/SQL is the natural choice for anyone who loves to code, and loves their data Read more

  • Technology debates

    As always happens from time to time, we had the following request on AskTom today: Could you list down 2 reasons why sql server is better than oracle? Then 2 counter reasons as to why why oracle is better than sql server   And I thought I’d reproduce my response here   I’ll answer it… Read more

  • Scheduler frequency and repeat intervals

    When using strings such as “FREQ=DAILY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9,10″ within the scheduler, sometimes its not readily apparent how this will translate to actual dates and times of the day that the scheduled activity will run. To help you understand, a nice little utility is to use EVALUATE_CALENDAR_STRING” SQL> set serveroutput on size 999999 SQL> SQL> declare… Read more

  • AskTom – the personal touch

    AskTom has been answering questions from the user community for nearly 16 years. But what if that service could have an even more personal touch ?  We are pleased announce the largest advance in AskTom service since it started way back in 2000. See all the details in the video below for the new service… Read more

  • Dealing with URL’s

    If you are creating or handling URL’s, there is a nice little package routine that assists with handling all those pesky special characters. I’ve added a little wrapper just to make the parameter handling easier SQL> create or replace function utl_url_escape(x varchar2) return varchar2 is 2 begin 3 return utl_url.escape(x,true); 4 end; 5 / Function… Read more

  • Opatch quick tip

    I was patching my 12.1.0.2 home installation to the latest patchset today, and got the following error: C:\oracle\stage\22581007>opatch apply Oracle Interim Patch Installer version 12.1.0.1.10 Copyright (c) 2016, Oracle Corporation. All rights reserved. Oracle Home : C:\oracle\product\1210~1.2 Central Inventory : C:\Program Files\Oracle\Inventory from : n/a OPatch version : 12.1.0.1.10 OUI version : 12.1.0.2.0 Log file… Read more

  • Median in SQL

    Are you stuck with a database that does not offer analytic SQL facilities ? Never mind, you can use the following trivial query can determine the median salary from the EMP table SQL> select avg(sal) from 2 ( select x.sal 3 from scott.EMP x, scott.EMP y 4 group by x.sal 5 having sum(sign(1-sign(y.sal-x.sal))) in 6… Read more

  • Are your SQL Plus scripts going to ‘ell ?

    Excuse the hideous pun in the title.  All will become clear shortly. Often we use SQL Plus to run scripts on a scheduled basis in our database.  The “problem” is, they need to connect to that database. Here’s a simple script I want to run: and here’s my very sophisticated batch file (which could just… Read more

  • Up to date text indexes

    If you really need your text indexes to be “super dooper” up to date, that is, always in sync with your data, you can set the SYNC level to be ON COMMIT.  For example, SQL> create table t1 ( 2 x int, 3 y varchar2(1000) ) 4 ; Table created. SQL> SQL> create index t1_iz… Read more