Who, Me? Welcome once again, dear reader, to Who, Me? - the cathartic corner of The Register wherein, once a week, we hand over to our readers, such as yourself, so that they may unburden themselves about times when things did not quite go according to plan.

This week's confession comes from a reader we'll Regomize as "Larry". Some years ago Larry was working as an Oracle DBA for a healthcare provider in the US. He hadn't been a DBA for very long so, in his words, he was not yet "all knowing or all powerful" - but he knew enough to get by and could ask intelligent questions of the older DBAs.

Then he was handed a Really Important Task™️. The biz he worked for produced monthly reports on its membership numbers, based on the current and previous months. That monthly report was automated, but for some reason the annual report still required a manual process. Larry told Who, Me? that it involved "backing up the most recent two months, truncating said months, running an ad hoc job to repopulate it so a report can be generated, then finally restoring from the backup."

Obviously this needed to be more efficient.

Larry had around this time become familiar with Oracle's Data Pump utility, which was great for one-off table backups. H also knew how to back up specific partitions, so - he thought - why not use the Data Pump to write an import parameter file to handle the yearly backups?

Now, obviously there was no need, in building this file, to backup the entire table and the more than ten years of data it stored. All he needed was the last two months. Enter a handy parameter called TABLE_EXISTS_ACTION. This would run on the import file and, if it found a table that already existed, perform whatever actions were desired.

Larry was a keen little learner, and he had learned a new thing.

His export of the relevant partitions ran flawlessly, so - he thought - the import would be equally flawless. Normally, the process would involve manually truncating the partitions in order to restore them from the backup, but the whole point of the exercise was to do away with manual intervention. Thus, Larry added TABLE_EXISTS_ACTION=TRUNCATE to the import parameter file.

His vision was that "the database would automatically truncate those two partitions and successfully reload the data, saving everyone time and energy." Cue praise from the higher-ups and knowing nods of approval from his fellow DBAs.

Except that when he ran the process, he found that the backup file was considerably smaller than he had expected it to be. Like, a lot. Like, 1GB when it should have been more than 75GB.

Which aroused his curiosity.

It transpired that TABLE_EXISTS_ACTION is, as its name more or less suggests, a table-level parameter, not a partition-level operation. And what he had essentially done was truncate not just the tables required for the report, but all of them.

Over ten years worth of customer data. Gone in seconds.

Thankfully, of course, there were backups and it didn't take long to restore all of the data. Indeed, no-one even reported broken links or any disruption of service. Only Larry ever knew - and he learned an important lesson about properly understanding a command you've just learned before you go using it on production data.

If you've ever had a learning experience like Larry's, we want to know about it - and so do your fellow Regizens. Tell us about it in an email to Who, Me? and we'll all share in the catharsis some future Monday. ®

https://www.theregister.com//2024/03/25/who_me/