Thursday 29 March 2012

Thursday Thought - Technical Omniscience

These days when I friends/family ask me for technical support, two things cross my mind
- Tim Hall
- My car mechanic analogy

To elaborate on the car mechanic thing (a story of which my wife is sick of), I propose someone might take their car to a panel beater and say "it's not working, can you fix it?"

By all appearances, a car is a car is a car. Sure it might have a few dents or scratches - but until you see/hear/smell it driving down the road you have no idea what might be wrong with it. And a panel beater might have enthusiasm for cars, but have no idea how to diagnose, let alone replace, say, a cracked head gasket.

Now I'm all up for helping people out where I can, but I'm frank and say while I've been playing/working with computers since I was 10, I don't know everything and I'm actually a database technologist for commercial applications.

So when someone says:
my pc has been crashing alot lately out of the blue. A message comes up with 'the system has recovered from a serious error'
I ask a plethora of questions.

I felt the urge to share some of my questions & responses. Partially because this is a blog and it's what you do, and I also thought I'd put it out there to see if others geeks have any thoughts on how accurate some of my responses and interpretations were. I then had a little rant about the personal computing world as it is today where I made more generalisations, accurate or otherwise.

the question, their response, my feedback
  • how old is your pc? About 7 years whoa, until I saw you replaced hard drive, double whoa! 
  • have you replaced any components? new hard drive and have upgraded to its fullest with ramHard drive is the #1 component by far to fail. That doesn't mean that other components in a 7 year old box aren't immune. They're manufactured to a general shelf life. In my experience, the blue screen of death (bsod) is often related to dodgy hardware.
  • installed any new software recently? I don’t think so just looking for change
  • been careful with the email attachments you download? Always, only from who i know even then you can't be sure, always check extensions and addresses. Check this out first two sections (phishing, malware) very layman, good guides from google
  • do you get the "blue screen of death" before it resets and gives you that error? yes just making sure you got bsod
  • what does the error look like? Like the screen above and then just crashed and resets.  Get a message to email Microsoft when it reboots but never do hmm, here is where I don't have the skills to go much further, especially without seeing it. Apparently it's easy to do that remotely now, but there's so many things to keep up with! sorry!
sometimes the pc has been running really really slow so I defrag and virus scan and then it seems to be okay.  Hav ehad to do this twice in about 4 months

Long story - I've still got a tower I bought back in '05, somethings just gone wrong with it, too. I've got a external hard drive case on it's way from ebay, but my recent backup would have got most of my stuff anyway I'd say.   More of an inconvenience, especially with my ipod synced to that itunes.
Anyway, it takes a long time to do anything major (or minor), and it can only do one thing at a time. If that's you now, you've just got a second reason to upgrade. 
Defrag helps, but mainly if your moving files around a lot - creating content can contribute. Give windows time it will get lost in a paper bag.
Viruses these days come from e-mails, not floppy disks. Some through dodgy websites, but you gotta be wondering in bad neighbourhoods for that. Most concentration is on denial of service at a corporate level, and events like stuxnet
Systems like windows bloat over time. Add to that, you may have so many folders with so many items for your work, it has trouble with that, too. Especially if you have lots (thousands) of files in specific folders.
But they mainly bloat, get old, wear out, go slower than they used to. Windows. I put a different operating system on my old tower to try out - ran so much more responsive than windows - on the same pc! 
And as they bloat, they accumulate software, slowly. Patches, updates, new utilities that written to perform with today's pc's, nowhere past the pace of computer progress (double speed every 18 months - "moore's law"). 
Same goes for these new smartphones. When I replaced my iPhone with the Samsung, it felt so much faster in comparison. 2 years on, the extra lag reminds me of the 2y/o iPhone.

I know I went from faults to speed - but does your computer treat you well? Extra ram only goes so far.  
I don't know how to track down general computer faults, I generally look for changes and track backwards from there, uninstalling or whatever.
on a side note, the next generation of hard drives (SSD) have no moving parts. They're still fairly expensive, but becoming common-place in laptops. Hybrid models are really good.

No moving parts, less likely to break down. But they do have a life when it comes to read/write - but it's fairly long.

The conversation tapered off from there.

