将BLOB从一个表移动到另一个表而不复制数据

huangapple go评论79阅读模式
英文:

Move BLOB from a table to another without copy data

问题

我正在重新组织我们数据库中的一张表,将一个BLOB列从一张表移到另一张表。这张表有300GB的BLOB数据。数据库服务器有磁盘空间限制,所以在迁移过程中没有足够的空间来复制数据。

是否有办法直接将数据移动到另一张表,而不是复制信息?类似于仅复制BLOB字段的内存位置的引用,而不必复制数据?这些表位于同一个表空间中。

英文:

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.

答案1

得分: 2

唯一我能想到的选项是分批次“移动”这些二进制大对象(blobs),而不是一次性全部移动。
你可以尝试从一个表中复制一些行(例如100行)到另一个表中,然后将二进制大对象列设置为null,或者删除原始表中的该列,并在继续复制下一批仍需要复制的行之前提交此批处理。

理论上,这应该只需要每批次中二进制大对象的两倍空间,但实际上这取决于Oracle如何管理插入和删除。

请注意,在Oracle数据库中,当不再需要BLOB或从表中删除它时,列占用的空间不会立即释放或释放。相反,该空间被标记为可供重用。这种行为被称为“空间重用”或“空间回收”。

如果您需要在执行大规模删除或更新后明确回收BLOB列的空间,您可以使用ALTER TABLE ... SHRINK SPACE

英文:

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.

huangapple
  • 本文由 发表于 2023年7月31日 19:20:07
  • 转载请务必保留本文链接:https://go.coder-hub.com/76803123.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定