这篇文章主要为大家展示了“ORACLE中表空间和表碎片的示例分析”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“ORACLE中表空间和表碎片的示例分析”这篇文章吧。

idle>selecta.tablespace_name,sqrt(max(a.blocks)/sum(a.blocks))*(100/sqrt(sqrt(count(a.blocks))))FSFIfromdba_free_spacea,dba_tablespacesbwherea.tablespace_name=b.tablespace_nameandb.contentsnotin('TEMPORARY','UNDO')groupbya.tablespace_nameorderby2;TABLESPACE_NAMEFSFI----------------------------------------EAM2.57604251ALM20.1734462SYSAUX22.2842767SYSTEM23.7809729USERS53.439579RECCAT100ARCH1007rowsselected.idle>123456789101112131415161718192021

数字越小，表空间碎片较多，当小于30%的时候说明碎片程度很可观了。

引用官方的一段话：

The ideal situation is to have one large free extent in your tablespace. The more extents of free space there are in the tablespace, the more likely you will run into fragmentation problems. The size of the free extents is also very important. If you have a lot of small extents (too small for any next extent size) but the total bytes of free space is large, then you may want to consider defragmentation options.

脚本中统计了连续空间及对连续空间求和，当表中的总的free空间很大时，但有很多小块，说明碎片化越严重。

========Script:tfstsfgm========SETECHOoffREMNAME:TFSTSFRM.SQLREMUSAGE:"@path/tfstsfgm"REM------------------------------------------------------------------------REMREQUIREMENTS:REMSELECTONDBA_FREE_SPACEREM------------------------------------------------------------------------REMPURPOSE:REMThefollowingisascriptthatwilldeterminehowmanyextentsREMofcontiguousfreespaceyouhaveinOracleaswellastheREMtotalamountoffreespaceyouhaveineachtablespace.FromREMtheseresultsyoucandetecthowfragmentedyourtablespaceis.REMREMTheidealsituationistohaveonelargefreeextentinyourREMtablespace.ThemoreextentsoffreespacethereareintheREMtablespace,themorelikelyyouwillrunintofragmentationREMproblems.Thesizeofthefreeextentsisalsoveryimportant.REMIfyouhavealotofsmallextents(toosmallforanynextREMextentsize)butthetotalbytesoffreespaceislarge,thenREMyoumaywanttoconsiderdefragmentationoptions.REM------------------------------------------------------------------------REMDISCLAIMER:REMThisscriptisprovidedforeducationalpurposesonly.ItisNOTREMsupportedbyOracleWorldWideTechnicalSupport.REMThescripthasbeentestedandappearstoworkasintended.REMYoushouldalwaysrunnewscriptsonatestinstanceinitially.REM------------------------------------------------------------------------REMMaintextofscriptfollows:createtableSPACE_TEMP(TABLESPACE_NAMECHAR(30),CONTIGUOUS_BYTESNUMBER)/declarecursorqueryisselect*fromdba_free_spaceorderbytablespace_name,block_id;this_rowquery%rowtype;previous_rowquery%rowtype;totalnumber;beginopenquery;fetchqueryintothis_row;previous_row:=this_row;total:=previous_row.bytes;loopfetchqueryintothis_row;exitwhenquery%notfound;ifthis_row.block_id=previous_row.block_id+previous_row.blocksthentotal:=total+this_row.bytes;insertintoSPACE_TEMP(tablespace_name)values(previous_row.tablespace_name);elseinsertintoSPACE_TEMPvalues(previous_row.tablespace_name,total);total:=this_row.bytes;endif;previous_row:=this_row;endloop;insertintoSPACE_TEMPvalues(previous_row.tablespace_name,total);end;./setpagesize60setnewpage0setechooffttitlecenter'ContiguousExtentsReport'skip3breakon"TABLESPACENAME"skippageduplicatespoolcontig_free_space.lisremcolumn"CONTIGUOUSBYTES"format999,999,999column"COUNT"format999column"TOTALBYTES"format999,999,999column"TODAY"noprintnew_valuenew_todayformata1remselectTABLESPACE_NAME"TABLESPACENAME",CONTIGUOUS_BYTES"CONTIGUOUSBYTES"fromSPACE_TEMPwhereCONTIGUOUS_BYTESisnotnullorderbyTABLESPACE_NAME,CONTIGUOUS_BYTESdesc;selecttablespace_name,count(*)"#OFEXTENTS",sum(contiguous_bytes)"TOTALBYTES"fromspace_tempgroupbytablespace_name;spooloffdroptableSPACE_TEMP/1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798表空间级别整理方法

对于ASSM管理的表空间，一般都是由smon进程自动整理，前提是表空间的pctincrease值为非0，可以将表空间的缺省存储参数pctincrease改为非0，一般将其设为1。如修改temp表空间的pctincrease属性：alter tablespace temp default storage(pctincrease 1); 这样就可以自动整理表空间级别的碎片整理了。

如果对于字典管理的表空间，可以用下面的命令进行整理：
sql> alter tablespace <表空间名> collesce;

SQL>altertablet1enablerowmovement;--打开行移动表已更改。SQL>altertablet1shrinkspacecascade;--压缩表及相关数据段并下调HWMSQL>altertablet1shrinkspacecompact;--只压缩不下调HWMSQL>altertablet1shrinkspace;--下调HWMSQL>altertablet1disablerowmovement;--关闭行移动1234567891011

只能在ASSM、本地管理的表空间进行，完成这些之后不需要进行索引的重建，但统计信息最好重新收集下，脚本参加本博客上上篇。^_^

用exp/imp导出后，重新导入重建，在重新创建索引和重新收集统计信息。

create table newtable as select * from old_table

drop old_table

rename table newtable to old_table

重建索引，收集统计信息。

sql>altertable<表名>movetablespace<表空间名>重建索引，收集统计信息。123

以上是“ORACLE中表空间和表碎片的示例分析”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注亿速云行业资讯频道！