I spend a lot of time loading data to my Oracle Database. I’m continuously loading different types of CSV, Excel, JSON, Avro files to make sure folks have a good experience when they’re using our tools.
But this post is more about the janitorial work one does AFTER your data loading tasks have been completed.
When loading data to your tables, we take advantage of a database feature provided via DBMS_ERRLOG (Docs.) This allows us to LOG any failed rows to be inserted to your tables. The error log tables all start with SDW$ERR$_.
You’ll have one of these logging tables for each table that’s had data imported using SQL Developer Web.
Let’s import some data
Need some interesting data to play with? Maybe you should try your own! I’ve talked about this a lot, but otherwise I’ll assume you have no shortage of CSV and Excel files laying around that you might want to put SQL over.
In the right hand corner of the Worksheet, you’ll see the ‘Data Load’ button.
Once it’s finished, I can see some new tables! Let’s go browse one.
If I go to query one of the SDW$ERR$_ tables, I can see there weren’t really any failed inserted rows.
Ok, my data is imported, I don’t need these logging tables anymore.
Let’s filter our list of tables, and I guess start dropping them.
Yes, I could write a script, or even create a JOB to drop these tables on a regular basis, or I could just ‘click the button.’
Using the Data Loading dialog to drop our error logging tables
Let’s re-open the data loading dialog, this time from the table browser.
There’s a ‘History’ item.
Clicking into that brings me into this screen, pay attention to the toolbar, there’s a ‘trashcan’ button.
Clicking that button we get a warning –
The action can’t be reverted because not only do we DROP the tables, we drop them with the PURGE keyword. That means they won’t be available for recovery from the Recycle Bin.
This button saved me a lot of typing. I figure you might be loading data too on a regular basis, and if you want to clean up your system, this will come in handy!
