Sydney Oracle Lab

SCNs and Timestamps

2013-05-16T21:05:00.000+10:00

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

2013-03-29T09:00:00.000+11:00

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

2013-02-05T17:50:00.000+11:00

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

2013-02-02T15:39:00.001+11:00

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

2013-01-28T04:00:00.000+11:00

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+

2013-01-27T11:48:00.000+11:00

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

2013-01-10T22:20:00.001+11:00

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

2013-01-05T13:26:00.000+11:00

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.

COLLECT and DISTINCT

2012-12-10T23:16:00.000+11:00

In regular SQL, you can use the DISTINCT qualifier with the COLLECT function.
Here it removes the duplicates for PETER and DAVID.

SQL> select cast(collect(first_name) as sys.dbms_debug_vc2coll)
2 from HR.employees
3 where job_id = 'SA_REP'
4 and manager_id in (147,145);

CAST(COLLECT(FIRST_NAME)ASSYS.DBMS_DEBUG_VC2COLL)
--------------------------------------------------------------------------------
DBMS_DEBUG_VC2COLL('Peter', 'David', 'Peter', 'Christopher', 'Nanette', 'Oliver'
, 'Clara', 'Danielle', 'Mattea', 'David', 'Sundar', 'Amit')

SQL>
SQL> select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)
2 from HR.employees
3 where job_id = 'SA_REP'
4 and manager_id in (147,145);

CAST(COLLECT(DISTINCTFIRST_NAME)ASSYS.DBMS_DEBUG_VC2COLL)
--------------------------------------------------------------------------------
DBMS_DEBUG_VC2COLL('Amit', 'Christopher', 'Clara', 'Danielle', 'David', 'Mattea'
, 'Nanette', 'Oliver', 'Peter', 'Sundar')

However, if you get into PL/SQL, things are different. Without the DISTINCT, it works fine.

SQL> declare
2 v_tab sys.dbms_debug_vc2coll;
3 begin
4 select cast(collect(first_name) as sys.dbms_debug_vc2coll)
5 into v_tab
6 from HR.employees
7 where job_id = 'SA_REP'
8 and manager_id in (147,145);
9 dbms_output.put_line(v_tab.count);
10 end;
11 /
12

With the DISTINCT an error is reported.

SQL> declare
2 v_tab sys.dbms_debug_vc2coll;
3 begin
4 select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)
5 into v_tab
6 from HR.employees
7 where job_id = 'SA_REP'
8 and manager_id in (147,145);
9 end;
10 /
select cast(collect(distinct first_name) as sys.dbms_debug_vc2coll)
*
ERROR at line 4:
ORA-06550: line 4, column 15:
PL/SQL: ORA-30482: DISTINCT option not allowed for this function
ORA-06550: line 4, column 3:
PL/SQL: SQL Statement ignored

That's the case in 11.2.0.2. I'm not sure if there's a patch for it, or whether it will be fixed in 12c. [And if they want to get that out in the twelfth month of 2012, they don't have long left.]

SQL Developer, COLLECT and CAST

2012-12-09T23:01:00.001+11:00

This one is a quick hint for SQL Developer and the COLLECT function.

In case you're not familiar with it, COLLECT is an aggregate function that was introduced in 10gR2 and simply gathers all the items up into a VARRAY/TABLE style collection type. If you use it in PL/SQL programs, you can handle the collection programmatically.

But sometimes it can be handy to see the elements in a quick ad-hoc query.

In SQL*Plus, you'll get an output that is ugly, but usable:

SQL> l
1 select country_id, collect(city)
2 from hr.locations
3* group by country_id
SQL> /

CO
--
COLLECT(CITY)
--------------------------------------------------------------------------------
AU
SYSTPv4AgebXuT9qJVHqM2Gc/MQ==('Sydney')

BR
SYSTPv4AgebXuT9qJVHqM2Gc/MQ==('Sao Paulo')

CA
SYSTPv4AgebXuT9qJVHqM2Gc/MQ==('Toronto', 'Whitehorse')

