Oracle 11g XE installed on Windows 8.1

I count myself lucky if a post here gets a few hundred views. I have two posts that have hit 4000+ views, and a third edging towards 3000.

In third place is a reference to foreign keys referencing unique constraints rather than a primary key.

In second place, and heading towards its second birthday, was my desire for SQL Developer to have a "keep awake" function that stops sessions being killed. It still doesn't but will restore connections. 

In first place, was an almost throwaway article from when I migrated my netbook from XP to Windows 8. I installed Oracle 11g XE on it, and apparently a lot of people are (or were) interested in that. I guess people get Windows 8 on a machine and wonder if it is worth trying an XE install. The XE install on Windows is trivially simple:

1. Download
2. Unzip
3. Run the setup program
4. Choose a directory
5. Enjoy a cup of coffee while it churns through. You have time for a biscuit too.

That article is nearing its first anniversary. In honour of that occasion, I upgraded to Windows 8.1. Oracle XE broke. Specifically I couldn't find the services that ran the listener or the database or anything Oracle related. A repair install of XE didn't fix things. Disclaimer: I don't use Oracle a lot on that machine, and it possible that something other than the 8.1 upgrade broke it.

I uninstalled it (that would have been the 'repaired' install), blew away all the database files as I had nothing I wanted to keep, and re-installed (instructions above - my biscuit was a Tim Tam, but people in the UK may substitute a p-p-p-penguin). It seems to work fine now.

So Oracle 11g XE works on Windows 8.1, as far as I can see. Bear in mind that XE doesn't have any real support anyway, so the difference between a 'supported' and 'unsupported' configuration is purely imaginary. I don't do anything like RMAN backups and restores, let alone between OS upgrades. I can't see why they wouldn't work, but I'm not a DBA.

'Medalling' in Humour

Yesterday I competed, and came third, in the Humorous Speech contest of the Hawkesbury Division of Toastmasters. I'm pretty chuffed with that.

Partly, I'll blame Yuri for this. He recommended Toastmasters as a way of getting some extra public speaking skills. Luckily for me, there's a group that meets one lunchtime a week at work, which makes attendance relatively easy. Much easier than trying to fit in an evening on top of my kids' busy social and sporting calendars. I started going just before Christmas and became a paid up member in January this year.

Next, I'll blame Neil Sequeira who prompted me to enter the club contest a month ago on the basis that attendees regularly laughed at my speeches....in a nice way. I won that, and then it dawned on me that I'd be going into the Area level contest. Our club was hosting that event, so I had a slight 'home ground' advantage, but I was still in front of a bunch of people I didn't know, most of whom have been honing their speaking skills for YEARS.

I won that, which meant going up to the Division level contest last night. That was in a church hall, unfamiliar territory for me. We were speaking from an elevated stage, and with a headset microphone. Getting into the big leagues.

I was a bit ruffled because my trip there was delayed with my phone unaccountably deciingd it couldn't find any GPS signal, and refusing to tell me where I was or how to get where I was supposed to be. My destination was the other side of Middle Harbour so my regular tactic of pointing the car vaguely in the right direction and hoping for the best was foiled by its inability to fly across the water. Resorting to my trusty and dusty Gregory's Street Directory I made the 40 minute journey in a mere 80 minutes.