In short, no, I don't really know how to make your computer work again, but I still have an interest and general understanding of how computers tick.

Just because I work with computers, doesn't mean I'm technically omniscient and know how to fix every laptop, printer, fax, phone, watch, elevator, EEG, microcomputer...

Ask me about Oracle. Even then I probably only retain a small fraction of the body of knowledge that's out there, even on something focused like PL/SQL or Apex. With this topics, however, I do know where to start looking and how to start diagnosing.

Thought rant over & out.

To make a title about recursion, you've got to understand recursive titles.

Recursion, that's right, recursion, that's right, recursion. Best I stop that...

This screen shot an example showing one of Google's easter eggs that I found amusing.

In honour of creating a subtle one myself by accident recently, and the inspiration for me to add it as an option in a PL/SQL Challenge quiz question I submitted. If you're not playing, get in there, learn & have fun - who doesn't like quizzes!

The question submission stemmed from testing out a idea from reading Gary's blog post, which I found interesting, concise and thought provoking. Just something simple involving compiler directives.

For those chasing a simple example of recursion, this would create an infinite loop if executed in Oracle.
create function recursive(pc varchar2) 
return varchar2 is
  return recursive(pc);

select recursive('me') from dual;

Wednesday 28 March 2012

Changing Oracle APEX calendar icon

Sometimes I stumble across little features that after a moment I realise - hey, that wasn't in the last version. It's good to see the APEX team tackling little improvements as well as the big ones, ever maturing product.

Then I stumble on something that I discover is not a new feature after all, just one I never really paid attention to.

When editing a theme, you can select a different icon to use as the date picker.

So instead of the elegant default:

you could use the ugly retro looking #IMAGE_PREFIX#date.gif

And the reason why I know it's from 3.x, Paulo Vale picked it up in green 2008.

Thursday 22 March 2012

The most astounding fact

Astrophysicist Dr. Neil DeGrasse Tyson (21st century Carl Sagan) was asked by a reader of TIME magazine, "What is the most astounding fact you can share with us about the Universe?" This is his answer

Enough said, watch in wonder!

ps - love the martian sunset finale

Wednesday 21 March 2012

AUSOUG Perth Event in March

Attention AUSOUG members - come by for a nice breakfast and listen to Sydney based Pythian DBA & Oracle Ace Yuri Velikanov!

If you're on our mailing list, you check your e-mail - if not, please contact us about joining, particularly if you think your corporation has group membership.

Where: Oracle Australia: Level 2, 66 Kings Park Road, West Perth

When: Wednesday, March 28, 2012. Breakfast starts 7:30am. Connor will speak from 8-9am, question time afterwards for those who can stick around.

RSVP: By RSVP Sunday 26th February 5pm by emailing

Cost: Free to all AUSOUG members. Ticket parking on Walker St, just off Kings Park Road, or catch the Red CAT, with a short walk from corner of Outram/Ord.

Yury will spend around 30 minutes each on the following topics:

Single Client Access Name is a concept that makes database deployment easy on Oracle Database 11g R2 grids and complete the level of abstraction from the application perspective. Starting with the understanding of why this component is vital in the 11g grid infrastructure, this presentation walks through the main concepts of SCAN. You will learn how to plan and implement SCAN, what areas to be careful with, and how to monitor SCAN infrastructure and make sure it works as expected.
Yuri is called to audit RMAN backup scripts on regular basis for several years now as part of his Day to Day duties. He see the same errors in scripts that Oracle DBAs using to backup critical databases over and over again. Those errors may play a significant role in a recovery process when you working under stress. During that presentation you will be introduced to typical issues and hints how to address those. The author will give away a prize to any Oracle DBA who has a script without any issues mentioned in the presentation.

Yury's Bio:
Yury is a nice person who enjoys meeting and working with people on challenging projects in the Oracle space. He started to work as an Oracle DBA 15 years ago (1997). Yury is an OCP 7,8,9,10g,11g and 9i,10g,11g OCM (Oracle Certified Master). He is a frequent presenter at Oracle related conferences such as Oracle OpenWorld, Collaborate, Hotsos, UKOUG and AUOUG. Yury is a socially active person. Apart from Social Media (Twitter, Blogging, Facebook) he has contributed a lot to the local Oracle community over the years.