In SQL Developer, the same query will return you an error:

ORA-00932: inconsistent datatypes: expected NUMBER got -
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:

As described in the documentation for COLLECT, you should apply a CAST. If you don't already have a convenient TYPE that is a TABLE OF VARCHAR2, and can't create one, there's a built-in one you can use, SYS.DBMS_DEBUG_VC2COLL. Unusually for a SYS object, you do need to specify the schema name as there's no public synonym.

select country_id, cast(collect(city) as sys.dbms_debug_vc2coll) cities
from hr.locations
group by country_id

I'm mostly using 11gR2 now, which has the more powerful LISTAGG, but if you still haven't upgraded all your databases, ad-hoc COLLECTs can be useful. And in programs, a collection is easier to parse than a string.

Toastmasters - Testing public speaking away from 'production'

2012-12-01T16:18:00.000+11:00

Every Wednesday lunch (pretty much) there's a Toastmasters meeting at the company where I'm working. After having been there a year, I finally got around to turning up a couple of weeks ago. Hey, they also have live music on Wednesday lunch. It's a busy place.

Yury, from Pythian and the Sydney Oracle Meetup, has mentioned Toastmasters. Given my audience is mostly developers, let me sum it up my way: Toastmasters is a test environment for public speaking.

As with a test environment, you're expected to have a few "does not meet requirements" incidents. That's why it is there. You can experience those, and learn from them, and repeat with improvements. And none of that damages "production". It is a dedicated, safe environment for rehearsals.

I can't speak for all groups, but in the couple of meetings I have attended, as a guest, I've seen positive and constructive criticism. As well as learning to speak, others are evaluating...which means listening. The talks are only a few minutes, so I don't bore people with techy details. There's no sign of Powerpoint either, so it isn't quite a conference rehearsal.

Again, like testing, this means you are using 'test' data, rather than volume testing. You can focus on the fundamentals of communicating rather than on content.

