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