Find his blog or give him a tweet

I hope to see you there,


ps - Penny from Sage Computing is still offering to coach anyone with a technical or Oracle product related background with a view to presenting to an audience eg an Oracle User Group monthly meeting or at the Oracle conference.

This is a fantastic opportunity open to anyone wishing increase their profile within the Oracle community.  Just think, the call for papers for the Insync conference has just opened, and the Perth conference call won't be far away.

pps - Are you a student? Are you interested in getting involved and making more contacts in the Oracle community?

The Australian Oracle User Group are proud to announce free student membership for 12 months subject to the student undertaking volunteer work.

The WA student campaign will be managed by Ursula Rogaunig (contact: for details ) and will have a duration from now until the WA conference in October 2012.

Get involved!

Local DB died, local developer doesn't panic

My database crashed on my dad's birthday.

There's no correlation to be made there, let alone causation.
“The invalid assumption that correlation implies cause is probably among the two or three most serious and common errors of human reasoning.” Steven J Gould.
And if you haven't read it, Tom Kyte has a great Oracle related article on the matter. His old AskTom link is dead, but here is one from Oracle China - I think the transmission is by carrier pigeon, or a 9000 baud modem, but give it a few minutes. Similar to opening a sensis website, really - except it's all text.

Not a DBA!!!
Now my DBA hat is really small. I can't open this like that jeff smith fellow.

Nope, the first thing I did was informed my boss that my laptop blue screened - first time on this sucker, not long after I noticed Glassfish gone and the Oracle DB simmering on the ashes.

After a quick layman's look following a hunch, I found my SYSTEM tablespace was lacking elbow room.

I thought I'd lost the original figures in another crash (opening the lid from hibernation), but I found them in my e-mail to Penny.

SQL> @free sys%

NAME       KBytes         Used         Free   Used      Largest
----------------- ------------ ------------ ------ ------------
SYSAUX    768,000      630,848      137,152   82.1       40,960
SYSTEM  1,249,280    1,244,032        5,248   99.6        5,120

With my uneducated eye, I deduced that SYSTEM Used 99.6% was called a "vital clue".

So in another script I had hidden away, some other little tip Penny gave me one day, was to make a bigger canvas, so to speak
alter tablespace system
add datafile 'C:\app\Scott\oracle\11.2.0\oradata\sw11g\SYSTEM02.dbf'
SIZE 1000M;
And once Penny got back to me with another suggestion:
select owner,segment_name,segment_type
      ,bytes/(1024*1024) size_m
from dba_segments
where tablespace_name = 'SYSTEM'
and    bytes/(1024*1024)> 1
order by size_m desc

----- --------------- ------- ------
SYS   AUD$            TABLE      360
SYS   IDL_UB1$        TABLE      288
SYS   SOURCE$         TABLE      120
SYS   IDL_UB2$        TABLE       40
SYS   ARGUMENT$       TABLE       16
SYS   I_SOURCE1       INDEX       15
SYS   C_OBJ#          CLUSTER     14  
She palmed me off to the relevant documentation where I could do some trimming. for those watching at home.

Here's what I came up with
    audit_trail_type         => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    default_cleanup_interval => 24*7 /* hours */);

    audit_trail_type           => DBMS_AUDIT_MGMT.AUDIT_TRAIL_ALL,
    audit_trail_purge_interval => 24*30 /* hours */,
    audit_trail_purge_name     => 'PURGE_ALL_AUDIT_TRAILS',
    use_last_arch_timestamp    => FALSE);

I was originally having problems with seeing the trace, as I didn't have enough temporary space (or something similar, I lost the actual message) in SYSAUX, so I gave that tablespace another 300M datafile.

Now my free space looks like I have enough room to swing a dinosaur, and I haven't had any velociraptors opening doors since.

SQL> @free sys%

NAME       KBytes         Used         Free   Used      Largest
----------------- ------------ ------------ ------ ------------
SYSAUX  1,075,200    1,015,680       59,520   94.5       43,008
SYSTEM  2,273,280      875,840    1,397,440   38.5    1,022,976
     ------------ ------------ ------------
