oracle 如何回收已删除 LOB 的存储空间
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/3038792/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to reclaim storage for deleted LOBs
提问by Jim Hudson
I have a LOB tablespace. Currently holding 9GB out of 12GB available. And, as far as I can tell, deleting records doesn't reclaim any storage in the tablespace. (This was by the simple method of monitoring storage -- queries against user_extents, which is about all I'm allowed as a non-DBA) I'm getting worried about handling further processing.
我有一个 LOB 表空间。目前持有 12GB 中的 9GB 可用空间。而且,据我所知,删除记录不会回收表空间中的任何存储空间。(这是通过监视存储的简单方法——对 user_extents 的查询,这是我作为非 DBA 所允许的所有内容)我开始担心处理进一步的处理。
My concern is simply running out of space -- we're at about 9 GB out of 12 GB available for the tablespace and I want to figure out how to reclaim space before asking for more.
我担心的只是空间不足——我们在表空间可用的 12 GB 中大约有 9 GB,我想在要求更多空间之前弄清楚如何回收空间。
The LOB columns are stored in a separate tablespace, though "storage in row" is allowed for small ones.
LOB 列存储在单独的表空间中,但对于小列允许“按行存储”。
This is Oracle 11.1 and the data are in a CLOB and a BLOB column in the same table. The LOB Index segments (SYS_IL...) are small, all the storage is in the data segments (SYS_LOB...)
这是 Oracle 11.1,数据位于同一个表中的 CLOB 和 BLOB 列中。LOB 索引段(SYS_IL...)很小,所有的存储都在数据段(SYS_LOB...)中
We'e tried purge and coalesce and didn't get anywhere -- same number of bytes in user_extents.
我们尝试了清除和合并,但没有得到任何结果——user_extents 中的字节数相同。
"Alter table xxx move" will work, but we'd need to have someplace to move it to that has enough space for the revised data. We'd also need to do that off hours and rebuild the indexes, of course, but that's easy enough.
“Alter table xxx move”会起作用,但我们需要有一个地方可以将它移动到有足够空间容纳修改后的数据的地方。当然,我们还需要在非工作时间这样做并重建索引,但这很容易。
Copying out the good data and doing a truncate, then copying it back, will also work. But that's pretty much just what the "alter table" command does.
复制好的数据并进行截断,然后将其复制回来,也将起作用。但这几乎就是“alter table”命令所做的。
Am I missing some easy ways to shrink things down and get the storage back? Or is "alter table xxx move" the best approach? Or is this a non-issue and Oracle will grab back the space from the deleted lob rows when it needs it?
我是否缺少一些简单的方法来缩小内容并恢复存储?或者“改变表xxx移动”是最好的方法?或者这不是问题,Oracle 会在需要时从已删除的 lob 行中夺回空间?
回答by Jean-Fran?ois Savard
This question is old, but never had a working answer and may be useful for lot of people so here the solution I found when facing this problem.
这个问题很老,但从来没有一个有效的答案,可能对很多人有用,所以这里是我在面对这个问题时找到的解决方案。
You're right, deleting the LOB won't reclaim any storage in the tablescape.
你是对的,删除 LOB 不会回收表中的任何存储。
Running this query before and after deleting LOB will give the same result
在删除 LOB 之前和之后运行此查询将给出相同的结果
select bytes
from user_segments
where tablespace_name = yourTableSpace
and segment_name = yourTableName;
Reading thisOracle doc, I found out that you need to execute the following statement
阅读这个Oracle doc,我发现你需要执行以下语句
ALTER TABLE yourLobTable MODIFY LOB (yourLobColumn) (SHRINK SPACE);
Which will shrink a BASICFILE
LOB segment.
这将缩小BASICFILE
LOB 段。
Now if you re-execute the query
现在,如果您重新执行查询
select bytes
from user_segments
where tablespace_name = yourTableSpace
and segment_name = yourTableName;
You will notice that the space have been released to the tablespace, and will be able to re-use that space as you wanted.
您会注意到空间已释放到表空间,并且可以根据需要重新使用该空间。
Now for those who would like to free up space on disk, note that the datafile won't automatically re-size and will still hold the full size on your disk.
现在,对于那些想要释放磁盘空间的人,请注意数据文件不会自动重新调整大小,并且仍会保留磁盘上的完整大小。
But before re-sizing the datafile, make sure you deallocate unused space in a segment (stole it from the same doc as stated before) using these queries (use the one you need)
但是在重新调整数据文件大小之前,请确保使用这些查询(使用您需要的查询)释放段中未使用的空间(从前面所述的同一个文档中窃取它)
ALTER TABLE table DEALLOCATE UNUSED KEEP integer;
ALTER INDEX index DEALLOCATE UNUSED KEEP integer;
ALTER CLUSTER cluster DEALLOCATE UNUSED KEEP integer;
Note that the KEEP
clause is optional and lets you specify the amount of space retained in the segment. You can verify that the deallocated space is freed by examining the DBA_FREE_SPACE
view.
请注意,该KEEP
子句是可选的,可让您指定段中保留的空间量。您可以通过检查DBA_FREE_SPACE
视图来验证释放的空间是否已释放。
Now to re-size your datafile,
现在要重新调整数据文件的大小,
ALTER DATABASE DATAFILE yourDatafile.dbf resize 500M
Note that the maximum size you can shrink when resizing is based on the position of the last block of data in your datafile. Therefore there is a lot of chance that after a deletion of a lot of datas you can't really re-size. Then, the easiest way is to export the data and re-import in another tablespace, then drop the old tablespace. When importing the data, Oracle will pack them as much as possible.
请注意,调整大小时可以缩小的最大大小取决于数据文件中最后一个数据块的位置。因此,在删除大量数据后,您很有可能无法真正重新调整大小。然后,最简单的方法是将数据导出并重新导入另一个表空间,然后删除旧表空间。在导入数据时,Oracle 会尽量打包。
If you want to free up even more space, you can clear your temp tablespace which sometime hold a lot of space (5GB for my database). Use the following statement to check it size :
如果您想释放更多空间,您可以清除临时表空间,它有时会占用大量空间(我的数据库为 5GB)。使用以下语句检查它的大小:
SELECT tablespace_name, file_name, bytes
FROM dba_temp_files WHERE tablespace_name like 'TEMP%';
回答by Gary Myers
Generally, once an extent is allocated to a table, it stays allocated. Empty space within a table can be re-used if additional data is inserted into the table. However it is hard to reclaim space from within a table because the table may have, for example, blocks 1 to 100, and the empty blocks are in blocks 50-75, or the empty blocks are 1,3,5,7 etc.
通常,一旦将区分配给表,它就会保持分配状态。如果将附加数据插入表中,则表中的空白空间可以被重新使用。但是很难从表中回收空间,因为该表可能有,例如,块 1 到 100,而空块在块 50-75 中,或者空块是 1、3、5、7 等。
The question is, are you worried about space being re-used within the table or do you need the space released for other objects in the tablespace, or do you need to be able to shrink the tablespace datafiles ?
问题是,您是否担心表中的空间被重复使用,或者您是否需要为表空间中的其他对象释放空间,或者您是否需要能够缩小表空间数据文件?