這篇文章主要為大家展示了“ORACLE中表空間和表碎片的示例分析”，內(nèi)容簡而易懂，條理清晰，希望能夠幫助大家解決疑惑，下面讓小編帶領(lǐng)大家一起研究并學習一下“ORACLE中表空間和表碎片的示例分析”這篇文章吧。

表空間碎片率

idle>selecta.tablespace_name,sqrt(max(a.blocks)/sum(a.blocks))*(100/sqrt(sqrt(count(a.blocks))))FSFI
fromdba_free_spacea,dba_tablespacesb
wherea.tablespace_name=b.tablespace_name
andb.contentsnotin('TEMPORARY','UNDO')
groupbya.tablespace_name
orderby2;
TABLESPACE_NAMEFSFI
----------------------------------------
EAM2.57604251ALM20.1734462SYSAUX22.2842767SYSTEM23.7809729USERS53.439579RECCAT100ARCH1007rowsselected.
idle>
123456789101112131415161718192021

數(shù)字越小，表空間碎片較多，當小于30%的時候說明碎片程度很可觀了。

按照表空間顯示連續(xù)的空閑時間

引用官方的一段話：

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.

腳本中統(tǒng)計了連續(xù)空間及對連續(xù)空間求和，當表中的總的free空間很大時，但有很多小塊，說明碎片化越嚴重。

========
Script:tfstsfgm
========SETECHOoff
REMNAME:TFSTSFRM.SQLREMUSAGE:"@path/tfstsfgm"REM------------------------------------------------------------------------
REMREQUIREMENTS:
REMSELECTONDBA_FREE_SPACE
REM------------------------------------------------------------------------
REMPURPOSE:
REMThefollowingisascriptthatwilldeterminehowmanyextents
REMofcontiguousfreespaceyouhaveinOracleaswellasthe
REMtotalamountoffreespaceyouhaveineachtablespace.FromREMtheseresultsyoucandetecthowfragmentedyourtablespaceis.
REM
REMTheidealsituationistohaveonelargefreeextentinyour
REMtablespace.Themoreextentsoffreespacethereareinthe
REMtablespace,themorelikelyyouwillrunintofragmentation
REMproblems.Thesizeofthefreeextentsisalsoveryimportant.
REMIfyouhavealotofsmallextents(toosmallforanynextREMextentsize)butthetotalbytesoffreespaceislarge,thenREMyoumaywanttoconsiderdefragmentationoptions.
REM------------------------------------------------------------------------
REMDISCLAIMER:
REMThisscriptisprovidedforeducationalpurposesonly.ItisNOTREMsupportedbyOracleWorldWideTechnicalSupport.
REMThescripthasbeentestedandappearstoworkasintended.
REMYoushouldalwaysrunnewscriptsonatestinstanceinitially.
REM------------------------------------------------------------------------
REMMaintextofscriptfollows:
createtableSPACE_TEMP(
TABLESPACE_NAMECHAR(30),
CONTIGUOUS_BYTESNUMBER)
/
declare
cursorqueryisselect*
fromdba_free_space
orderbytablespace_name,block_id;
this_rowquery%rowtype;
previous_rowquery%rowtype;
totalnumber;
begin
openquery;
fetchqueryintothis_row;
previous_row:=this_row;
total:=previous_row.bytes;
loop
fetchqueryintothis_row;
exitwhenquery%notfound;
ifthis_row.block_id=previous_row.block_id+previous_row.blocksthen
total:=total+this_row.bytes;
insertintoSPACE_TEMP(tablespace_name)
values(previous_row.tablespace_name);
else
insertintoSPACE_TEMPvalues(previous_row.tablespace_name,
total);
total:=this_row.bytes;
endif;previous_row:=this_row;
endloop;
insertintoSPACE_TEMPvalues(previous_row.tablespace_name,
total);end;.
/
setpagesize60setnewpage0setechooff
ttitlecenter'ContiguousExtentsReport'skip3breakon"TABLESPACENAME"skippageduplicate
spoolcontig_free_space.lis
rem
column"CONTIGUOUSBYTES"format999,999,999column"COUNT"format999column"TOTALBYTES"format999,999,999column"TODAY"noprintnew_valuenew_todayformata1
rem
selectTABLESPACE_NAME"TABLESPACENAME",
CONTIGUOUS_BYTES"CONTIGUOUSBYTES"fromSPACE_TEMP
whereCONTIGUOUS_BYTESisnotnullorderbyTABLESPACE_NAME,CONTIGUOUS_BYTESdesc;selecttablespace_name,count(*)"#OFEXTENTS",
sum(contiguous_bytes)"TOTALBYTES"fromspace_temp
groupbytablespace_name;spooloff
droptableSPACE_TEMP
/
1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798

表空間級別整理方法

對于ASSM管理的表空間，一般都是由smon進程自動整理，前提是表空間的pctincrease值為非0，可以將表空間的缺省存儲參數(shù)pctincrease改為非0，一般將其設(shè)為1。如修改temp表空間的pctincrease屬性：alter tablespace temp default storage(pctincrease 1); 這樣就可以自動整理表空間級別的碎片整理了。

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

表級別碎片整理方法

1.首選shrink

SQL>altertablet1enablerowmovement;--打開行移動表已更改。
SQL>altertablet1shrinkspacecascade;--壓縮表及相關(guān)數(shù)據(jù)段并下調(diào)HWMSQL>altertablet1shrinkspacecompact;--只壓縮不下調(diào)HWMSQL>altertablet1shrinkspace;--下調(diào)HWMSQL>altertablet1disablerowmovement;--關(guān)閉行移動1234567891011

只能在ASSM、本地管理的表空間進行，完成這些之后不需要進行索引的重建，但統(tǒng)計信息最好重新收集下，腳本參加本博客上上篇。^_^

2.導入導出

用exp/imp導出后，重新導入重建，在重新創(chuàng)建索引和重新收集統(tǒng)計信息。

3.CATS技術(shù)

1. create table newtable as select * from old_table

2. drop old_table

3. rename table newtable to old_table

4. 重建索引，收集統(tǒng)計信息。

4.move tablespace

sql>altertable<表名>movetablespace<表空間名>
重建索引，收集統(tǒng)計信息。123

總結(jié)

以上是生活随笔為你收集整理的ORACLE中表空间和表碎片的示例分析的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。