I plan to sign up fully in the near future, so there might be more coming in this blog, or it might come through in my Google Plus posts (apparently I am in the circles of over a thousand people, but I'm none the wiser on how many actually read anything I post).

Windows 8 running Oracle 11g XE

2012-11-11T14:28:00.003+11:00

It's been several months since my last blog post, mostly because free time is a scare resource.

But after a few false starts, I've removed the last vestige of "Windows XP" from my home, and upgraded my netbook to Windows 8. Windows 7 was never an option for this machine and Windows 8 has an afforable XP upgrade option and a simpler set of variants. The "Pro" version which has replaced my "XP Home" adds such goodies as acting as a Remote Desktop server, which was always skipped for 'home' users. I feel all grown up :)

The Jumps

The first false start was the Windows 8 upgrade assistant which fell over several times. Since it won't let you go to the 'Buy' component without going through this step, it was a significant obstacle. I uninstalled a bunch of stuff and then created a fresh administrator account. One of those fixed the problem and it informed me of several minor issues but no showstopper.

The analysis did suggest that the Oracle 11gR2 XE would not necessarily need re-installing, but I can't be sure if that was true.

The 'Buy' was the second barrier, with repeated failures when trying to produce an invoice. None of the unsuccessful attempts reached my credit card, and it eventually succeeded. I think I needed to wait until the US and/or Europe was asleep before the server could cope.

The final blocker was a failed attempt to upgrade and keep 'Personal documents'. In my position, computers tend to get a lot of stuff installed and uninstalled on them. After a couple of years, I think the XP environment was just too stuffed to manage an upgrade.

Success

I decided to backup a bunch of stuff and did a 'kill all' install which went all the way through, and didn't actually wipe out the 'D:' partition of my disk anyway.

The 1024x600 resolution of the built-in screen means the "Don't call it metro" apps / Windows Store is unavailable. I might look into solutions to that, but it isn't a big deal.

Reinstall

Now I needed to regather all my favorite applications, and see if the VPN option to work will still run.

Google Chrome was the first application installed. I will try IE10, but I think the clutter of the default home screen for Australian users (ninemsn.com) made me flinch.

Oracle XE 11g has been re-installed and I'm downloading the latest version of SQL Developer which Jeff has had updated again.

MobaXTerm has apparently jumped to version 6 while I blinked.

PSPad has been at version 4.5.6 for a year.

Greenshot is my favoured screen capture utility

VLC Media player has been added too.

Haven't decided whether to aim for OpenOffice or LibreOffice, and I suspect there'll be a few more applications I remember over the coming weeks.

Being just a little bit unique

2012-07-17T21:12:00.000+10:00

A few weeks back, I saw a mention of an old Tanel Poder post about the uniqueness of ROWIDs.

This is an interesting subject as a lot of people expect ROWIDs to be unique, but don't think about how unique that is. While you can't be a little bit pregnant, you can be a little bit unique. Or at least unique only within a specific subgroup.

The only complaint I have about the piece is that it is very DBAish. Lots of moving files around the OS and all those other things that DBAs like to do.

I'm a developer, and like to stick with SQL. And it is pretty easy to demonstrate the non-uniqueness of a ROWID without leaving SQL*PLUS.

SQL> CREATE CLUSTER emp_dept
2 (dept_id NUMBER(4));

SQL> CREATE TABLE dept_c
2 (dept_id number(4) primary key, dept_name varchar2(20))
3 CLUSTER emp_dept (dept_id)
4 ;

SQL> CREATE TABLE emp_c
2 (emp_id number primary key, name varchar(20), dept_id number(4))
3 CLUSTER emp_dept (dept_id)
4 ;

SQL> CREATE INDEX idx_emp_dept ON CLUSTER emp_dept;

SQL> insert into dept_c values (10,'Accounts');

SQL> insert into emp_c values (1,'Scott',10);

SQL> select rowid from dept_c
2 union
3 select rowid from emp_c;

ROWID
------------------
AAAF/4AAEAAACX9AAA

SQL> select rid, count(*), min(type), max(type)
2 from
3 (select 'D' type, rowidtochar(rowid) rid from dept_c
4 union all
5 select 'C' type, rowidtochar(rowid) rid from emp_c)
6 group by rid;

RID COUNT(*) M M
------------------ ---------- - -
AAAF/4AAEAAACX9AAA 2 C D

Of course a DBA will still argue that the ROWID is unique and this one simply doesn't belong to a table.

But the takeaway is that a ROWID is not tied to a single row in a single table in a database.

Whatever happened to...Packaged Apex Applications

2012-07-07T20:17:00.000+10:00

Many moons ago, there existed a bunch of packaged applications for Apex on the Oracle website.

Alas and alack, they were removed and all that exists in their place is a collection of sample code. They'll be making a comeback in Apex 4.2 where they will be 'prepacked' in the installation and ready to install. You can keep them vanilla, and perhaps get some support from Oracle for them (or maybe just an upgrade path), or you can unlock them and make your own customisations and enhancements.

These packaged productivity apps also got mentioned as part of the cloud.oracle.com offering. Assuming that does eventually materialize. Maybe its been waiting for Apex 4.2. (By the way, the EA is running on 11gR2 so there's no 12c hiding there.)

The only drawback is that the new packaged applications won't be available for anything earlier than 4.2. I guess it is possible for someone to unlock them and somehow achieve a backport to an earlier version, but I wouldn't count on it. Certainly don't expect that from Oracle.

Anyway, for a bit of nostalgia, I dug out one of those old packaged apps from my hard drive. The original Issue Tracker was developed for Apex 2.2 and is old enough to be attending school. The new one looks a lot more attractive. Since it caters for features such as uploading attachments, it looks much more like a usable application than a sample or demo.

Chrome, Firefox and chunky padlocks

2012-07-02T21:09:00.002+10:00

For sometime now I have been using Chrome as my preferred browser. Once upon a time it was Firefox. But there's a few things about Firefox I dislike and this is one of them.

Some site, such as the New York Times, have a half-hearted implementation of HTTPS. I generally see this when someone who uses a tool like "HTTPS Everywhere" posts a link. The problem is that Firefox doesn't make it REALLY obvious that the https is a bit broken.

You see the regular icon, and if you bother to click on it, you get a little warning.

Now, compare that to Chrome, which gives you a big chunky warning you have to accept.

And if you do accept it, you get a padlock with an 'x' on it, and a line drawn through the https text. This makes it obvious that it is broken.

When the https is okay, Firefox does give you the hostname but it isn't that great. Again, clicking gives you a bit more info.

Chrome gives you the padlock icon and a whole bunch of techie details are just a click away..

Ever increasing priorities

2012-04-29T10:29:00.000+10:00

Inspired by a recent Martin Widlake post, I also have something to say about urgency.

In most problem/incident management systems there is some 'priority' level. And all the ones I've met, count it the wrong way, by starting with "1" as the highest priority.

The reason this is wrong is that, as anyone who has worked on the receiving end of such a system, there is always something that will trump the current highest priority.

"1" should refer to the lowest priority item. A typo in the help text (or the use of the phrase 'most unique'). The items that will never actually get fixed, but you record in the system just to let everyone know you are aware of the issue.

Then you work your way up to level 2 where the work might one day get done by some new guy as part of a learning exercise, but where it isn't something that is affecting the business in any material way. Actually a lot of issues at this level are trivial to fix, so if someone needs to 'get the numbers down' this is the place to target.

Level 3 should be the 'regular' work. In theory. In practice, you'll find most stuff at this level is actually level 2 items that have been pushed up because someone wants to see some progress. But also the hard stuff tends to sit here a long time as people argue over solutions.

My level 4 would correspond to what most systems class as urgent. The business expects all issues at this level to be actively being worked on by someone. If you have 20 issues at this level and 5 team members, this won't actually be the case though.

This is where corporate politics gets involved, and who has the biggest influence. And this is why I recommend counting from the bottom up. Because as people start pushing their issues up, there is unlimited room for the priority levels to be increased. Levels 5 and 6 will quickly come into play. After a few months, someone will find a need for level 7. When people find out there is a level 7, the issues at levels 5 and 6 will be pushed up the rankings.

Don't settle for single digits though. You'll hit 9 within a year and then you'll need a high priority issue to make enough room for a level 10.

What are these dollar signs doing in my code ?

2012-02-26T11:41:00.001+11:00

I've had the good fortune, in my current role, to be doing a fair amount of PL/SQL development in 11gR2. Boy, it feels good to be actually using up-to-date versions. Being picky, we are on Apex 4.0, rather than 4.1 but that's not a biggie.

One item I've found myself using more and more are the $$PLSQL_UNIT and $$PLSQL_LINE inquiry directives. I think they are great for logging.

Here's a handy example

create or replace procedure fizz_buzz as
begin
for i in 1..15 loop
if i/2 = trunc(i/2) then
if i/3 = trunc(i/3) then
dbms_output.put_line($$plsql_unit||'('||$$plsql_line||') - Fizz Buzz');
else
dbms_output.put_line($$plsql_unit||'('||$$plsql_line||') - Fizz');
end if;
elsif i/3 = trunc(i/3) then
dbms_output.put_line($$plsql_unit||'('||$$plsql_line||') - Buzz');
else
dbms_output.put_line($$plsql_unit||'('||$$plsql_line||') - '||i);
end if;
end loop;
end fizz_buzz;
/

When executed, you get a "poor man's trace" of the path the process takes through the logic.

SQL> exec fizz_buzz
FIZZ_BUZZ(13) - 1
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(11) - Buzz
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(13) - 5
FIZZ_BUZZ(6) - Fizz Buzz
FIZZ_BUZZ(13) - 7
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(11) - Buzz
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(13) - 11
FIZZ_BUZZ(6) - Fizz Buzz
FIZZ_BUZZ(13) - 13
FIZZ_BUZZ(8) - Fizz
FIZZ_BUZZ(11) - Buzz

By automatically substituting in the actual program name (eg the package name) and the line, you don't have to worry that refactoring the code is going to mess up any debug or logging logic. Refactoring is a fancy name for taking chunks of code and moving them into specialised procedures and functions so you can reuse them. Most developers do refactoring but may not know the name of it, or even that it has a name. Mostly you only need to know the buzzwords at interviews.

So that is what those $$ directives are doing in my code.

Why I don't (always) care if you follow me....

2012-02-10T23:30:00.000+11:00

This one is inspired by Jeff Smith's (yes, That Jeff Smith) post "Why I don't follow you" post.

I don't care if you follow me.

Okay, that's not completely true. I hope you didn't stop reading then. What I really meant was that I don't expect all the people that I follow to follow me back.

Here's why.

I read a lot of blogs and articles to learn stuff I don't know about. I'll subscribe to RSS feeds and add people to my Google Plus circles if they write or link to 'quality' material. By quality, I mean stuff that I find interesting or useful.

I write stuff here (occasionally, and will do an Oracle based one in the next day or too) and link to articles through Google plus. These are things that I think / hope other people may find interesting or useful.

So I follow some people and some people follow me. Inevitably there will be some overlap when people are interested in the same topics. But there are some people who know far more about a topic than I do and won't learn anything new from my posts. Or they post on a topic which I 'consume but don't publish'.

So I'm not offended if you don't follow me. Please don't be offended if I don't follow you.

PS.
Ongoing frustrations with Twitter mean that I've mostly replaced it by Google Plus.
Contrary to what I've said here, if you circle me there and have something like 'DBA' in your profile, I will circle you back because I have a special circle for DBAs. As did Dante, I believe.

I'm very flattered to be followed on Twitter, but I grew fed up of trying to twist sentences into something that is accurate and useful but still brief.

Crazy Crossovers

2012-01-08T16:55:00.000+11:00

Some months back, when it was in a 99c special offer, I added "Pride and Prejudice and Zombies" to my book collection. If you haven't heard of it, Seth Grahame-Smith took most of Jane Austen's work (by now in the public domain) and added a zombie subplot.

Then about a month back, one of Google Plus's more popular contributors,Tessie L'Amour, offered up the option of personalizing her (NSFW) books so that the purchaser could pick the names of the protagonists. That idea isn't new as I recall my parent getting me a personalised book when I was young. It involved a wizard who enjoyed peanut butter and had a magic sunflower. See, these things can stick with you !

Wanting to experiment with some Google Apps Mail Merging, I borrowed both concepts. Following the guide, what came out was a web-form where you can enter the desired first and last names, plus an email address, and you get a personalised PDF of the first of the Sherlock Holmes short stories, "A Scandal in Bohemia". A google search quickly found similar personalisation of other classic works.

This is the story where Sherlock meets the only woman he truly respects. The personalisation relates to the name of the woman (Irene Adler in the original), rather than Sherlock or Watson. I'd prefer an actual ebook format, but that looks a lot trickier. I've settled for sizing the PDF as A5 which makes it readable on my Kindle.

I haven't (yet) included the original Sidney Paget pictures, though I suspect they will also be in the public domain by now. That may happen in future, but work beckons for tomorrow.

Playing with Google Plus

2012-01-04T20:29:00.000+11:00

It's a Christmas break for me and I've been reviewing my social networking commitments.

I started by thinning RSS Feeds and Twitter load. My main problem with Twitter was a lot of my subscriptions were US based and the timezone meant a poor fit for me. The feed often overflowed overnight, and relatively little would come up during the day.

I'm shifting more to Google Plus. My initial experience echoes the "Quiet here, isn't it" which I saw mentioned a lot in the media. Luckily after persisting for a few days, I found some shared circles to import, and later someone else shared a circle with me of 500 Aussies.
This is the first failing of Google Plus - unless you are following someone when they share a circle, you will miss it. That makes it hard to find a decent starting point.

I have found Google Plus much more like Twitter than Facebook. If Facebook friends are people I'd invite over for dinner, Twitter and Google Plus contacts are a wider group of people I'd chat with in a restaurant or a pub.

Circles are a useful way to group people to read, similar to Twitter lists. For example, I have circles of writers, of Australians, of techies etc. They can be useful in distributing some material to a limited audience, but I'd recommend posting most things to Public. There are items I'll only post to my Australian or Techie circles if I think it would 'pollute' my public flow with information that most people would find irrelevant.

If there are some people I want to hear everything from, I put them in a specific circle and I can read everything in that circle. 'Noisy' individuals will go into a circle where I don't mind missing posts.

Google Plus has a couple advantages over Twitter.

Firstly, comments are attached to a post which allows for a conversation to develop. I don't find Twitter conversations as usable (but maybe that's my client). In Twitter, I get a few disjointed messages if I don't follow everyone who is chatting, but I can see everyone who comments on a Plus post. Another failing of Google Plus is that when you re-share another person's post, it is hard for the recipient to get back to the original. As such, they'll tend to comment on the shared post not the original.

By the way, when you comment on a post, you can't see who the post was shared with. That means you don't know who can see your comment. When in doubt, assume it is visible to everyone, including your boss, wife and mother.

Secondly, there's no 140 character limit. The posts can be fuller and include images and video. With Twitter, sometimes I get redirected through half a dozen URL shorteners to see a cartoon I read through someone else the day before.

My gut feel is that Twitter has more to worry about from Google Plus than Facebook.

Migration from Twitter has few barriers. It wouldn't be hard for a client to integrate tweets into a Google Plus stream. It will be easy to pull out a list of who an individual follows for that integration. It will also be pretty easy to find accounts on Google Plus corresponding to those twitter accounts.
Tweets are ephemeral so no-one expects to maintain history.
Twitter doesn't have features likes games that contribute to the stickiness of Facebook.
The content will be that bit richer on Google Plus than the forced brevity of Twitter.

Pay a few of the high-profile Tweeters to shift over, and you'll get some major traction. The biggest barrier is probably the 18 age limit on Google Plus, so Twitter might still maintain a place as a 'kiddie channel'.

Personally, I expect to abandon Twitter in the next 12 months.

The Minus about Plus

There's still a lot that is broken in Google Plus.

I had a couple of fireworks photos on my phone from New Year's Eve and Android's "Instant Upload" took at least a day and a half.
Sometimes retrieving posts suffers from irritating lags. My guess would be that, even for Google, they still need to work on scaling.
Saying the API is half done would be generous. It doesn't allow posting so that means there are no practical third-party clients, and there's no way of automating posts (eg from an RSS feed). They shouldn't fix that until after they can guarantee the scaling.
I'm still managing a primary and 'Google Apps' account because the merging is yet to materialize. I've also got a separate page for SydOracle which I will have to think about.
Managing circle membership is cumbersome. It would be nice to have the ability to make some circles as public/shared and for them to show up on a profile.
To a degree, it is still 'empty'. If your interest if photography or Android, you will be spoilt for choice. Other interests or hobbies may be less well represented.

Suggestions

If you are going to join Google Plus (or get deeper into it) I recommend the following :

Share or post a bunch of stuff over a week or so, and make it all Public. That way anyone looking at your posts will get some idea of who you are, what you are interested in etc. Sharing stuff through Google Reader works well for me. Sharing from the browser on my Android phone is less pretty.
Then build up a LONG profile. You can search profiles for key words (or twitter handles etc) so make sure that anyone looking for you can find you.

When you put someone in your circles, they may put you in theirs. If they push some material only to particular circles, you want them to easily work out which of those might be appropriate for you.

Do some searches on things that interest you. If you find posts that interest you, add the author into an appropriate READ circle. See who they follow, and you may find some more people to add.
Read up on this for finding people.
If someone you have circled seems well-integrated, and your profile has been sufficiently engaging that they have added you, then you can send them the equivalent of a direct-message. Ask them if they have a circle of similar people they can share with you.

Newsflash - the replacement for the Oracle Support HTML interface will be....HTML

2011-12-29T20:14:00.003+11:00

Like many other Oracle peeps, I got an email telling me that the current Flash and HTML interfaces for My Oracle Support were to be replaced by a new snazzy ADF based UI.

What has irked me is the way that some people have implied that the new interface won't be HTML.One example is the Oracle Infogram ("There's a new version of MOS in the works, not Flash and not HTML"), though I've seen similar (especially on Twitter which is admittedly constrained by the 140-character limit).

Let's clarify this. Browsers mostly render HTML (though they deal with straight text, images, maybe video and some other stuff). Through plugins, they can do Flash or Java.

But the new Oracle UI will be HTML. HTML spewed by a bunch of App Servers running Java and ADF, true, but still HTML. Going further, if you actually do a 'View Source' in the current HTML support application, right at the top it has meta name="generator" content="Oracle ADF Faces"

I don't know how much, if any, of the ADF layer of the current HTML application is being re-used for the new UI. They may well have done a complete throw-away and rewrite. But it isn't a novel technology stack being used here.

Maybe this is nit-picky. I just had a horrible feeling that there might be some people out there in Oracle land (and, if I'm being blunt, I don't mean developers) who don't realise that Oracle Forms stuff is an exception and that the main web development paradigm consists of mid-tier code generating HTML (probably with lots of javascript too, but that's a separate issue).