sum     3,348,480    1,891,520    1,456,960

I saw Jeff Smith's article about his ORA-3113 issue via twitter. After locating my alert log, I couldn't track down anything wrong from where I first saw issues, but I'm not used to reading these logs.

Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
ERROR: Unable to normalize symbol name for the following short stack (at offset 199):
Sun Feb 26 19:16:53 2012
Errors in file c:\app\scott\diag\rdbms\sw11g\sw11g\trace\sw11g_smon_7520.trc  (incident=73309):
ORA-00600: internal error code, arguments: [25027], [2], [2965385640], [], [], [], [], [], [], [], [], []
Errors in file c:\app\scott\diag\rdbms\sw11g\sw11g\trace\sw11g_m002_7944.trc  (incident=73461):
ORA-07445: exception encountered: core dump [kgllkal()+151] [ACCESS_VIOLATION] [ADDR:0xFFFFFFFFFFFFFFFF] [PC:0x92A2D07] [UNABLE_TO_READ] []
Incident details in: c:\app\scott\diag\rdbms\sw11g\sw11g\incident\incdir_73309\sw11g_smon_7520_i73309.trc
Incident details in: c:\app\scott\diag\rdbms\sw11g\sw11g\incident\incdir_73461\sw11g_m002_7944_i73461.trc
Non-fatal internal error happenned while SMON was doing cursor transient type cleanup.
SMON encountered 1 out of maximum 100 non-fatal internal errors.
Sun Feb 26 19:17:01 2012
Trace dumping is performing id=[cdmp_20120226191701]
Maybe it might help someone on day, or someone can add further detail.


Monday 19 March 2012

New Perth Blogger - Phil Robinson

Are you sick of blogs to focused on technical details or tuning hoo-ha?

Probably not, but you may appreciate a new blog from fellow Perth-ite Phil Robinson. 
His content won't be technical from an Oracle perspective, but he'll probably shed some light on other ideas such as modelling; testing & software quality; and just outside-the-box style ideas.

He's been a trainer for many years throughout Australia & South East Asia; he's well published; and is always a pleasure to talk to.

You'll also find him on Twitter @lonsdalesystems

I look forward to seeing his content as a blogger.


Thursday 15 March 2012

On Oracle's statement of direction for Forms & Reports

For anyone that may have missed it, Oracle announced their current statement of direction for Oracle Application Development Tools - Statement of Direction: Oracle Forms, Oracle Reports and Oracle Designer

Statements of direction (SOD) can be fascinating reads, here is the current one for Oracle Application Express, and my thoughts on the 4.2 & 4.1 documents.

As a former (and sometimes current) Forms developer, I read this one with interest as it pertains to the pending 12c offering from Oracle.

The product strategy still oozes SOA and Fusion Middleware, but I found some interesting snippets:

Oracle has no plan to discontinue support of Oracle Forms

Which is great - as mentioned a few paragraphs earlier:
Oracle recognizes this considerable investment and remains committed to the long-term support of these product. 
It's as ubiquitous as Cobol, which fed a few jokes in the twitter world.  While there is no rush to move forward with your Forms applications, you might be up for more pain the longer you leave a plan to move on. I've worked in environments where Cobol is still around, and while the paradigm difference is a little more vast, it can make for massive migration projects.

Oracle’s strategic reporting solution is now BI Publisher

That's a shame in a way, perhaps I'm being nostalgic, but Oracle Reports was an awesome product. Reporting solutions still seem to remain a massive question mark for some sites, at least indecision on the best solution for the relevant environment. The cost seems to scare many away from using BI publisher, I guess especially when you compare the free Application Express product side-by-side. For comparison, I wouldn't like to estimate how much investment the infrastructure is for Oracle Reports, but it might be a middle-term solution for some as they migrate the application base away from Forms to ADF or Apex.

Protect the investment you have in your existing technology by upgrading to the latest release

Please do, I've been working in multiple sites still on 3.x and it's frustrating. Not only for the developer, but for what we can efficiently provide to make the application UI friendly. The same goes for your database, and of course with your Forms environment. There haven't been too many new features since Forms 6i, however advancements have been made to support newer hardware, operating systems and interfacing back to the database.

