What are these, and how do I get rid of them?

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.

More fun examples, using your own data to learn SQL, build REST APIs, etc.

Untappd || iTunes || Twitter || Strava || Netflix || Spotify


In the right hand corner of the Worksheet, you’ll see the ‘Data Load’ button.

I’ve added 4 or 5 files and hit the ‘Run All’ button.

Once it’s finished, I can see some new tables! Let’s go browse one.

Woohoo, my music is here.

If I go to query one of the SDW$ERR$_ tables, I can see there weren’t really any failed inserted rows.

I’m happy about this.

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.

No need type these out!

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.

Yeah, that’s what we want, the History.

Clicking into that brings me into this screen, pay attention to the toolbar, there’s a ‘trashcan’ button.

This does what we want.

Clicking that button we get a warning –

Yeah, yeah, yeah – nuke them already.

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.

My history is gone, and so are all of my accompanying SDW$ error tables.

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!

Author

I'm a Distinguished Product Manager at Oracle. My mission is to help you and your company be more efficient with our database tools.

Write A Comment