DBMS_METADATA and hashed passwords

2011-11-26T10:38:00.001+11:00

In the wonderfully named "ORAganism" blog (don't try oragasm - it is used for something QUITE different) Neil Johnson remarks on the privilege requirements for DBMS_METADATA.

I like DBMS_METADATA. In the old days, you had scripts that tried to re-engineer CREATE scripts from DBA_TABLES and so on. They were complicated and worked in most situations. DBMS_METADATA is much more powerful.

One feature worth noting about DBMS_METADATA is that it can also extract details about users which can be handy in the post 11g world of Oracle.

In 11g the PASSWORD column in the DBA_USERS returns null, rather than hashed version of the user's password as it did in 10g and earlier. That made it a lot harder to extract those password hashes from the database. There is some underlying SYS object that shows the hashes, but SYS objects are really tricky to access.

DBMS_METADATA gives a backdoor to that information. Not to everyone, but it should be usable by 'regular' DBAs without jumping through hoops.

select dbms_metadata.get_ddl('USER','GARY') a from dual;

CREATE USER "GARY" IDENTIFIED BY VALUES 'S:8A9EA7B75F8899D7163336AD9D29F9019C0361518594E6984E1EF1C4EDB8;34BCEDBA9E0AB83F'
DEFAULT TABLESPACE "APEX_4875120311438442"
TEMPORARY TABLESPACE "TEMP"

Before you try to crack my password, don't bother. In my little home environment, it is the same as the username.

Wish List IV - Read only and mean it

2011-11-18T19:00:00.000+11:00

Did you know that the SELECT privilege actually allows you to do a SELECT ... FOR UPDATE ? Yes, that means a user with just that privilege could lock an entire table.

I want a SELECT_NOLOCK privilege. The user can still do a SELECT, but they cannot have a lock on the table. Okay, while an SQL is executing, I guess that get the shared table lock. But that's it.

Wish List IV - The Whole Truth

2011-11-17T19:00:00.000+11:00

This is another little annoyance that, I hope, would be a simple change.

I want a column in USER_TAB_COLUMNS that tells me whether the object is a table or a view. Surely linking off to USER_TABLES to exclude views is an unnecessary burden.

Wish List III - The Sequence Cometh

2011-11-16T19:00:00.000+11:00

This is another of those 'chores' for which every DBA and his dog has a script.

Copy a new version of a table from Test to Dev and then you have to recreate or adjust the associated sequence. My proposed syntax would be

ALTER SEQUENCE blah_seq SYNCHRONIZE WITH blah_table (blah_column);

That would lock the table, get the highest value of 'blah_column' and reset the 'last_number' of the sequence to that value. Even better, retain the fact that the sequence has been synchronized with that table/column so there is some metadata about the relationship.