In this Document
APPLIES TO:Oracle Database - Enterprise Edition - Version 10.2.0.1 and laterInformation in this document applies to any platform. PURPOSEThis note aims to provide guidance on how to reclaim wasted space (fragmented space) introduced by deleting rows, moving or dropping tables, etc .. DETAILSHow to know if the segment is fragmentedTable
exec dbms_stats.gather_table_stats('<OWNER>','<TABLE NAME>');
select owner,table_name,round((blocks*8),2)||' kb' "TABLE SIZE",round((num_rows*avg_row_len/1024),2)||' kb' "ACTUAL DATA" from dba_tables where table_name='<YOUR TABLES'S NAME>'; IndexAn index could grow to be even bigger than the table's size at times, if your table has only one column then it's expected than the index's size could slightly exceed the table's size. The other reason for index growth (bigger than table's size or not) would be fragmentation. Rebuilding an index or recreating it usually save space on the cost of index performance. Before you decide to rebuild / recreate and index please check the following document first: Index Rebuild, the Need vs the Implications (Doc ID 989093.1) LOB segment (both Securefiles and Basic files)
The details for both methods are explained in the following document: How to Determine what storage is used in a LOBSEGMENT and should it be shrunk / reorganized? (Doc ID 1453350.1) How to reclaim free space (below HWM)Table
Refer: Why is no space released after an ALTER TABLE ... SHRINK? (Doc ID 820043.1) Index
LOB SegmentThere are multiple options here:
This is explained in more details in How to Shrink (make less sparse) a LOB (BASICFILE or SECUREFILE)? (Doc ID 1451124.1) How to reclaim the space back to the OS / ASM diskgroup by decreasing the datafile sizeIf you want to decrease the datafile's size by an amount bigger than the bytes at the end of the datafile you will have to do the following procedure: For more details please refer to How to Resize a Datafile (Doc ID 1029252.6) System tablespacePlease note that the procedure described in the previous section doesn't apply to system tablespace, this tablespace contains many bootstrap objects that should not be moved, if system tablespace is fragmented the only possible way is to recreate the database. You can recreate the database and copy the data using TTS (Transportable Tablespace) or Export/Import method. |
How To Reclaim Wasted Space on The Segment (Table, Index and LOB) and Tablespace Levels (文档 ID 16827
最新推荐文章于 2021-08-26 15:53:54 发布