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'.