I was working happily on my laptop in the living room, kids playing on the rug, birds were singing, et cetera. All of a sudden, a blood-curdling scream erupts from the office. It was my wife.
“Jeff! Is there an “undo” function in the roster program??!?!?”
(A very quick bit of background: the “roster program” is a little Apex application I wrote so my wife can manage a roster of over 100 volunteers at our local church, assigning them to a range of duties, while ensuring that they are available, are willing to perform the duty, and that their assignments do not conflict with other assignments (i.e. they normally can’t do two jobs at once).)
I open the program and look at the roster. It’s almost completely blank. Only half an hour previously it was almost completely filled. Not looking good. Rosalie runs into the room, beside herself – with good reason, even with the computer helping it takes a lot of effort to assign all the jobs.
Her: “Didn’t you build an “undo” feature?”
Me: “No – I didn’t get around to it…” While querying the database directly and finding that yes, indeed, all the rows have nothing but NULLs, I’m preparing to console her and offer to help to rebuild it. “Do you remember what was happening just before it all disappeared?”
Her: “I selected all the dates, selected just the “Helper” jobs, then clicked the “Clear Dates” button.”
Me: “Ohhhhhhhhhh……” Disaster. I now explain that the “Clear Dates” button is intended to clear all the assignments for the dates selected, not just the ones showing on the screen. Plus, she’d selected all the dates, so it went off and merrily cleared every single assignment from the roster. “Did you happen to email any spreadsheets to anyone?” I ask in vain hope.
Her: “Yes, but only for a few jobs. I guess I can put those back in and start the rest from scratch.” says my poor wife, trudging away knowing she’ll be doing this for the next five hours. Instead of cooking dinner. This is getting worse by the minute!
Me: “Hang on! I have an idea – leave it with me.” I say, thinking, “I hope that the rollback segment is big enough…”
I run this query:
select * from roster_dates2 as of timestamp systimestamp - 0.1;
With this result:
ORA-01555: snapshot too old: rollback segment number 3 with name "_SYSSMU3$" too small
Ok, maybe a shorter time difference:
select * from roster_dates2 as of timestamp systimestamp - 0.01;
Like magic, all the roster assignments that had been NULL are showing as NOT NULL. Brilliant! So now some UPDATE wizardry…
update roster_dates2 set (vol_id_worship_am, vol_id_worship_pm, ...) = (select vol_id_worship_am, vol_id_worship_pm, ... from roster_dates2 as of timestamp systimestamp - 0.01 x where x.roster_date = roster_dates2.roster_date) where roster_date between to_date('05-APR-2009','DD-MON-YYYY') and add_months(sysdate, +12);
A quick query to check it hasn’t done anything drastically wrong, then commit.
Me: “Rosalie, do you want to hit the Refresh button?”
Then, fast steps.
A big smile followed closely by my wife bursts into the room and gives me a big kiss.
Me: “Am I a wizard?”
Her: “Yes, darling, you are a wizard.”
I add some additional code to the start of the “Clear Dates” button:
RAISE_APPLICATION_ERROR(-20000,'Sorry, this function has been disabled.');
Life is good.