0

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.

marciel.deg
  • 400
  • 3
  • 17
  • No. You can't reassign LOBs like that. Unless you override the default, smaller LOBs are actually stored inline with the other columns of the table. Even if you prevent that by disallowing inline storage, (1) the table rows have pointers to the LOB extents, and (2) LOBs are equipartitioned with the table. They are glued at the hip. You can't steal a LOB segment as it is a dependent of the table segment, the two simply cannot be divorced. A step-wise copy and delete by row range is about your only option, and it's a terribly poor performing option too. I think you need to ask for more space. – Paul W Jul 31 '23 at 12:41
  • In the Oracle Enterprise there is a blob deduplication option, which could be useful. However it is not the version of Oracle we use. – marciel.deg Jul 31 '23 at 12:57
  • 1
    No, deduplication isn't helpful here. That simply saves space by not storing the same BLOB value more than once per column per table. It won't deduplicate between columns or between tables. And it requires very expensive ACO license. If your intention was simply to compress the existing table so it uses less space, LOB compression would do that much better, but that also requires the ACO license. – Paul W Jul 31 '23 at 13:00
  • 1
    Depending on what type of model changes you want to perform you may create a brand-new table as the current state of the model, move everything into it, rename old one into *another table with blob data*, drop all columns except LOBs and then shrink old table. This may require some sort of dependency analysis, but it will keep the data where it is – astentx Jul 31 '23 at 14:46
  • maybe DBMS_REDEFINITION could be useful for this situation, in any case the most efficient would be that OLD becomes NEW without moving the BLOBs at all, but this may requires at least 3 redefinition steps, all depending on....what type of model changes... – p3consulting Jul 31 '23 at 15:53

1 Answers1

2

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.

doberkofler
  • 9,511
  • 18
  • 74
  • 126
  • 1
    You need to `move lob(lob_column) store as (tablespace )` to deallocate LOB segment, because *You cannot specify the shrink_clause for SecureFiles LOBs.* This is the default storage type since 12c – astentx Jul 31 '23 at 22:12