Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vic.

Asked: August 12, 2016 - 8:44 am UTC

Last updated: August 14, 2016 - 5:39 am UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

Hi,

I have a quick question that doesn't require a test script or anything, if I may?

I have a table that is partitioned by date. Each year has it's own ASSM locally managed tablespace. The dates go back as far as 2006. There is a BLOB which holds pdf's. This table is now about 20 terabytes in size of which most of the data is the BLOBS. We have a requirement to archive off everything older than a couple of years and free up some space. The result will be moving the blobs into the cloud (cloud provider to be decided).

So I guess my question would be, once the blobs are removed, can I free up space somehow? I was thinking along the lines of possibly marking the BLOB column in a particular partition as unused then dropping the column in that partition.

I am going to try & perform some tests of my own but was hoping for a bit of a steer.

I know a question that could be asked by yourselves is why would you want to do this, but lets just say that it has to be done.

Any suggestions are more than welcome.

Regards,

Vic

and Connor said...

I'm a little confused by your question. If your data is split by year into tablespaces, then archiving off 'n' older years is just a case of dropping those partitions and then dropping the tablespaces to recover the space no ?

Or are you saying that the *data* is kept, but the *blobs* only will be removed ?

If that is the case, then once the blobs are copied out, you still have that old data. You havent mentioned row counts, but if its large, then deletion could be a pretty costly activity. Similarly, a 'drop column' is just as costly.

If the intention is to ditch the blob column (from the database altogether) I'd be inclined to perform a one-off CTAS which should be relatively efficient.

Hope this helps.

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks for that

Vic Cordiner, August 12, 2016 - 12:13 pm UTC

Hi Connor,

Absolutely agree. I suppose my main question was if it would be possible to drop the column after the blob has been removed for a particular partition.

Regards,

Vic
Connor McDonald
August 14, 2016 - 5:39 am UTC

You could only drop the column if you removed the blob for *all* partitions.

But if you've done 'set unused', then an 'alter table move' (at table or partition level) will reclaim that space.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here