UKOUG Tech 14

On Sunday I will be heading North from London to Liverpool for 4 days, to attend another UK Oracle User Group conference – #UKOUG_Tech14

I’m sure it will be as wonderful and informative a 4 days as you can get in the Oracle technical area. The hard part of attending is working out what and who to see.

I will be presenting there again – this time a talk on Goldengate late on the final (more...)

Epicaricacy

Wednesday’s word is Epicaricacy, meaning to take joy in the misfortune of others. Yes, this is the (admittedly rare) English word that doesn’t exist, causing an outbreak of German in otherwise sane sentence.

It’s interesting that taking pleasure from others misfortune as a single ‘concept'; Schadenfreude, skadeglädje, leedvermaak, skadefryd, skadeglädje and vahingonilo, are all Northern European words (being German, Swedish, Dutch, Danish, Norwegian and Finnish respectively). Us North Europeans seem to like little more that a good (more...)

Adding a DEFAULT column in 12C

I was at a talk recently, and there was an update by Jason Arneil about adding columns to tables with DEFAULT values in Oracle 12C. The NOT NULL restriction has been lifted and now Oracle cleverly intercepts the null value and replaces it with the DEFAULT meta-data without storing it in the table. To repeat the 11G experiment I ran recently:

 

SQL> alter table ncha.tab1 add (filler_default char(1000) default 'EXPAND' not  (more...)

Gambrinous

Wednesday’s word is Gambrinous, meaning full of beer, allegedly named after a Flemish King who is said to have invented beer.
 
Use: It was difficult to determine the most gambrinous group. Oakies, ACED, or Oakies ∩ ACED. However, it was not Oakies ∩ ACED ∩ BARBIGEROUS individuals, who tend to stick to cider.
 
 


Filed under: Prevarication

Adding NOT NULL Columns with DEFAULT values

In Oracle, if we add a column to a table which is NOT NULL, we are allowed to do it directly, in a single statement, as long as we supply a DEFAULT value to populate any pre-existing rows.
This would mean that every row in the table was updated with the default value. This could be a pretty nasty side effect, as lengthening every row in the table will inevitably lead to chained rows, and (more...)

Club Oracle in London – 12th November 2014

Just a timely note to publicise the e-DBA and Jonathan Lewis #cluboracle event in London on 12th November 2014.

I’m a great fan of community events, as I hope my blog, tweets, presentations and support of the UKOUG, chairing many SIGs over the last few years, will attest. It’s the second best way to learn anything, talking with your peers who have already worked it out, and those who have the same problem to (more...)

When to use the NOLOCK hint in SQL Server

I frequently hear of, and see, developers and DBA’s using the NOLOCK hint within SQL Server to bypass the locking mechanism and return their data sets as soon as possible. There are times when this is OK, such as when you are running an ad hoc query and are only interested in approximate results. It is somewhat less OK to write this hint into application code and reports, unless you don’t actually care whether the (more...)

Extending an ACFS filesystem dynamically.

To extend an ACFS cluster filesystem dynamically, we need to use the acfsutil command:

node01:/u01/grid>/sbin/acfsutil size +10G /u02
acfsutil size: ACFS-03008: The volume could not be resized.  The volume expansion limit has been reached.
acfsutil size: ACFS-03216: The ADVM compatibility attribute for the diskgroup was below the required
                           version (11.2.0.4.0) for unlimited volume expansions.

Oh dear, not 11.0.2.4, so you can only extend volumes dynamically a (more...)

Grid Infrastructure Disk Space Problem – CHM DB file: crfclust.bdb

The Grid Infrastructure filesystem was reporting that it was a bit full today (release 11.2.0.4). This was tracked down to the “crfclust.bdb” file, which records information about the cluster health for monitoring purposes. It was 26GB. It’s not supposed to get bigger than 1GB so this is probably a bug, but let’s explicitly resolve the size issue right now and search Oracle support later. Worst case, bdb (Berkerley Database) files get (more...)

Releasing to schemas the easy way

Sometimes we occasionally just miss the obvious, for years. Just noticed that an easy way to release code to a particular schema is to login as your normal DBA user (USER1) [as preferred by audit], use the alter session command to switch to point to the release schema (USER2) and run your DDL. Oracle behaves, from an object-owner perspective, as if you are logged-in as the schema owner without all the negative aspects of actually (more...)