Thanks for the question, Vic.
Asked: January 10, 2020 - 7:20 pm UTC
Last updated: January 13, 2020 - 4:01 am UTC
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 Connor 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.