Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

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 1000+ times

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.

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

More to Explore

Administration

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