dbms_space.free_space

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

Whatistheuseofthisstoredproc?
Ittellsyouhowmanyblockshavefreespaceforupdates,right?
Butitdoesnottellyouhowmuchfreespaceineachblock.Wecan
getfreespaceinfo.fromdba_free_space.
Canyoushowhowthisproccanbeofvaluetous?
  Anotherprocsinthispackageisunused_space.
Ifitreports35blocks.Doesitmean35blockshavenever
haddatainit?
  Itseemsthatitdoesn'treportanyemptyblocksabove
thehighwatermark,doesit?
  Howcanwemakeuseofthisinfo?Canyougivesomeexamples
thatwecanusetheseprocedurestohelpmanagespace.

Thanks,Tom.


andwesaid...


Hereisanexampleshowinghowtousedbms_spaceandhowtointerprettheoutput.Basicallybetweenthe2proceduresfreeblocksandunusedspace,we'llbeabletoget:freeBlocks......NumberofblocksonthefreelistTotalBlocks.....TotalblocksallocatedtothetableTotalBytes......TotalbytesallocatedtothetableUnusedBlocks....BlocksthathavenevercontaineddataUnusedBytes.....TheaboveinbytesItdoesnottellyouhowmanyblockshavefreespaceforupdates.WecantellyouhowmanyblocksarecandidatesforINSERTS(theyareonthefreelist)andblocksonthefreelisthavespaceforupdates--but--thereareblocksinthetablethathavespaceforupdatesbutthatarenotonthefreelist.Wecannotseetheminanyreport.Itdoesnottellyouhowmuchspaceisfreeineachblock(nothingdoes,typicallytherearethousandsorhundredsofthousandsofblocksinatable--ananalysisofthefreespaceblockbyblockisnotpractical.Wecangetanaveragefreespacebutnotblockbyblock).Thisreportdoesshowblocksabovethehighwatermark.UnusedBlocksareexactlytheblockabovethehighwatermark.Youcangetmostoftheinformationsuppliedbythispackagebyanalyzingthetableandusingqueriesagainstuser_tablesanduser_segments.Thefreelistanalysisismoredetailedusingthispackageasyoucanlookateachfreelistindependently.Belowisaprocedureyoucanusetomakeusingdbms_spacealittleeasier.AfterthatIcreateatableandshowhowspaceisbeingusedinitaftervariousoperations.Commentsinboldexplaintheoutput.ops$tkyte@8i>createorreplace2procedureshow_space3(p_segnameinvarchar2,4p_ownerinvarchar2defaultuser,5p_typeinvarchar2default'TABLE')6as7l_free_blksnumber;89l_total_blocksnumber;10l_total_bytesnumber;11l_unused_blocksnumber;12l_unused_bytesnumber;13l_LastUsedExtFileIdnumber;14l_LastUsedExtBlockIdnumber;15l_LAST_USED_BLOCKnumber;16procedurep(p_labelinvarchar2,p_numinnumber)17is18begin19dbms_output.put_line(rpad(p_label,40,'.')||20p_num);21end;22begin23dbms_space.free_blocks24(segment_owner=>p_owner,25segment_name=>p_segname,26segment_type=>p_type,27freelist_group_id=>0,28free_blks=>l_free_blks);2930dbms_space.unused_space31(segment_owner=>p_owner,32segment_name=>p_segname,33segment_type=>p_type,34total_blocks=>l_total_blocks,35total_bytes=>l_total_bytes,36unused_blocks=>l_unused_blocks,37unused_bytes=>l_unused_bytes,38LAST_USED_EXTENT_FILE_ID=>l_LastUsedExtFileId,39LAST_USED_EXTENT_BLOCK_ID=>l_LastUsedExtBlockId,40LAST_USED_BLOCK=>l_LAST_USED_BLOCK);4142p('freeBlocks',l_free_blks);43p('TotalBlocks',l_total_blocks);44p('TotalBytes',l_total_bytes);45p('UnusedBlocks',l_unused_blocks);46p('UnusedBytes',l_unused_bytes);47p('LastUsedExtFileId',l_LastUsedExtFileId);48p('LastUsedExtBlockId',l_LastUsedExtBlockId);49p('LastUsedBlock',l_LAST_USED_BLOCK);50end;51/Procedurecreated.ops$tkyte@8i>ops$tkyte@8i>createtablet(xint,ychar(2000)default'*')2storage(initial40knext40kminextents5)3tablespacesystem;Tablecreated.Icreateatablewith>1extenttomakeitinteresting.Ialsoputachar(2000)intheretomaketheminimumrowlengthbe2000bytes(charsalwaystaketheirmaxspacerightaway).Thisjustmakesmyrows"big"ops$tkyte@8i>insertintot(x)values(1);1rowcreated.Icreateonerowjusttousealittlespaceinthetableops$tkyte@8i>analyzetabletcomputestatistics;Tableanalyzed.ops$tkyte@8i>computesumofblocksonreportops$tkyte@8i>breakonreportops$tkyte@8i>selectextent_id,bytes,blocks2fromuser_extents3wheresegment_name='T'4andsegment_type='TABLE'5/EXTENT_IDBYTESBLOCKS------------------------------240960538192010457344704096051409605----------sum32Thisshowsthatthereare32blocksallocatedin5extentstothistable(asexpected)ops$tkyte@8i>clearbreaksops$tkyte@8i>selectblocks,empty_blocks,2avg_space,num_freelist_blocks3fromuser_tables4wheretable_name='T'5/BLOCKSEMPTY_BLOCKSAVG_spaceNUM_freeLIST_BLOCKS---------------------------------------------------13060911SinceIanalyzedthetable,Ihaveacccesstotheaboveinformation.You'llfindthatitmapsexactlytothedatabelow.Thereareatotalof32blocksallocatedtothetable(belowandasconfirmedbyuser_extentsabove).Thereare30EMPTY_BLOCKS(above)/UNUSED_BLOCKS(below).TheseareblocksabovetheHWM.Thisleaves2blocksunaccountedfor--1blockhasdatainit,theotherhastheextentmapforthetable(thefirstblockofeachtableisusedbythesystemitself).ops$tkyte@8i>execshow_space('T')freeBlocks.............................1TotalBlocks............................32TotalBytes.............................262144UnusedBlocks...........................30UnusedBytes............................245760LastUsedExtFileId....................1LastUsedExtBlockId...................64816LastUsedBlock.........................2PL/SQLproceduresuccessfullycompleted.ops$tkyte@8i>insertintot(x)2selectrownum3fromall_users4whererownum<505/49rowscreated.ops$tkyte@8i>commit;Commitcomplete.Sonowwehave50rowswith2keach--I'musinga8kblocksizesoIexpectabout3rows/block.Thatmeansabout18blocksofdataplus1forthesystem=about19blocksshouldbe"used"now.BelowIseethatIhaveo3blocksonthefreelist.theyhavemorespacefornewinserts(theyhavenothittheirpctusedyetandmaybeinsertedinto)o12unusedblocks,leaving20blocks"used".SinceIhave3onthefreelist--weprobablyusedalittlemorethenthe18fordata--weused19forthe50rows.Wehaveoneforthesystem--allaccountedfor.ops$tkyte@8i>execshow_space('T')freeBlocks.............................3TotalBlocks............................32TotalBytes.............................262144UnusedBlocks...........................12UnusedBytes............................98304LastUsedExtFileId....................1LastUsedExtBlockId...................64681LastUsedBlock.........................5PL/SQLproceduresuccessfullycompleted.ops$tkyte@8i>deletefromt;50rowsdeleted.ops$tkyte@8i>commit;Commitcomplete.Nowwecanseewhatadeletedoestoourutilization.ops$tkyte@8i>execshow_space('T')freeBlocks.............................19TotalBlocks............................32TotalBytes.............................262144UnusedBlocks...........................12UnusedBytes............................98304LastUsedExtFileId....................1LastUsedExtBlockId...................64681LastUsedBlock.........................5PL/SQLproceduresuccessfullycompleted.Theaboveshowsthatthedeletesimplyputallofourblocksonthefreelist.Wehave19blocksonthefreelist+12unusedblocks+1systemblock=32blocks.Allaccountedfor.NotethattheHWMstayedthesame--wedon'thave31unusedblocks--wehave12asbefore.TheHWMforatablewillneverdecreaseunlesswe.....ops$tkyte@8i>truncatetablet;Tabletruncated.ops$tkyte@8i>execshow_space('T')freeBlocks.............................0TotalBlocks............................32TotalBytes.............................262144UnusedBlocks...........................31UnusedBytes............................253952LastUsedExtFileId....................1LastUsedExtBlockId...................64816LastUsedBlock.........................1PL/SQLproceduresuccessfullycompleted.Truncateit.ThatputsalloftheblocksbelowtheHWM.Nowwehave31unusedblocks+1systemblock=32blockstotal.Noneonthefreelistsincenoneofthemhaveanydata.1<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 728x15, 创建于 08-4-23MSDN */google_ad_slot = "3624277373";google_ad_width = 728;google_ad_height = 15;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>

<script type="text/javascript"><!--google_ad_client = "pub-2947489232296736";/* 160x600, 创建于 08-4-23MSDN */google_ad_slot = "4367022601";google_ad_width = 160;google_ad_height = 600;//--></script><script type="text/javascript"src="http://pagead2.googlesyndication.com/pagead/show_ads.js"></script>