Skip to Main Content

Breadcrumb

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.

Question and Answer

Connor McDonald

Thanks for the question, Vic.

Asked: January 10, 2020 - 7:20 pm UTC

Answered by: Connor McDonald - Last updated: January 13, 2020 - 4:01 am UTC

Category: Database Administration - Version: 12.1.0.2

Viewed 100+ times

Whilst you are here, check out some content from the AskTom team: Optimizing the PL/SQL Challenge IV: More OR Condition Woes

You Asked

Hi Masters,

This doesn't require a test case but is rather a pick your brains scenario.

We have a table with a BLOB column and a migrated column of datatype number. This column holds pdf, tiff and jpg files.
We also have a process that copies the blobs to Amazon S3, then marks a migrated column with a value of 1 (as migrated)

This table, even though we have updated blob column as to NULL after it has been migrated, is about 5 terabytes in size. I would like to some how reclaim the space from the empty blob segment.

I know the shrink ONLINE option (for the blob segment) is fine for Version 12.2 and up, however we're on 12.1. Upgrading isn't an option at the moment.

The options I have so far are:
1. expdp / impdp
2. Do the shrink off line with downtime.

Do you know of any other ways to reclaim the space?

Regards,
Vic

and we said...

Take a look at this thread

https://asktom.oracle.com/pls/apex/asktom.search?tag=reclaimreuse-lob-space

It talks about how securefiles are much better than basicfile - it will automatically do better space reclamation, and talks about how to migrate.

If you are already using securefiles, then (without moving to 12.2) your options are somewhat limited. You could do with a full DBMS_REDEFINITION but thats a pretty resource expensive operation.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database