Stress Inoculation Training
“Sweat more during peace: bleed less during war.” – Sun Tzu ~475 BCE
I picked the “War Stories” metaphor for a reason: essentially the unpredictable situations you encounter in the DBA Trenches and the experience you obtain are not dissimilar with respect to learning and growing from combat situations in the heat of battle. A new DBA may experience intense paralysis in the minefield of fear when a bad thing happens on the database. Good judgement comes from experience, and most of that experience comes from the stressful experiences we encounter in our jobs. Military special forces use the term “stress inoculation training” to prepare themselves for adverse situations in the battlefield. To train for a bad database day, we build our bag of tricks and checklists, so that when we go into these situations in the future, we are well prepared and rehearsed to handle most situations.
This war story comes from Frank Dernoncourt; you can reach out to him on Twitter @fdernoncourt . I’ve personally heard similar war stories repeated, so I guess it is an easy situation to get yourself into. Hopefully we can all learn from this experience as “iron sharpens iron”.
Truncating Tables in the Wrong Schema:
“I have been a full-time DBA since 2012. I was a junior DBA when this happened. I was tasked to truncate all the tables of a schema except 3 of them. There were 57 tables in that schema. So I figured “I’m going to generate the 54 SQL statements automatically with one SQL command”. Since I did not want to write a long
SELECT 'TRUNCATE TABLE '||table_name||' ;' FROM user_tables WHERE 1=1 AND TABLE_NAME in (‘TABLE1’,’TABLE2,’TABLE3’,…’TABLE54’), I thought it’d be smart to write the code for ALL tables but exclude those 3:
WHERE TABLE_NAME NOT IN (‘EXCEPTION1’,’EXCEPTION2’,’EXCEPTION3’)But of course, that code works on any schema: the WHERE CLAUSE will always be verified.”
The Unhappy Users:
“So I ran the SQL, then the generated SQL. Then people complained…
I’d run my SQL on the wrong schema. And nothing in my code prevents it from truncating all the tables of whatever schema.
For example, if my schema has 6 tables and I must truncate 4 of them: if I’m connected with the wrong user, my code will generate 6 TRUNCATE statements, since my exceptions are not known in this schema:
When your code generates dozens of SQL statements, you can’t visually detect that there aren’t 54 of them.
So, one way to check you have generated the correct number of TRUNCATE statements is to precede your code with a COUNT that has the same WHERE CLAUSE as your SELECT 'TRUNCATE TABLE… FROM USER_TABLES…
”
Lessons:
“Lesson learned: make sure you are on the right schema before running any DML, especially with databases that have lots of schemas with similar names!”
Editor Comments:
Backup tables before truncating, as you never know when you’ll need them again, or the user asks for them back.
Attention to detail may someday, save the day. The best virtue of a DBA is caring that bad outcomes are minimized.
Get into the mindset of expecting things to go wrong, but without fear. One can fear what they don’t know, so build your body of knowledge, anticipate, and prepare for those possibilities.
Get into the “zone” and focus in on what you are trying to do; fall back to your training (your interior monolog so to speak) and have confidence in your pre-battle drills.
Chalk it up to experience and put that one in your bag of tricks.
Appeal for More War Stories
That’s it for now. Again, I’m starting to get volunteers to join me in sharing their war stories, so tune up your war stories. The idea of a doing the war stories in a podcast format is starting to take shape. My vision here is to make a place where DBA’s, developers, Oracle “grey beards” and other battle-scarred veteran (and new) techies get together and tell each other their war stories, banter around a bit, and have some fun. Please contact me if you are interested in having your war story included. Of course, “the stories you are about to hear are true, only the names have been changed to protect the innocent [guilty]” [some of you old timers will recall where this quote is from].