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: 220.127.116.11
Viewed 100+ times
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?
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.