Oracle Designer 10g was the last version of this product

Move on people. I was never a big designer user - I had the pleasure of using Headstart once, but the universe has evolved. Among other options, there is SQL Developer Data Modeler.

On Oracle's recommendations for migrating from Forms and Reports, they first place your environment in context.

If you need to integrate with Fusion Applications; have extensive business rules & UI logic within the application; general preferences to Java/JEE (among others) => then move to Oracle JDeveloper with ADF.

If most of your application logic is within PL/SQL; most of the processing is within the database, and you don't favour Java => then move to Oracle Application Express.

(On a side note, Chris Muir has a good post answering the question on how much Java is needed for ADF)

It seems the two common, major factors in this decision are
1) The scale of your project and size of your team
2) Your infrastructure & processing requirements

I'm not sure I'm really convinced on the first point. I've never been involved in a JDeveloper project, however I have been in a few small & large Forms projects. I think it really depends on the application you need to build and the team you have.

For me, the most important factor is the infrastructure you currently have, and where you want to go with that. Commenting any further is tough because most of my concentration goes into development. I do listen to what those in the know have to say, though - and it seems to be key to their decision making.

Second to that, though, understanding of the capability of both products is essential. A recent discussion with someone that had little knowledge of Apex (and they are still on 3.1) had them well inside the JDeveloper fence, likening the capability of "dinky" Apex to something like MS Access.

I'm not about to defend Apex as a product, and say it's the only destination out there for migrating away from Forms - but choose the right tool for your job at hand. Chris' blog title hits the nail on the head - one size does not fit all.

Arm yourself with the right information before deciding how/where/when to move away from Forms.  Talk with people that have been involved in both types of projects; talk with experts in the relevant areas; find out the capabilities of each product; consider the infrastructure you have & the status of your databases; & consider the team of developers you already have.

And most importantly, don't forget your users. At the end of the day, they're the ones that need to use what you build, and the workflow of the application needs to suit them. Web pages can get pretty clever these days, but your are still constrained to that browser environment. Do you they need more than what a browser can provide?

Finally, take anything your local Oracle sales rep has to save with a few grains of salt ;-) I'll re-iterate a previous point - talk with the experts, the grunts in the field who are familiar with the capabilities of each product and then re-think about the big picture.

That's my 5 cents.

Thursday Thought - Fascinating Science

Phil Plait is a gateway drug to skepticism.

It's certainly not the first time that phrase has been used, but I'm pretty sure that's how I found myself immersed the last few years in various podcasts & blog feeds.

I think what fundamentally started it was the conspiracy theories describing why we supposedly didn't land on the moon. I've been an astronomy buff since I was a little tucker, and I thought I could rebut many of the arguments myself, but I thought I'd arm myself with more facts. Somewhere along the line I stumbled across the old Bad Astronomy site. A simple example of common misconceptions (which leads generally to pareidolia, which itself is a fascinating topic) is the illusion created by craters - depending on your perspective.
(thanks to Bad Astronomy)
Simple concepts like this turn many of the conspiracy theories up-side down.

To ignore all that and simply move to today's science news, and providing more evidence to the moon-landing deniers - where they'll probably just move the goal posts again, here is part of an awesome shot from the Lunar Reconnaissance Orbiter, found in one of my RSS feeds.
Apollo 11 landing site from 24km up
It shows various footprints, reflections from objects on the ground - many components not achievable but doing something like trying to point Hubble at the moon. Some of the later sites show the tracks from the lunar rover.

And if you rotate that shot 90 degrees left or right, you'll see the illusion Phil Plait talks about. He's written a few books too, I recommend Death from the Skies - these are the ways the world will end. It includes a fascinating chapter on "heat death".

And some people say science isn't cool!

Wednesday 14 March 2012

A sign to refactor SQL

Note to fellow programmer: just wondering why the yearn for multiple subqueries when it seems a simple group by would suffice?

