I'm restructuring a table in our database, moving a BLOB column from one table to another. This table has 300GB of BLOB data. The database server has disk space limitations, so there is not enough space for a copy of the data during migration.
Is there any way to move the data directly to another table, without copying the information? Something like copying only the reference to the BLOB field's memory location, without necessarily copying the data? The tables are in the same tablespace.
The only options I could think of, is to "move" the blobs in batches and not all at once. You might try to copy some (for example 100) rows columns from one table to the other table, then set the blob column to null or delete the column from the original table and commit this batch before continuing with the next batch of rows that still need to be copied.
In theory this should only require to twice the space of the blobs in each batch but in reality it really depends on how Oracle will manage the insertions and deletions.
Be aware that in an Oracle Database, when a BLOB is no longer needed or is deleted from a table, the space occupied by the column is not immediately released or freed. Instead, the space is marked as available for reuse. This behavior is known as "space reuse" or "space reclamation".
If you need to explicitly reclaim space from a BLOB column after performing large-scale deletions or updates, you can use the ALTER TABLE ... SHRINK SPACE.