How to determine the actual size of the LOB segments and how to free the deleted/unused space above/below the HWM (Doc ID 386341.1)
In this Document
Goal |
Fix |
References |
Applies to:
Oracle Database - Enterprise Edition - Version 9.2.0.1 to 11.2.0.3 [Release 9.2 to 11.2]Information in this document applies to any platform.
Goal
Some hints about how to check the actual size of a LOB segment including the free and available space, above and below the HWM, including the commands to free the space above the HWM.
Fix
The size of the LOB segment can be found by querying dba_segments, as follows:
The first thing to do would be to check the space that is actually allocated to the LOB data.
Please note that the UNDO data for a LOB segment is kept within the LOB segment space. The above query result is merely the still active LOB data. The rest of allocated data is undo space. The undo space can vary quite a lot, from being very small in size (when LOBs are only inserted) to being very large (when many LOBs are deleted) and is largely depending on the PCTVERSION LOB parameter or the RETENTION parameter.
Hence, The difference between these two is free space and/or undo space. It is not possible to assess the actual empty space using the queries above alone, because of the UNDO segment size, which is virtually impossible to assess. Furthermore, even when there is free space in the LOB, this does not mean this space can be released to the tablespace, it could be under the HWM. To find the freeable space, use the UNUSED_SPACE procedure as shown below.
Check the "free" space within a LOB segment.
First, remember that a lob can have 3 states: "inuse", "deleted" and "free". There are no statistics for the LOB segment, the DBMS_SPACE package is the only tool that could give an idea about it. As well, there is no view showing the deleted space within the LOB. The deleted space can be converted into free by rebuilding the freelist or by rebuilding the LOB segment itself, but this is not always possible.
Note:
LOB Partition sizing is not supported in DBMS_SPACE package until 10g.
Error ORA-00600 [ktsircinfo_num1] will be generated if the procedure is run against the lob partition on versions below 10g
P.S: When using dbms_lob.getlength, the output is in characters for CLOBs and NCLOBs, and in bytes for BLOBS and BFILES.
One can get an idea about how much space is actually used and what could be deallocated as follows:
1. Determine the unused space within the LOB segment, above the HWM. using the UNUSED_SPACE procedure.
declare
TOTAL_BLOCKS number;
TOTAL_BYTES number;
UNUSED_BLOCKS number;
UNUSED_BYTES number;
LAST_USED_EXTENT_FILE_ID number;
LAST_USED_EXTENT_BLOCK_ID number;
LAST_USED_BLOCK number;
begin
dbms_space.unused_space('','','LOB',
TOTAL_BLOCKS, TOTAL_BYTES, UNUSED_BLOCKS, UNUSED_BYTES,
LAST_USED_EXTENT_FILE_ID, LAST_USED_EXTENT_BLOCK_ID,
LAST_USED_BLOCK);
dbms_output.put_line('SEGMENT_NAME = ');
dbms_output.put_line('-----------------------------------');
dbms_output.put_line('TOTAL_BLOCKS = '||TOTAL_BLOCKS);
dbms_output.put_line('TOTAL_BYTES = '||TOTAL_BYTES);
dbms_output.put_line('UNUSED_BLOCKS = '||UNUSED_BLOCKS);
dbms_output.put_line('UNUSED BYTES = '||UNUSED_BYTES);
dbms_output.put_line('LAST_USED_EXTENT_FILE_ID = '||LAST_USED_EXTENT_FILE_ID);
dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID = '||LAST_USED_EXTENT_BLOCK_ID);
dbms_output.put_line('LAST_USED_BLOCK = '||LAST_USED_BLOCK);
end;
/
This is the only space that could be deallocated using the ALTER TABLE ... DEALLOCATE UNUSED command as seen below.
2.1. For tablespaces that are using free lists, the following procedures could be used to find the blocks that are on the freelists (which is not extremely useful when wanting to free up some space, as these blocks are just filled below the PCTUSED. Nevertheless, this shows there is some free space within the LOB.
2.1.1. for one freelist group:
free_blocks number;
begin
DBMS_SPACE.FREE_BLOCKS('', '', 'LOB',0 ,free_blocks);
dbms_output.put_line('Nb of free blocks = '||free_blocks);
end;
/
2.1.2. for multiple freelist groups:
declare i int;
declare v_freelist_groups:=100; --- replace this with the actual number
begin
FOR i IN 0..v_freelist_groups-1 LOOP
DBMS_SPACE.FREE_BLOCKS('','','LOB', i, :free_blks);
dbms_output.put_line('instance#: ' || i);
dbms_output.put_line('free_blks: ' || :free_blks);
dbms_output.put_line('');
END LOOP;
the above procedures would not work for ASSM tablespaces, because the free_blocks procedure does not work with them.
2.2 SPACE_USAGE procedure could be used for ASSM segments instead:
v_unformatted_blocks number;
v_unformatted_bytes number;
v_fs1_blocks number;
v_fs1_bytes number;
v_fs2_blocks number;
v_fs2_bytes number;
v_fs3_blocks number;
v_fs3_bytes number;
v_fs4_blocks number;
v_fs4_bytes number;
v_full_blocks number;
v_full_bytes number;
begin
dbms_space.space_usage ('', '', 'LOB', v_unformatted_blocks,
v_unformatted_bytes, v_fs1_blocks, v_fs1_bytes, v_fs2_blocks, v_fs2_bytes,
v_fs3_blocks, v_fs3_bytes, v_fs4_blocks, v_fs4_bytes, v_full_blocks, v_full_bytes);
dbms_output.put_line('Unformatted Blocks = '||v_unformatted_blocks);
dbms_output.put_line('FS1 Blocks = '||v_fs1_blocks);
dbms_output.put_line('FS2 Blocks = '||v_fs2_blocks);
dbms_output.put_line('FS3 Blocks = '||v_fs3_blocks);
dbms_output.put_line('FS4 Blocks = '||v_fs4_blocks);
dbms_output.put_line('Full Blocks = '||v_full_blocks);
end;
/
Determine ASSM tablespaces or ASSM residing LOBs with:
select tablespace_name,EXTENT_MANAGEMENT,allocation_type,segment_space_management
from dba_tablespaces where segment_space_management='AUTO'
and tablespace_name NOT LIKE '%UNDO%'
and tablespace_name = ''
/
--Find LOBs that reside in ASSM tablespaces:
col Table format a24
col Tablespace format a22
col partitioned format a11
col column_name format a24
select column_name "Column",table_name "Table",tablespace_name "Tablespace",partitioned
from DBA_LOBS
where tablespace_name in (select tablespace_name
from DBA_TABLESPACES where segment_space_management='AUTO'
and tablespace_name NOT LIKE '%UNDO%'
and owner NOT IN ('SYS','SYSTEM','CTXSYS','MDSYS','ORDSYS','DBSNMP',
'SYSMAN','XDB','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS'))
order by tablespace_name
/
3. The command used to deallocate the lob free space is:
This is not very useful in most circumstances. There is probably very little space above the high water mark. On top of this, the deleted space from inside the the lob segment is not even shown by the procedures above. This is the expected behaviour and, unfortunately, currently there is no procedure/view to show the deleted space. Having such an option is the current subject of an enhancement request.
However, the deleted space can be turned into free space and, when this happens, the procedure in 2.2 would show this free space. To turn the deleted space into free space, one has to rebuild the freepools. The command used to do this is:
The free pools number can be taken from the dba_lobs view. When this value is null, the command can be run with a freepools number of 1. This procedure will not release the free space to the tablespace.
If one wants to release the space,
- for versions below 10.2, one has to rebuild the LOB segment using the MOVE command:
- 10.2 introduced an even better option, it extended the SHRINK SPACE command to LOBs. As such, one can remove the deleted and free space altogether from the LOB segment and LOB index:
WARNINGS:
#1. Be aware of the following bug before adjusting storage freelists for any LOB
Serious LOB corruption can occur after an
ALTER TABLE
#2. Be aware of the following bug before using the SHRINK option in releases which are <=10.2.0.3:
Bug: 5636728 LOB corruption / ORA-1555 when reading LOBs after a SHRINK operation
Please check:
Note.5636728.8 Ext/Pub Bug 5636728 - LOB corruption / ORA-1555 when reading LOBs after a SHRINK operation
for details on it.
#3. Be aware that, sometimes, it could be needed to perform. the shrink operation twice, in order to avoid the:
Bug:5565887 SHRINK SPACE IS REQUIRED TWICE FOR RELEASING SPACE.
is fixed in 10.2.
References
NOTE:1453350.1 - How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized?来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17252115/viewspace-773496/,如需转载,请注明出处,否则将追究法律责任。
<%=items[i].content%>
<%if(items[i].items.items.length) { %><%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
最新文章
- ASM Di Show PROVISIONED On One RAC,UNKNOWN Other Node,Cannot Add To DG_1468642.1
- Execution of DBMS_METADAT.GET_DDL results in ORA-19206,ORA-6512_292266.1
- Calling Dbms_metadata.Get_ddl From Stored Procedure Results Ora-31603_463483.1
- Linux: How To Setup UDEV Rules For RAC OCR Voting Dev On SLES10,RHEL5_414897.1
- Config DS devices for use Oracle ASM 11.2/12.1 IBM:Linux on System z_1377392.1
- Setting up ASM on linux with LVM (Doc ID 292348.1)
- Configuring device-mapper for CRS/ASM (Doc ID 357472.1)
- Manage ASM instance-creating diskgroup,adding/dropping/resizing disks_270066.1
- Oracle Linux and External Storage Systems (Doc ID 753050.1)
- Config and Use of Device Mapper Multipathing on Oracle Linux (OL)_555603.1
转载于:http://blog.itpub.net/17252115/viewspace-773496/