My speech starts with the other Gary Myers, multi-time champion of Summernats (which I've mentioned before in my blog ) and land speed record holder. Oh, and candidate for one of the NSW Senate spots for Federal parliament. He didn't win, but a candidate for the same party did get a spot for Victoria. I suspect this came as somewhat a surprise to him, as the Senate voting system is complex and highly unpredictable. An unemployed ex-sawmill worker who didn't own a suit and has kangaroo poo chucking as a hobby will be an interesting addition to the chamber.

I was more than  happy with my third place finish in the contest. The winner, who also took the Table Topics contest top prize, was excellent. And second place went to a Dave the Happy Singer who took the mick out of homoeopathy and similar rubbish, so I won't criticise that. I get a small trophy and a certificate. And an update to my LinkedIn profile.

And, for posterity, my certificates:

Tracking email receipts through images

I pinched a technique from EMail marketeers last week. It goes by the cool name of a web beacon or web bug and is used to detect when someone reads (or at least opens) an email.

Our application is like a work flow system and it sends out a whole bunch of emails. I'm a little sceptical about who reads them, especially since I've got a bunch of rules that automatically ignore about 20-30 emails a day.

For a new email alert to a distribution list, I decided to code in a beacon. 

First for each email sent, it grabs a unique identifier. You can use a plain sequence, but I opted for a GUID. It is harder for anyone to guess other values for GUIDs and it is easy for me to tell which ones come from the test environment and which ones from production.

    declare

      v_guid_vc varchar2(32);

    begin

      v_guid_vc := sys_guid();

Then, the email HTML body included an image link

v_body := v_body || 

  'img src="http://'||v_host||'/PKG_MAIL.image?i_val='||v_guid_vc||''' ';

These mails go out to internal users, and Outlook is the primary email tool. The technique doesn't work for users reading their mail through Outlook webmail or the iOS email. The host is behind our firewall and will be unreachable from a home email account. But our company standard Outlook config will happily try to render that image when the user opens the email at their desk. And when that happens, it tries to get the image content by calling PKG_MAIL.image and telling us the GUID we associated with that email.

When we get that image request, we record the GUID, timestamp, IP address, user agent and cookie data. If the user happens to be logged into our system at that time, the cookie will tell us who the reader is. If not, the IP address will give us a clue. [When someone logs into the application, we record the userid and IP address, so we can see if someone logged in from that address recently.] Once we've recorded that data, we return a standard image content, irrespective of the GUID passed in.

No one has to click any link for the picture rendering / email read to be logged.

Rather than sending one email to ten recipients (with the same GUID), you will be better off sending ten variations with their own GUIDs. If you are sending the email to a specific individual, you may not need that excess logging (unless you want to track forwarded emails too). 

Personal email clients, such as Thunderbird and GMail, tend not to open remote images by default, offering a vague "privacy" warning. This is what they are warning you about. It means that Dominos probably know that I read their email vouchers about 5 minutes before ordering a pizza. And they will know whether I am using Thunderbird or Chome, and a bunch of other stuff about me. But, cheaper pizza !

Session based sequences in 12c.

Catching up on some blogs, and I saw this 12c gem by Syed Jaffar Hussain.

Sequences are great. But sometimes they're just a little more unique than you actually need. Sort of like GUIDs. Start churning through them and they're quickly nine or ten digits. There's nothing wrong with long keys from a technical perspective as the extra byte or two of storage is rarely significant. But they can be greedy from a screen layout point of view (especially in tabular forms). And there's a greater chance of getting digits mixed up in email or conversations about them.

If you are doing a nightly or weekly load, it can be nice to load up your half-a-million rows with a 'batch id' and a 'sequence in batch'. Session based sequences are a nice concept that give you scope for keeping those values in a smaller range.

Another trick for reducing the size of IDs is to covert them into HEX. You'll probably only save one character there though, but the mix of alphabetic and numeric characters. TO_CHAR and TO_NUMBER are both happy to use 'XXXXXXXX' format masks to convert between decimal and hex.

WITH enhancements in 12c

There's been some mentions of this feature on Oracle-base and elsewhere, but here's an example of what excites me.

If you deal with nested arrays/tables in SQL, then you quickly bump into an impedance match. You can't readily get that embedded list into horizontal columns.

I'll use the example I'm most familiar with - 2-D geometry data types.

There's a concept called the MBR (minimum bounding rectangle). You can basically think of it as the most northerly, southerly, easterly and westerly points of an area.

select a.mbr from LGA_2012_AUST a  where lga_name12 = 'Darwin (C)';

MBR(SDO_GTYPE, SDO_SRID, SDO_POINT(X, Y, Z), SDO_ELEM_INFO, SDO_ORDINATES)
--------------------------------------------------------------------------------
SDO_GEOMETRY(2003, 8311, NULL, 
   SDO_ELEM_INFO_ARRAY(1, 1003, 3), 
   SDO_ORDINATE_ARRAY(130.815117, -12.469386, 130.938563, -12.33006))

The MBR is a geometry column with an ordinate array listing the four points.

We can do a Collection Unnesting operation, but then we end up with rows.

select b.*  from LGA_2012_AUST a, table(a.mbr.sdo_ordinates) b 
where lga_name12 = 'Darwin (C)';

COLUMN_VALUE
------------
  130.815117
  -12.469386
  130.938563
   -12.33006

To get back to columns, you'd need a PIVOT operation. But because we've lost any sense of the order of those values in the array, that has limitations. In Australia we can easily tell the difference between our latitudes and longitudes, but that isn't possible for many locations.

The WITH operator allows us to extract those array elements easily, and without recourse to a stored function. Though the PRAGMA UDF mention by Tim may mean I don't need to fret about the context switches of switching between SQL and PL/SQL as much.

SQL> l
  1  WITH
  2  FUNCTION ext_val (i_arr in MDSYS.SDO_ORDINATE_ARRAY, i_val in number) RETURN NUMBER IS
  3  BEGIN
  4    return i_arr(i_val);
  5  END;
  6  select ext_val(a.mbr.SDO_ORDINATES,1) v1, ext_val(a.mbr.SDO_ORDINATES,2) v2,
  7         ext_val(a.mbr.SDO_ORDINATES,3) v3, ext_val(a.mbr.SDO_ORDINATES,4) v4
  8  from LGA_2012_AUST a
  9* where lga_name12 = 'Darwin (C)'
SQL> /

        V1         V2         V3         V4
---------- ---------- ---------- ----------
130.815117 -12.469386 130.938563  -12.33006

My demo in SQL*Plus works fine in version 12c.
In the 11.2 Instant Client, I could get it to run using
set sqlterminator #

That stops it treating the ";" in line 4 as a terminator, and allows it to pull in the whole statement. The backslash will send it off to the DB for processing, and it works fine then

SQL Developer 4 also seems to choke on the syntax. I'll have to work up the strength to see if it is already logged as an issue. I assume it won't work in 3.2, and that a 12c rollout will require SQL*Plus and SQL Developer installs to be upgraded. Not sure about TOAD and any other clients.

The Adventures of the Trickster developer - Aliases

The Trickster is a mythological being who enjoys potentially dangerous counter-intuitive behaviour. You'll often find him deep within the source code of large systems.

The Alias Trap

Generally an alias in a query is there to make it easier to understand, either for the developer or the database. However the Trickster can reuse aliases within a query to make things more confusing.


desc scott.emp
       Name       Null?    Type
       ---------- -------- --------------------
1      EMPNO      NOT NULL NUMBER(4)
2      ENAME               VARCHAR2(10)
3      JOB                 VARCHAR2(9)
4      MGR                 NUMBER(4)
5      HIREDATE            DATE
6      SAL                 NUMBER(7,2)
7      COMM                NUMBER(7,2)
8      DEPTNO              NUMBER(2)

desc scott.salgrade
       Name      Null?    Type
       --------- -------- --------------------
1      GRADE              NUMBER
2      LOSAL              NUMBER
3      HISAL              NUMBER


desc scott.dept
       Name      Null?    Type
       --------- -------- --------------------
1      DEPTNO    NOT NULL NUMBER(2)
2      DNAME              VARCHAR2(14)
3      LOC                VARCHAR2(13)


The trickster can try queries such as

SELECT e.ename, e.grade
FROM scott.emp e 
       JOIN scott.salgrade e ON e.sal BETWEEN e.losal AND e.hisal;

and


SELECT x.ename, x.dname
from scott.emp x join scott.dept x using (deptno);

As long as any prefixed column names involved are unique to a table, the database can work out what to do. 

If you find this in a 'live' query, it is normally one with at least half a dozen tables where an extra join has been added without noticing that the alias is already in use. And you'll discover it when a column is added to one of those tables causing the database to throw up its hands in surrender. Sometimes you will find it in a dynamically constructed query, when it will fail seemingly at random. 

Once discovered, it isn't a mentally difficult bug to resolve. But first you have to get past the mental roadblock of "But all the columns already have an alias pointing to the table they come from".

SCNs and Timestamps

The function ORA_ROWSCN returns an SCN from a row (or more commonly the block, unless ROWDEPENDENCIES has been used).

select distinct ora_rowscn from PLAN_TABLE;

But unless you're a database, that SCN doesn't mean much. You can put things in some sort of order, but not much more.

Much better is

select sys.scn_to_timestamp(ora_rowscn) from PLAN_TABLE;

unless it gives you

ORA-08181: specified number is not a valid system change number

which is database-speak for "I can't remember exactly".

That's when you might be able to fall back on this, plugging the SCN in place of the **** :

select * from 

  (select first_time, first_change# curr_change, 

          lag(first_change#) over (order by first_change#) prev_change,

          lead(first_change#) over (order by first_change#) next_change

  FROM v$log_history)

where **** between curr_change and next_change

It won't be exact, and it doesn't stretch back forever. But it is better than nothing.

PS. This isn't a perfect way to find when a row was really inserted/updated. It is probably at the block level, and there's 'stuff' that can happen which doesn't actually change the row but might still reset the SCN. If you're looking for perfection, you at the wrong blog :)

An Oracle April Fools trick

If anyone is looking for a trick for April Fools' Day, try

alter session set nls_date_format = 'fm';

The result will be an simple TO_CHAR on a date, or implicit conversion of a date to a string, will return NULL. You could try that with an ALTER SYSTEM too.

If no-one notices then, "Congratulations", no-one is relying on the default date format.

Out with the old, in with the new

The good news is that I've successfully got my picture being flagged up with my blog articles in Google Search. 

The bad news is that sometimes (mostly ?) it has the picture from my deprecated 'domain' GPlus account rather than my primary one. I suspect I need to take more drastic steps to eliminate the old profile. 

Folks, we have an Image Problem

Firstly the Daily WTF
"The forums have strong feelings about Oracle. Not a single one of those feelings is positive." 
This was actually a post in their "I hate Oracle" forum so it may be a bit biased. But the fact that the Dailt WTF have opened a forum just for Oracle with that name, well I get the hint.


It doesn't help that the only time Oracle hits the big news is when there's another major hole in Java. It's the only installer that comes with a revolving door as standard. Oh, and the Ask.com toolbar being foisted on people. 

Then I saw the video linked on the highscalability blog . The whole relational / SQL database market has all the cool of, well, a Blackberry phone. It doesn't help that no-one can decide whether to pronounce it S-Q-L or see-quel.

While the video suggest a few fresh naming options, I think we need to be radical. ACIDbase recalls the core requirements of the relational model, suggests danger with a hint of rebellious substance abuse and coolly trips off the tongue. The only disadvantage is that the chemists seem to have wrapped up the best domain names. 

Now all we need is a fresh mascot. I suggest...Beaker

I'll have my people call his people....

Big BLOBs being greedy in TEMP tablespace

I work in a small team. There are just five of us at the 'coalface' for the application, responsible for both development and production. One of the five acts as the 'DBA/Sysadmin' (as well as doing programming and acting team as leader when the actual team leader is on leave and filling in a couple of other roles). And he was on leave on Friday when we got the plaintive email with the subject "The application is broken".

We could log in and out, so it wasn't 'catastrophic'. The application is mostly APEX, so my next step was to check the Apex activity log where I saw "ORA-01652: unable to extend temp segment by 128 in tablespace TEMP". The error was at least 'extreme'. 

Yes, a DBA probably would have gone to the alert.log first. I am not a DBA :)

Prompted by that message, I ran a query across V$TEMPSEG_USAGE to find several inactive sessions under APEX_PUBLIC_USER holding hundreds of MBs of temporary LOB segments. With a temp tablespace around 2GB, this level of usage isn't sustainable. The quick solution was just to shoot the sessions. 

APEX lies on top of HTTP, a stateless protocol and the session state is actually preserved in tables (check out Scott Wesley's post on viewing Apex session state). It generally isn't a problem if you kill one of the database sessions forming the connection pool and it isn't active.

On further investigation, we have identified one trouble spot, which is an application component that allows users to upload and download files. It seems that recently they have been doing large files (hundreds of MB). When the files are uploaded or download it allocates a temporary LOG segment and it holds onto the segment even after a commit or a DBMS_LOB.FREETEMPORARY or a reset package state. 

Those end users aren't getting the same database session each time oo if they do several file transfers, multiple sessions can be hanging onto these hundreds of MB of temporary segments. With around 20 sessions at peak, that was locking up our 2GB temp file.

As a stop-gap, we've introduced a scheduled job every three hours to kill off greedy sessions. It is an ugly solution though, and we'll be discussing options when we're all back at work after the long weekend. These include

• A support note that points to event 60025 to free up the stray LOB segments. 
• Tying the file upload/download component to one or two database sessions
• File size limits on uploads and downloads
• Is Apex the appropriate security gate-keeper to those fils
• Can we avoid dealing with the 'entire' file, and just have a few MBs at a time

If you have any other suggestions, feel free to add a comment.

Engaging with Google+

In the pub after the Sydney Oracle Meetup ('SQL Developer for DBAs, featuring a guest recorded appearance by the one and only +Jeff Smith ), Google+ got a mention. Okay, it was me who mentioned it, and it was closely followed by a remark by another attendee that he didn't know anyone else who used it.

It is getting some traction, apparently overtaking Twitter in active members ( I don't know how the figures are calculated. Twitter is more high profile still, possibly because practically every tweet is public).

Google+... grew in terms of active usage by 27% to 343m users to become the number 2 social platform. Interestingly for Google, YouTube (not previously tracked by us as a social platform) comes in at number 3....

But it is still a geeky place. The most popular communities are Space, Android and Photography. Photography is big in G+. Sport less so, with the Minecraft community being bigger than the 'F1 Racing' group (which is the biggest of any sport).

Google+ Tips and Suggestions

If you do want to get active in G+, finding appropriate communities is a good start.

Try Science Sunday or IT Professionals for active groups, or find something aligned to your non-work interests. There's no busy Oracle community yet. If you are an Oracle person, feel free to join the Sydney Oracle community (even if you don't live around here).

If you want a selected set of individuals to start with, I've shared a circle here. Some of them (Laurence Pegard and Artsaholic) are 'visual' posters with lots of imagery. Wired, The Economist and the Daily WTF will be known to anyone reading this. Frankly the comments on the Economist posts often fall into the drivel category.Tim Hall and Jeff Smith are included because they are the bigger Oracle 'identities'. David Brin is the sci-fi author, and the others represent good originators or curators of content.

I have one circle that is empty. I share articles to that if I want to read them later.

I have a circle for 'local' content (where it will be active during the daytime in Australia) and another for 'Following' a group of people who post stuff that I don't want to miss. I catch up on that group in the mornings (mostly) simply reading back to the last post I remember.

I have a 'Background' circle of stuff that I can read if I have time, but I can skip when bust. And I sometimes have 'potential' circles where I've imported a bunch of people and want to filter them into a better circle, or remove them altogether,

I generally remove (uncircle) people for posting lots of cat pictures or memes, or if they have a very high noise to signal ratio. I block people who irritate me. It makes life more pleasant.

If you comment on an article, you get notifications for subsequent comments (unless you mute the post). If you follow interesting people, you can often find other interesting people when they comment on interesting posts.

I don't expect people I follow to follow me back. I don't expect to find people I know in real-life.
Lots of people don't post publically. Mostly women, and mostly because of creeps. Because of this, you might need to wait until you are circled by them before you actually see any content from them.

Also, stick enough information in your profile so that people know 'what you are about'.

Greatest, Least and NULLs

The functions greatest and least are amongst the special ones which can take lots of arguments. They are a bit similar to min and max for columns rather than rows. But they have one difference to the aggregate functions that meant I avoided them for a long time.

Unlike aggregate functions, which ignore null values,  greatest and least will return a null if any of the supplied columns (or expressions) are null. If that isn't what you want (and it often isn't) it means putting NVLs around everything and with substitution values that don't break the logic.

For example, 


create table state_sum
  (style varchar2(10), 
  nsw number, vic number, wa number, qld number, 
  sa number, tas number, nt number, act number);
  
begin
  insert into state_sum values
    ('Red',5,3,9,null,1,7,8,null);
  insert into state_sum values
    ('Blue',null,null,null,null,null,null,null,null);
  insert into state_sum values
    ('Green',10,9,8,7,6,5,4,3);
  commit;
end;
/

If I try a 

SELECT least(nsw,vic,wa,qld) 

FROM state_sum 

WHERE style  = 'Red';

It will return a null (because there's a null value for the QLD column);

I could try

SELECT least(nvl(nsw,0),nvl(vic,0),nvl(wa,0),nvl(qld,0)) 

FROM state_sum 

WHERE style  = 'Red';

But that returns a 0 because that has been substituted for the QLD null. To get the lowest 'real' value, I'd need to NVL with a value so high that it could never be the lowest. A similar problem occurs for greatest except you can probably get away with a zero....up until you get a set of negative numbers. And don't even get me started on character and date values.

In short, I never found these functions to be as useful as intended.

But once in a while this apparent drawback in their management of null values does make them useful.

Coalesce, greatest/least and null tests

I have become a fan of coalesce as NVL on steroids. 

I never liked NVL2. It might be useful, but if you can't actually give it a decent name, then you shouldn't have bothered. Or maybe just wait till you invent the concept of overloading and simply have the existing two-parameter NVL and an additional three-parameter NVL instead of NVL2.

If you haven't used it, it returns the first no-null value in the list. And, like greatest/least, it can take lots of arguments.

SQL> select style from state_sum

  2  where coalesce(nsw,vic,wa,qld,sa,tas,nt,act) is null;

STYLE

----------

Blue

SQL> select style from state_sum

  2  where coalesce(nsw,vic,wa,qld,sa,tas,nt,act) is not null;

STYLE

----------

Red

Green

But what if you want to know if any of the values are null. That is, you want Red and Blue, but not Green (or vice versa).

Yep, you guessed it. That's where you can use  greatest/least.

SQL> select style from state_sum

  2  where least(nsw,vic,wa,qld,sa,tas,nt,act) is null;

STYLE

----------

Red

Blue

SQL> select style from state_sum

  2  where least(nsw,vic,wa,qld,sa,tas,nt,act) is not null;

STYLE

----------

Green

And it is easy enough to combine them 

SQL> select style,

  2         case when least(nsw,vic,wa,qld,sa,tas,nt,act) is null 

                 then 'Y' else 'N' end nulls_present,

  3         case when coalesce(nsw,vic,wa,qld,sa,tas,nt,act) is null 

                 then 'Y' else 'N' end all_null

  4  from state_sum;

STYLE      N A

---------- - -

Red        Y N

Blue       Y Y

Green      N N

Inskipp the uncatchable exception

The world is full of exceptions. And exception handlers.

And sometimes, just sometimes, it actually makes sense to have a WHEN OTHERS. For example, if a program fails, under any circumstances, maybe you want it to log the exception or send an email to a support account.

But then, just sometimes, you want a program to FAIL, and to fail in such a way that even the most resilient of exception handlers won't catch it. Maybe you're working on a function that gets called way down the bottom of a deep call stack, and right there at the top is a WHEN OTHERS exception handler you can't afford to trigger.

PL/SQL doesn't support classes or sets or exception handlers. But there's one exception that is superspecial. Okay, NO_DATA_FOUND is special, as I've discussed before, but this one is special in a different way.

This is exception -0028, licenced to die. (Actually, the double-zero prefix is unnecessary, but it is still Christmas holidays for me, so I'm sneaking in a Bond reference).

This is a regular exception.


SQL> declare
  2    e_suicide exception;
  3    pragma exception_init(e_suicide,-27);
  4  begin
  5    raise e_suicide;
  6  exception
  7    when others then
  8      dbms_output.put_line('Caught:'||sqlcode);
  9  end;
 10  /
Caught:-27

PL/SQL procedure successfully completed.

And this is the uncatchable exception (oh, and Inskipp the uncatchable was a character from the Stainless Steel Rat)

SQL> declare

  2    e_suicide exception;

  3    pragma exception_init(e_suicide,-28);

  4  begin

  5    raise e_suicide;

  6  exception

  7    when others then

  8      dbms_output.put_line('Caught:'||sqlcode);

  9  end;

 10  /

declare

*

ERROR at line 1:

ORA-00028: your session has been killed

ORA-06512: at line 5

When it says the session has been killed, it means it. It is dead. An ex-session. Not pining for a PGA, but deceased and bereft of state.

SQL> select 1 from dual;

select 1 from dual

*

ERROR at line 1:

ORA-01012: not logged on

This one will return control to the calling program and a new connection will need to be established to the database if desired.