oracle碎片整理方法

oracle碎片整理方法 extent autoallocate uniform Starting with Oracle 9i, DBAs can now create locally managed tablespaces. A Locally Managed TBS manages its own list of free extents in a bitmap block placed inside the header of the first data file of the tablespace. Inside the bitmap block, each bit maps to a free block in the tablespace. When creating a locally managed tablespace, you can specify the extent allocation method to be used. AUTOALLOCATE - means that the extent sizes are managed by Oracle. Oracle will choose the optimal next size for the extents starting with 64KB . As the segments grow and more extents are needed, Oracle will start allocating larger and larger sizes ranging from 1Mb to eventually 64Mb extents. This might help conserve space but will lead to fragmentation. This is usually recommended for small tables or in low managed systems. UNIFORM - specifies that the extent allocation in the tablespace is in a fixed uniform size. The extent size can be specified in M or K. The default size for UNIFORM extent allocation is 1M . Using uniform extents usually minimizes fragmentation and leads to better overall performance. SQL>CREATE TABLESPACE test_tablespcae DATAFILE '/emc/oradata/test_tablespace1.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL AUTOALLOCATE; SQL>CREATE TABLESPACE test_tablespcae DATAFILE '/emc/oradata/test_tablespace1.dbf' SIZE 50M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 512K; I usually prefer to keep large production-grade tables in UNIFORM sized tablespaces and smaller tables or tables in unmanaged environments in AUTOALLOCATE tablespaces. 上文提到“自动分配”方式extent的尺寸是从64kb开始，这就说明 segment的storage的initial参数必须大于64k，否则无效并使用 64k。 在“自动分配”方式下，建的参数:pctincrease、next extent、 Max extents pctused 等参数无效 理解: 1 插入数据到表的时候(表和段就是同义词)，先申请自由空间放数 据，会在HWM以上去寻找自由空间: ->如果是采取AUTOALLOCATE,则自己找合适的空间64K-64M oracle 自己决定因此当oracle希望找到20M空间时候又没有而且碎片也很 难找到适合大小的空间，则会报错。 ->如果是uniform则每次分配指定的大小，没有了会在碎片里面去 找，因此碎片会较少; 2 ->oracle SMON会自动合并些碎片; ->大表建议用uniform size,小表可以用autoallocate 3 碎片整理: ->exp-truncate-imp:delete不会reset HWM,只有truncate; ->rename old_tab to new_tab create old_tab imp old_tab rebuild index drop new_tab ->alter table name move tablespace_new truncate rebuild index 4 ->dba_segments ->dba_extents ->dba_tablespaces ->dba_free_space ->dba_free_extens