If you see multiple instances of the one table in a query, why not see if it can be re-factored.
Admittedly the query was originally querying from separate tables which got consolidated into a view, so the query pedigree was sharpened already - if that's the right term to use.

      ,TO_CHAR(c.my_date, 'DD/MM/YYYY HH:MI AM') my_date
      ,(SELECT SUM(cl.my_total)
        FROM my_view cl
        WHERE cl.my_id = c.my_id) my_value
      ,(SELECT COUNT(1)
        FROM my_view cl
        WHERE cl.my_id = c.my_id
        AND   cl.special_nbr_field > 0) my_cnt
FROM   my_view c


SELECT my_id
      ,TO_CHAR(my_date, 'DD/MM/YYYY HH:MI AM') my_date
      ,SUM(my_total) my_value
      ,SUM(SIGN(special_nbr_field)) item_cnt
from my_view
group by my_id, my_date, my_name

Don't feel negative - I added an elegant use of SIGN

I fear my puns are getting worse...

Tuesday 13 March 2012

Penny Cookson as an ADF boot-camp commander

Sergeant Penny
For those of you lucky enough to be attending Collaborate12 in Las Vegas, and are <insert your own adjective> enough to be interested in ADF - our charismatic managing director Penny Cookson will be managing an ADF EMG boot camp.

ADF session details are available here, and Penny's titles are

  • ADF: A Path to the Future for Dinosaur Nerds
  • JDeveloper ADF and the Oracle Database - Friends Not Foes
Chris Muir reminded me Penny won best paper for the Friends not Foes session - not surprising since she has previously won Oracle Magazine's Educator of the Year.

Friday 9 March 2012

It's only a bug when you're looking

If you haven't guessed already, I'm a big fan of science.

I especially like it when science cross paths with programming. I've always remembered the suggestion for the only time the <blink> tag should ever be used:

In related news, it turns out there are various names for different kinds of bugs- heisenbugs, for example.
If you haven't seen it, Geek & Poke is an easy to read web comic about all things geeky - click on that link for the punchline to that panel.

If you haven't guessed, it relates to Heisenberg's uncertainty principle and to the Schrödinger's paradox of the cat.

Other web comics I subscribe to also feature this paradox:
An early xkcd - make sure you check out the alt text

It makes me wonder whether defect tracking software should have an attribute for developers to assign these various bug types.

Thursday 8 March 2012

Thursday Thought: Body of Knowledge

Anyone see Chris' tweet showing a visual representation of the six kingdoms?

It kinda put us into a little perspective, like the pale blue dot.

What it immediately remind me of was a creation of Matt Might:
I would confidently suggest that circular family tree - I'm sure there's a fancier name - is representative of hundreds or even thousands of PhDs. Apparently there are ~3000 species in the tree.

Incorporate this body of knowledge with those boundaries it shares with physics, geology, paleontology etc... you have the a predictive scientific theory, such as the Theory of Evolution.

And to think what I do from day to day, I wouldn't get past the middle of the red, depending on how we compare academics to those in the field.

Wednesday 7 March 2012

Exporting deleted Apex applications

It's very rare I lose a piece of work, but recently when removing a bunch of miscellaneous applications from my workspace, I inadvertently removed one that I think I should have kept - so I thought I better go restore it.

Update: June 2016: Turns out there's a craftier solution - just create a new blank application with the same app_id, then you can use the declarative offering.

Heading over to the application export, we can in fact export applications as of x many minutes ago. I know a few people who have used this feature to save lost work, but it's dependent on a number of factors - one of which is that your application still needs to be present in the workspace!

So I got my database to turn back time so I could export the application from the database before I nuked it.

Trouble is, in my first effort, I went a little too far.

See, in the midst of battle, I wasn't sure exactly when I removed it, and how far on my little laptop db I could create time paradoxes.

I found the magic number then grabbed a copy of my application - I could have done the same thing via the SQL Developer GUI but I just happened to have this syntax on hand.

And so that's how I saved the day, well, as it turns out I did have a copy after all.

Of course, when I try to double check information about this particular feature, I find people like Tyler wrote about this some time ago. Never hurts to have a reminder :-)

-- to assist with googlers, I thought I better add the code, instead of just the image.
exec dbms_flashback.enable_at_time(systimestamp-0.17);
select wwv_flow_utilities.export_application_to_clob(120) from dual;
select * from apex_applications;


Friday 2 March 2012