MSSQL网页设计dbms_space.free_space

有了数据以后，我们就要想一个比较统一的方法来闪回。上面我们说了对于DML操作，可以通过反向执行所有逆操作来实现，对于语句里面的DDL，只能直接跳过。原因是一个DDL不一定有直接的逆操作。
Whatistheuseofthisstoredproc?
Ittellsyouhowmanyblockshavefreespaceforupdates,right?
Butitdoesnottellyouhowmuchfreespaceineachblock.Wecan
getfreespaceinfo.fromdba_free_space.
Canyoushowhowthisproccanbeofvaluetous?
Anotherprocsinthispackageisunused_space.
Ifitreports35blocks.Doesitmean35blockshavenever
haddatainit?
Itseemsthatitdoesntreportanyemptyblocksabove
thehighwatermark,doesit?
Howcanwemakeuseofthisinfo?Canyougivesomeexamples
thatwecanusetheseprocedurestohelpmanagespace.

Thanks,Tom.


andwesaid...


Hereisanexampleshowinghowtousedbms_spaceandhowtointerprettheoutput.Basicallybetweenthe2proceduresfreeblocksandunusedspace,wellbeabletoget: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_typeinvarchar2defaultTABLE)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=T4andsegment_type=TABLE5/EXTENT_IDBYTESBLOCKS------------------------------240960538192010457344704096051409605----------sum32Thisshowsthatthereare32blocksallocatedin5extentstothistable(asexpected)ops$tkyte@8i>clearbreaksops$tkyte@8i>selectblocks,empty_blocks,2avg_space,num_freelist_blocks3fromuser_tables4wheretable_name=T5/BLOCKSEMPTY_BLOCKSAVG_SPACENUM_FREELIST_BLOCKS---------------------------------------------------13060911SinceIanalyzedthetable,Ihaveacccesstotheaboveinformation.Youllfindthatitmapsexactlytothedatabelow.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--Imusinga8kblocksizesoIexpectabout3rows/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--wedonthave31unusedblocks--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.

ReviewsBookmarkReview|Bottom|TopTablespaceusedforaparticulartableMarch17,2001
Reviewer:spmurthyfromSingapore

HiTom,Thanksforyourreplyitismoreusefultometoknowthetablespace.Regards

BookmarkReview|Bottom|Topdbms_spaceusageMarch22,2001
Reviewer:B.N.SarmafromUSA

Tom,Excellent,nothingless.ItwouldhavebeennicehadyoushownaselectstatementwithautotondoingFTSuptohwm,evenifyouhavedeltedalltherowsandthesamewithtruncate.Itwouldhavebecomeagoodnotes.Yourexplanationwithexamplesmakesthingsveryclear.Whydontyouwriteabook:-)RegardsBN

BookmarkReview|Bottom|TopMarch23,2001
Reviewer:HelenaMarkovafromBratislava,Slovakia



BookmarkReview|Bottom|Topdbms_space.free_spaceMay09,2001
Reviewer:D.C.L.fromSeattle,USA

Righton.Awesomegripofthesubjectmatter.

BookmarkReview|Bottom|TopMay10,2001
Reviewer:VikramfromDelhi,India

Excellent

BookmarkReview|Bottom|TopAugust21,2001
Reviewer:k.v.s.RajufromSydney,Australia

Itsexcellent

BookmarkReview|Bottom|Topdbms_spaceSeptember19,2001
Reviewer:JimfromMA

Very,VeryGood!!!

BookmarkReview|Bottom|TopErrorsinshow_spaceSeptember20,2001
Reviewer:Areader

Tom,triedusingyourshow_spaceprocedure.ItcompiledsuccessfullybutonusingitIgetfollowingerrors:SQL>execshow_space(T)BEGINshow_space(T);END;*ERRORatline1:ORA-00942:tableorviewdoesnotexistORA-06512:at"SYS.DBMS_SPACE",line55ORA-06512:at"TOM.SHOW_SPACE",line22ORA-06512:atline1thetableTexistsunderschemaTOMandtheshow_spaceprocedurewascompiledunderuserTOM.DBMSUTIL/PRVTUTILetc.haveallbeenrun.WhatamImissing?

Followup:
Somethingmustbewrong--givemeafullexamplelikethis(thatshowsitworks)ops$tkyte@ORA817DEV.US.ORACLE.COM>createuseraidentifiedbya;Usercreated.ops$tkyte@ORA817DEV.US.ORACLE.COM>grantcreatesession,createprocedure,createtabletoa;Grantsucceeded.ops$tkyte@ORA817DEV.US.ORACLE.COM>alteruseraquotaunlimitedonusers;Useraltered.ops$tkyte@ORA817DEV.US.ORACLE.COM>connecta/aConnected.a@ORA817DEV.US.ORACLE.COM>@showspaceProcedurecreated.a@ORA817DEV.US.ORACLE.COM>createtablet(xint)tablespaceusers;Tablecreated.a@ORA817DEV.US.ORACLE.COM>execshow_space(T)PL/SQLproceduresuccessfullycompleted.ops$tkyte@ORA817DEV.US.ORACLE.COM>setserveroutputonops$tkyte@ORA817DEV.US.ORACLE.COM>execshow_space(T);FreeBlocks.............................0TotalBlocks............................64TotalBytes.............................524288UnusedBlocks...........................63UnusedBytes............................516096LastUsedExtFileId....................7LastUsedExtBlockId...................4809LastUsedBlock.........................1PL/SQLproceduresuccessfullycompleted.so,dothewholecreateuser/installtheprocedure/runthetestandseeifitreproduces.Ifnot,eitheryouwerenotloggedinasTOM,TOMdidnotownT,etc...(isTavieworsynonyminyourcase??)

BookmarkReview|Bottom|TopAlittlequestionOctober18,2001
Reviewer:IgorfromFrance

Idontunderstandhowyouknewitwouldbe32blocksforonerowof2000charsandnumber?

Followup:
the5extentswereexpected.the32blocksjusthappened.

BookmarkReview|Bottom|Top5extents40keach(8kblocksize)whynot5X5=25blocks?October22,2001
Reviewer:YKLIUfromCA,USA



BookmarkReview|Bottom|TopFreeSpaceOctober30,2001
Reviewer:anfromDE

itsexcellent!

BookmarkReview|Bottom|TopORA-14107:partitionspecificationisrequiredforapartitionedobjectNovember19,2001
Reviewer:Areader

SQL>CREATETABLET(XVARCHAR2(20));Tablecreated.SQL>EXECSHOW_SPACE(T);PL/SQLproceduresuccessfullycompleted.SQL>setserveroutputonSQL>EXECSHOW_SPACE(T);FreeBlocks.............................0TotalBlocks............................10TotalBytes.............................81920UnusedBlocks...........................9UnusedBytes............................73728LastUsedExtFileId....................5LastUsedExtBlockId...................126659LastUsedBlock.........................1PL/SQLproceduresuccessfullycompleted.T_P--isapartitionedtableSQL>EXECSHOW_SPACE(T_P);BEGINSHOW_SPACE(T_P);END;*ERRORatline1:ORA-14107:partitionspecificationisrequiredforapartitionedobjectORA-06512:at"SYS.DBMS_SPACE",line55ORA-06512:at"myschema.SHOW_SPACE",line22ORA-06512:atline1WhyIamIrunningintothiserrorwhiletryingtouseshow_spaceonapartitionedtable.

Followup:
Ok,timeforanupdateofthisutility!Ihadthissittingaroundalready--itdoestwothings1)addspartitionsupport2)makesitsothisrunsinSQLforanything...givesaresultsetinsteadofprinting.Youcaneasilymakeitdbms_output.put_lineifyouwant...Firstwestartwiththetypes:createorreplacetypeshow_space_typeasobject(ownervarchar2(30),segment_namevarchar2(30),partition_namevarchar2(30),segment_typevarchar2(30),free_blocksnumber,total_blocksnumber,unused_blocksnumber,last_used_ext_fileidnumber,last_used_ext_blockidnumber,last_used_blocknumber)/createorreplacetypeshow_space_table_typeastableofshow_space_type/Andthenthefunction:createorreplacefunctionshow_space_for(p_segnameinvarchar2,p_ownerinvarchar2defaultuser,p_typeinvarchar2defaultTABLE,p_partitioninvarchar2defaultNULL)returnshow_space_table_typeauthidCURRENT_USERaspragmaautonomous_transaction;typercisrefcursor;l_cursorrc;l_free_blksnumber;l_total_blocksnumber;l_total_bytesnumber;l_unused_blocksnumber;l_unused_bytesnumber;l_LastUsedExtFileIdnumber;l_LastUsedExtBlockIdnumber;l_last_used_blocknumber;l_sqllong;l_conjvarchar2(7)defaultwhere;l_datashow_space_table_type:=show_space_table_type();l_ownervarchar2(30);l_segment_namevarchar2(30);l_segment_typevarchar2(30);l_partition_namevarchar2(30);procedureadd_predicate(p_nameinvarchar2,p_valueinvarchar2)asbeginif(instr(p_value,%)>0)thenl_sql:=l_sql||l_conj||p_name||like||upper(p_value)||;l_conj:=and;elsif(p_valueisnotnull)thenl_sql:=l_sql||l_conj||p_name||=||upper(p_value)||;l_conj:=and;endif;end;beginl_sql:=selectowner,segment_name,segment_type,partition_namefromdba_segments;add_predicate(segment_name,p_segname);add_predicate(owner,p_owner);add_predicate(segment_type,p_type);add_predicate(partition,p_partition);executeimmediatealtersessionsetcursor_sharing=force;openl_cursorforl_sql;executeimmediatealtersessionsetcursor_sharing=exact;loopfetchl_cursorintol_owner,l_segment_name,l_segment_type,l_partition_name;exitwhenl_cursor%notfound;begindbms_space.free_blocks(segment_owner=>l_owner,segment_name=>l_segment_name,segment_type=>l_segment_type,partition_name=>l_partition_name,freelist_group_id=>0,free_blks=>l_free_blks);dbms_space.unused_space(segment_owner=>l_owner,segment_name=>l_segment_name,segment_type=>l_segment_type,partition_name=>l_partition_name,total_blocks=>l_total_blocks,total_bytes=>l_total_bytes,unused_blocks=>l_unused_blocks,unused_bytes=>l_unused_bytes,LAST_USED_EXTENT_FILE_ID=>l_LastUsedExtFileId,LAST_USED_EXTENT_BLOCK_ID=>l_LastUsedExtBlockId,LAST_USED_BLOCK=>l_LAST_USED_BLOCK);l_data.extend;l_data(l_data.count):=show_space_type(l_owner,l_segment_name,l_partition_name,l_segment_type,l_free_blks,l_total_blocks,l_unused_blocks,l_lastUsedExtFileId,l_LastUsedExtBlockId,l_last_used_block);exceptionwhenothersthennull;end;endloop;closel_cursor;returnl_data;end;/Thenwecan:ops$tkyte@ORA817DEV.US.ORACLE.COM>selectSEGMENT_NAME,PARTITION_NAMESEGMENT_TYPE,FREE_BLOCKS,TOTAL_BLOCKS,UNUSED_BLOCKS2fromtable(cast(show_space_for(HASHED,user,%)asshow_space_table_type))3/SEGMENT_NASEGMENT_TYPEFREE_BLOCKSTOTAL_BLOCKSUNUSED_BLOCKS---------------------------------------------------------------HASHEDPART_216462HASHEDPART_316462HASHEDPART_416462HASHEDPART_116462ops$tkyte@ORA817DEV.US.ORACLE.COM>Andin9i,wedchangethefunctiontobepipelined:ops$tkyte@ORA9I.WORLD>createorreplace2functionshow_space_for3(p_segnameinvarchar2,4p_ownerinvarchar2defaultuser,5p_typeinvarchar2defaultTABLE,6p_partitioninvarchar2defaultNULL)7returnshow_space_table_type8authidCURRENT_USER9PIPELINED10as11pragmaautonomous_transaction;12typercisrefcursor;13l_cursorrc;1415l_free_blksnumber;16l_total_blocksnumber;17l_total_bytesnumber;18l_unused_blocksnumber;19l_unused_bytesnumber;20l_LastUsedExtFileIdnumber;21l_LastUsedExtBlockIdnumber;22l_last_used_blocknumber;23l_sqllong;24l_conjvarchar2(7)defaultwhere;25l_ownervarchar2(30);26l_segment_namevarchar2(30);27l_segment_typevarchar2(30);28l_partition_namevarchar2(30);2930procedureadd_predicate(p_nameinvarchar2,p_valueinvarchar2)31as32begin33if(instr(p_value,%)>0)34then35l_sql:=l_sql||l_conj||p_name||like||upper(p_value)||;36l_conj:=and;37elsif(p_valueisnotnull)38then39l_sql:=l_sql||l_conj||p_name||=||upper(p_value)||;40l_conj:=and;41endif;42end;43begin44l_sql:=selectowner,segment_name,segment_type,partition_name45fromdba_segments;4647add_predicate(segment_name,p_segname);48add_predicate(owner,p_owner);49add_predicate(segment_type,p_type);50add_predicate(partition,p_partition);5152executeimmediatealtersessionsetcursor_sharing=force;53openl_cursorforl_sql;54executeimmediatealtersessionsetcursor_sharing=exact;5556loop57fetchl_cursorintol_owner,l_segment_name,l_segment_type,l_partition_name;58dbms_output.put_line(l_segment_name||,||l_segment_type);59exitwhenl_cursor%notfound;60begin61dbms_space.free_blocks62(segment_owner=>l_owner,63segment_name=>l_segment_name,64segment_type=>l_segment_type,65partition_name=>l_partition_name,66freelist_group_id=>0,67free_blks=>l_free_blks);6869dbms_space.unused_space70(segment_owner=>l_owner,71segment_name=>l_segment_name,72segment_type=>l_segment_type,73partition_name=>l_partition_name,74total_blocks=>l_total_blocks,75total_bytes=>l_total_bytes,76unused_blocks=>l_unused_blocks,77unused_bytes=>l_unused_bytes,78LAST_USED_EXTENT_FILE_ID=>l_LastUsedExtFileId,79LAST_USED_EXTENT_BLOCK_ID=>l_LastUsedExtBlockId,80LAST_USED_BLOCK=>l_LAST_USED_BLOCK);8182piperow(show_space_type(l_owner,l_segment_name,l_partition_name,83l_segment_type,l_free_blks,l_total_blocks,l_unused_blocks,84l_lastUsedExtFileId,l_LastUsedExtBlockId,l_last_used_block));85exception86whenothersthennull;87end;88endloop;89closel_cursor;9091return;92end;93/Functioncreated.ops$tkyte@ORA9I.WORLD>setarraysize1ops$tkyte@ORA9I.WORLD>selectSEGMENT_NAME,SEGMENT_TYPE,FREE_BLOCKS,TOTAL_BLOCKS,UNUSED_BLOCKS2fromtable(show_space_for(%,user,%))3/SEGMENT_NAMESEGMENT_TYPEFREE_BLOCKSTOTAL_BLOCKSUNUSED_BLOCKS--------------------------------------------------------------------KEEP_SCNTABLE16462EMPLOYEESTABLE06463STINKYTABLE06463OBJECT_TABLETABLE16462RUN_STATSTABLE26453EMPTABLE06462PROJTABLE06462XTABLE16462WORDSTABLE06463DOCSTABLE06463KEYWORDSTABLE06463DEPTTABLE26461CTABLE16462DSINVLINESTABLE16462NUM_STRTABLE16423TTABLE46428T1TABLE06463T2TABLE06463BOMTABLE16462PARTSTABLE16462SYS_C001371INDEX06462SYS_C001372INDEX06462SYS_C001574INDEX06462SYS_C001694INDEX06462SYS_C001695INDEX06462BOM_PKINDEX06462PARTS_PKINDEX0646227rowsselected.

BookmarkReview|Bottom|TopReaderDecember26,2001
Reviewer:ReaderfromUSA

Tom,Couldyouclarify,whythe*firstblock*ofadatafilebeingusedfortheOS.,2ndforsegmentheader,3rd...fordata.Icreatedatablets1intablespacets1,db_block_size8kResults:SQL>setserveroutputonsize1000000SQL>execshow_space(TS1,SYS,TABLE);FreeBlocks.............................1TotalBlocks............................2TotalBytes.............................16384UnusedBlocks...........................0UnusedBytes............................0LastUsedExtFileId....................2LastUsedExtBlockId...................2LastUsedBlock.........................2PL/SQLproceduresuccessfullycompleted.SQL>selectlpad(file_name,40),bytes/1024/8BLOCKS,TABLESPACE_NAMEfromdba_data_fileswhereTABLESPACE_NAME=TS1;LPAD(FILE_NAME,40)BLOCKSTABLESPACE_NAME-------------------------------------------------------------------------/u07/oradata/iiim/ts1.dbf3TS1SQL>selectSEGMENT_NAME,TABLESPACE_NAME,BLOCK_ID,BLOCKS,extent_idfromdba_extentswhereSEGMENT_NAME=TS1;SEGMTABLESPACE_NAMEBLOCK_IDBLOCKSEXTENT_ID----------------------------------------------------------------TS1TS1220SQL>selectTABLE_NAME,INITIAL_EXTENT,NEXT_EXTENTfromdba_tableswhereTABLE_NAME=TS1;TABLE_NAMEINITIAL_EXTENTNEXT_EXTENT-------------------------------------------------------TS1163848192SQL>altertablets1allocateextent(size8192K);altertablets1allocateextent(size8192K)*ERRORatline1:ORA-01653:unabletoextendtableSYS.TS1by1024intablespaceTS1Thedatafilehas3blocks,segmenthasused2blocks(includingsegmentheader).WhenItrytoallocateoneextent=1block,getora-1653Thanks

Followup:
IneversaidthefirstblockwouldbeusedbytheOS.Inanycase,youareaskingfor8192k(8MEG)ofspace,not8k.

BookmarkReview|Bottom|TopReaderDecember26,2001
Reviewer:ReaderfromUSA

Tom,Iamsorry,Ididtry8kandgotthisora-1653SQL>altertablets1allocateextent(size8k);altertablets1allocateextent(size8k)*ERRORatline1:ORA-01653:unabletoextendtableSYS.TS1by1intablespaceTS1Ihavefoundthe1stblockseemedtohavebeenusedbyOS,notsureifthisisplatformspecific(SiliconGraphics)Thanks

Followup:
whatistheCREATETABLESPACEcommandyouused(andwhyarewelosingsleepover1block)

BookmarkReview|Bottom|TopReaderDecember26,2001
Reviewer:ReaderfromUSA

Tom,CreateTablespacecommand:CreatetablespaceTS1datafile/u07/oradata/iiim/ts1.dbfsize24k;Createtablets1storage(initial8knext8kpctincrease0)tablespacets1;Thisispurelyofacademicinterest.Ofcourse,wedonotneedtospendtoomuchtimeonthis.IagreeAlthough,ifthedatafileisforexample1000Mandthesegmentinthetablespaceisinitial500Mnext500M,since1block(8/1024M)isusedforwhateverreasonotherthandatabaseEXTENTS,thereisfragmentationinduced;500Mthatcanbeallocatedforsegmentsand499.99Mgetsunusable;unlesssizethedatafiletobe1001Mtostartwith.Thanks

Followup:
Yes,thefirstblockofafileinaDICTIONARYmanagedtablespaceisusedbythesystem(us,Oracle)Justlikethefirst64kofaLOCALLYmanagedtablespaces.Additionally,onmysystemweallocated32kforthedatafiles--not24k.Thefollowingshowswhatyouaskfor--whatyougetandhowmuchisusablebyyou(iwouldhighlyrecommentLMTsbtw--avoidDMTs):ops$tkyte@ORA817DEV.US.ORACLE.COM>CreatetablespaceTS1datafile/tmp/ts1.dbfsize24kreuse;Tablespacecreated.ops$tkyte@ORA817DEV.US.ORACLE.COM>hostls-l/tmp/ts1.dbf-rw-r-----1ora81732768Dec2613:33/tmp/ts1.dbfops$tkyte@ORA817DEV.US.ORACLE.COM>@free1MaxPossMaxTablespaceNameKBytesUsedFreeUsedLargestKbytesUsed---------------------------------------------------------------------.....TS12481633.3160.0--------------------------sum3,818,8481,605,1442,213,70413rowsselected.see,24kinsize--8isused,16free...ops$tkyte@ORA817DEV.US.ORACLE.COM>altertablespacets1adddatafile/tmp/ts2.dbfsize24kreuse;Tablespacealtered.ops$tkyte@ORA817DEV.US.ORACLE.COM>hostls-l/tmp/ts2.dbf-rw-r-----1ora81732768Dec2613:33/tmp/ts2.dbfops$tkyte@ORA817DEV.US.ORACLE.COM>@free1MaxPossMaxTablespaceNameKBytesUsedFreeUsedLargestKbytesUsed---------------------------------------------------------------------TS148163233.3160.0-------------------------sum3,818,8721,605,1522,213,72013rowsselected.now,48k(24*2),16kused(1block/file)ops$tkyte@ORA817DEV.US.ORACLE.COM>

BookmarkReview|Bottom|TopExtremelyUsefulDecember26,2001
Reviewer:ReaderfromUSA

Tom,ThanksverymuchIdoplanonusingLMTinnewdatabases

BookmarkReview|Bottom|TopDecember27,2001
Reviewer:DixitfromNJ,USA

VeryGood

BookmarkReview|Bottom|TopWow!March20,2002
Reviewer:MinifromOH

ExactlywhatIwaslookingfor.ThankyousomuchTom

BookmarkReview|Bottom|TopHowtheextentsareallocatedinbytesApril30,2002
Reviewer:SantoshJadhavfromIndia

itwasaverygoodexplanationTOM.butihaveonedoubtops$tkyte@8i>selectextent_id,bytes,blocks2fromuser_extents3wheresegment_name=T4andsegment_type=TABLE5/EXTENT_IDBYTESBLOCKS------------------------------240960538192010457344704096051409605----------sum32thisiswhataftercreatingtableTwith40kinitialandnextextent.sowhyitis81920(3rdextent)and57344(4thextent).Andwhy7and10blocksareallocatedrespectively

Followup:
ItisbecauseIcreatedthetableinadictionarymanagedtablespaceandweallocatethingstowithin+-5blocks(toavoidfragmentingthetablespacefreespacetotally)Read
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a76965/c02block.htm#2846
itcoversthealgorithm.

BookmarkReview|Bottom|TopgoodstuffSeptember16,2002
Reviewer:DougfromCT,USA



BookmarkReview|Bottom|TopSystemblockisalways1?January09,2003
Reviewer:TonyfromIndia

DoesOracleallocateonlyonesystemblockevenforverybigtable?

Followup:
atleastone,itmightgetmorelater.dependsonblocksizeandnumberofextents.

BookmarkReview|Bottom|TopWhatAUTOSEGMENTSPACEMANAGEMENTtss?March20,2003
Reviewer:MattfromAustralia

Whatshouldthevalueoffreelist_group_idbeineachcalltodbms_space.free_blocks?Youdefaultyourvalueto1,shouldthisbe1fortablesinallcases?Ijustexecuteddbms_space.free_blocksforasegmentinaLOCALLYmanagedTSwithAUTOSEGMENTSPACEMANAGEMENTandgotthefollowingerror:10618,00000,"Operationnotallowedonthissegment"//*Cause:ThisDBMS_SPACEoperationisnotpermittedonsegmentsin//tablespaceswithAUTOSEGMENTSPACEMANAGEMENT//*Action:Recheckthesegmentnameandtypeandre-issuethestatementHowdoyouidentifyfreeblocksinthiscase?

Followup:
Ijustassumedonefreelist.ifyouhavemore,thisroutineisnot"sophisticatedenough"thereisadbms_space.space_usageroutineforASSM

BookmarkReview|Bottom|Topyourshow_spaceprocedureandpartitonedtablesMay06,2003
Reviewer:PINGU

HiIamtryingtouseyourshow_spaceprocedurebutitseemsthatitdoesnotworkwithpartitionedtables?Ithinkthecursorforxin(selecttablespace_namefromdba_tablespaceswheretablespace_name=(selecttablespace_namefromdba_segmentswheresegment_type=p_typeandsegment_name=p_segnameandSEGMENT_SPACE_MANAGEMENTAUTO)thesubqueryshouldweadddistinct?

Followup:
goforit.IadjustitasIneed.

BookmarkReview|Bottom|Topafteracloserlook...May06,2003
Reviewer:PINGU

HiIhadacloserlookandIdontunderstandwhyweneedtheforloopforxin(selecttablespace_namefromdba_tablespaceswheretablespace_namein(selectdistincttablespace_namefromdba_segmentswheresegment_type=p_typeandsegment_name=p_segnameandSEGMENT_SPACE_MANAGEMENTAUTO))loopdbms_space.free_blocks(segment_owner=>p_owner,segment_name=>p_segname,segment_type=>p_type,partition_name=>p_partition,freelist_group_id=>0,free_blks=>l_free_blks);endloop;Ithinkwedontuseanythingfromtheloop.....?Oriteratingforanything

Followup:
itonlycallsfreeblocksIFthesegmentspacemanagementisnotAUTOandtheobjectexists.justadda"andrownum=1"tothequery.Losethedistinct,itisnotrelevant.

BookmarkReview|Bottom|TopMay19,2003
Reviewer:Areader

HiTom,Iamgettingerrorwheniuseshow_spaceprocedureSQL>execshow_space(T1);ErrorORA-10618:Operationnotallowedonthissegment-10618BEGINshow_space(T1);END;*ERRORatline1:ORA-10618:OperationnotallowedonthissegmentORA-06512:at"GAURANG.SHOW_SPACE",line49ORA-06512:atline1Thanks

Followup:
havingnocluewhatt1is,ihavenocomment.

BookmarkReview|Bottom|TopMay20,2003
Reviewer:Areader

T1ISTABLE.Iamusingoracle9iR2

Followup:
sorry,maybeifyoushowustheentirething--areyouusingthescriptthatdoesautosegmentspacemanagement"showspace",etc...haveyoureadaboutthedbms_*packagesIuse(theyaredocumented).Itisprettystraightforwardstuff.worksformein9iops$tkyte@ORA920>l1createorreplaceprocedureshow_space2(p_segnameinvarchar2,3p_ownerinvarchar2defaultuser,4p_typeinvarchar2defaultTABLE,5p_partitioninvarchar2defaultNULL)6authidcurrent_user7as8l_free_blksnumber;910l_total_blocksnumber;11l_total_bytesnumber;12l_unused_blocksnumber;13l_unused_bytesnumber;14l_LastUsedExtFileIdnumber;15l_LastUsedExtBlockIdnumber;16l_LAST_USED_BLOCKnumber;17procedurep(p_labelinvarchar2,p_numinnumber)18is19begin20dbms_output.put_line(rpad(p_label,40,.)||21p_num);22end;23begin24forxin(selecttablespace_name25fromdba_tablespaces26wheretablespace_name=(selecttablespace_name27fromdba_segments28wheresegment_type=p_type29andsegment_name=p_segname30andowner=p_owner31andSEGMENT_SPACE_MANAGEMENTAUTO)32)33loop34dbms_space.free_blocks35(segment_owner=>p_owner,36segment_name=>p_segname,37segment_type=>p_type,38partition_name=>p_partition,39freelist_group_id=>0,40free_blks=>l_free_blks);41dbms_output.put_line(Oldfashioned);42endloop;4344dbms_space.unused_space45(segment_owner=>p_owner,46segment_name=>p_segname,47segment_type=>p_type,48partition_name=>p_partition,49total_blocks=>l_total_blocks,50total_bytes=>l_total_bytes,51unused_blocks=>l_unused_blocks,52unused_bytes=>l_unused_bytes,53LAST_USED_EXTENT_FILE_ID=>l_LastUsedExtFileId,54LAST_USED_EXTENT_BLOCK_ID=>l_LastUsedExtBlockId,55LAST_USED_BLOCK=>l_LAST_USED_BLOCK);5657p(FreeBlocks,l_free_blks);58p(TotalBlocks,l_total_blocks);59p(TotalBytes,l_total_bytes);60p(TotalMBytes,trunc(l_total_bytes/1024/1024));61p(UnusedBlocks,l_unused_blocks);62p(UnusedBytes,l_unused_bytes);63p(LastUsedExtFileId,l_LastUsedExtFileId);64p(LastUsedExtBlockId,l_LastUsedExtBlockId);65p(LastUsedBlock,l_LAST_USED_BLOCK);66*end;ops$tkyte@ORA920>droptablet;Tabledropped.ops$tkyte@ORA920>createtablet(xint);Tablecreated.ops$tkyte@ORA920>execshow_space(T)FreeBlocks.............................TotalBlocks............................8TotalBytes.............................65536TotalMBytes............................0UnusedBlocks...........................5UnusedBytes............................40960LastUsedExtFileId....................9LastUsedExtBlockId...................48LastUsedBlock.........................3PL/SQLproceduresuccessfullycompleted.

BookmarkReview|Bottom|TopFREEBLOCKVsPERFORMANCEJune12,2003
Reviewer:RaghufromMilwaukee,WIUSA

HiTomWhatistheimpactoftheLargeHighwatermarkorFREEBlocksorUNUSEDblockonthetableperformacne(INSERT/UPDATE/SELECT).Ihaveacaseinmyenvironmentwhereabatchtakes9minutesinoneinstance(EDEV)amdottakes2hrsinanother(EPROD).TheoutputsfromyrprocedureandfromALL_TABLESisgivenbelowforboththeinstance.EDEVFreeBlocks4TotalBlocks1984TotalBytes16252928UnusedBlocks1UnusedBytes8192LastUsedExtFileId112LastUsedExtBlockId5193LastUsedBlock15EPRODFreeBlocks20TotalBlocks2352TotalBytes19267584UnusedBlocks11UnusedBytes90112LastUsedExtFileId346LastUsedExtBlockId3065LastUsedBlock5BLOCKSEMPTY_BLOCKSAVG_SPACENUM_FREELIST_BLOCKS2340644622103selectblocks,empty_blocks,avg_space,num_freelist_blocksfromuser_tableswheretable_name=BACKLOG_ITEMBLOCKSEMPTY_BLOCKSAVG_SPACENUM_FREELIST_BLOCKS395611457020042.IfmyprocessdoesnotpreventmetoissueaTRUNCATEstatement,butmybatchinvolveslotsofDELETESandINSERT,thenhowcanIpreventlargevaluesforhighwatermark?.Dowehaveanyutility/scriptthatwecanruntoreducethehighwatermarkofatable.

Followup:
1)bothofthosetablesaresosmall,IfIhada"batch"thattook9minutestoprocess20megofdata,Idbeconcernedaboutthatinitself.Thatisprobably8minutesand30secondslongerthenitshouldbe.UseTKPROFandSQL_TRACEtofindyourlowhangingfruit.2)youhaveaveryverysmalltable,Ithinkyouarelookinginthewrongplacehere.Youhavemostlikelyabadalgorithm.Youneedtodiagnosewheretheproblemis,thenfixit.Not"fixsomething"andseeifitwastherightthing.

BookmarkReview|Bottom|TopThanksJune13,2003
Reviewer:RaghufromMilwaukee,WIUSA

HiTom,Thanksforyourresponse.IabsolutelyagreewithyouthattheperformanceofthebatchIhadgiveninmyexamplecouldbeimprovedbychangingthealgorithm.Iamsorrythatmyexampledivertedyoufromthe"real"reasonformycomment.IwaswonderinghowIcanmakeuseoftheoutputofyourqueryinmyDBmaintenenace.Forinstance.Isthereaco-relationbetweenthenumberofFreeblocksandperformance.Inmyoriginalexample,CanIattributethedifferenceinbatchperformancebetweentwoinstancetonumberofFreeblocks?(AgainIacceptthattheperformancecouldbeimprovedfurther).Anotherexample,Ihaveatablethathasaround6millionrows.TheaverageRowlengthis1250bytes.Wedolotsof"Delete/Insert/Update"onthistable.Werecentlyre-partitionedthistable(usingExport,CREATETableandImport).WenoticedahugeperformancechangesforSELECTS..Overtheperiodoftime,itperformanceisgoingbacktothe"Normal".CanIattributethistoFREEBlocksandDoyouthinkthatweshouldconstantlylookforsuchblocksand"cleanit"andhowcanwedoit?.Note:Atthistime,IamtryingtofindtheRootcausefortheperformancechanges.WhenIreadthisarticleofyoursIfeltthatImighthavediscovered"one"ofthecasuesandwantedaconfirmationfromyou.IalsoliketopointoutthattheDBareverysimilarintermsofsize,parameteretc.Thecodeisalsosame.

Followup:
therecouldbe--butinthiscase,thenumbersarefartoosmall.IthoughtitwasclearthatIdonotthinkthatthesmallnumberofblocksyouhaveonfreelistsherewouldhaveanymaterialaffectonanything....No,youcannotattributeanythingtoanythinghere.Thereisinsufficientdatatosayanythingreasonable.Youdontsaywhichwaytheperformancechangewas,whattypeofoperationsyoudoonthistable,nothing.Again,totallyinsufficientdatatosayanythingaboutanything(excepthypothesize,whichIdontlikedoingtoomuch).Now,ifyouhadsomemetrics(tkprofs!!!statspacks!!!)-hardnumerical,incontravertibledatapointsthatwouldprovideinformationastothenumberofconsistentgets,queryplans,inputs,etcovertime--then,well,thenwedbecookingwithgas...(but,theperformanceofqueriesisunaffectedbythenumberofblocksonthefreelist--Icouldsetpctfree/pctusedsothateveryblockisonthefreelist.Icouldsetthepctfree/pctusedsothatNOblockisonthefreelist.IcouldhaveBOTHofthesetables--onewitheveryblockonandotherwitheveryblockoff--organizedinsuchawaythatblockforblocktheyareineffect"thesame".Theywouldperformidentically.Itisnothingtodowithhowmanyblocksareonthefreelist(althoughyoumaybeabletousethatasanindicatorifyouunderstandhowpctfree/pctusedaresetandhowtheyareused).IthaseverythingtodowithhowmanyblocksOraclemustprocessinordertoansweryourqueries.YouneedaTKPROF.YouneedaTKPROFofthis"batch"ondev,ofthis"batch"onprod.Youneedtocomparethem.Thatllgetyou90%ofyourwayalonginyourrootcauseanalysis.

BookmarkReview|Bottom|TopDearTom,whatcouldbewronghere?July24,2003
Reviewer:SaminathanSeeranganfromNJ,USA

Tom,Iamnotabletouseshow_spaceprocedure.Pleasehelpmeout.SQL>createuseraidentifiedbya;Usercreated.SQL>grantcreatesession,createprocedure,2createtabletoa;Grantsucceeded.SQL>alteruseraquotaunlimitedonusers;Useraltered.SQL>connecta/aConnected.SQL>@D:hareoracleasktomhow_space.sql51/Procedurecreated.SQL>createtablet(xint)tablespaceusers;Tablecreated.SQL>execshow_space(T)BEGINshow_space(T);END;*ERRORatline1:ORA-10618:OperationnotallowedonthissegmentORA-06512:at"SYS.DBMS_SPACE",line74ORA-06512:at"A.SHOW_SPACE",line22ORA-06512:atline1SQL>desctNameNull?Type-----------------------------------------------------XNUMBER(38)SQL>insertintotvalues(100);1rowcreated.SQL>descdbms_spacePROCEDUREFREE_BLOCKSArgumentNameTypeIn/OutDefault?-------------------------------------------------------------------SEGMENT_OWNERVARCHAR2INSEGMENT_NAMEVARCHAR2INSEGMENT_TYPEVARCHAR2INFREELIST_GROUP_IDNUMBERINFREE_BLKSNUMBEROUTSCAN_LIMITNUMBERINDEFAULTPARTITION_NAMEVARCHAR2INDEFAULTPROCEDURESPACE_USAGEArgumentNameTypeIn/OutDefault?-------------------------------------------------------------------SEGMENT_OWNERVARCHAR2INSEGMENT_NAMEVARCHAR2INSEGMENT_TYPEVARCHAR2INUNFORMATTED_BLOCKSNUMBEROUTUNFORMATTED_BYTESNUMBEROUTFS1_BLOCKSNUMBEROUTFS1_BYTESNUMBEROUTFS2_BLOCKSNUMBEROUTFS2_BYTESNUMBEROUTFS3_BLOCKSNUMBEROUTFS3_BYTESNUMBEROUTFS4_BLOCKSNUMBEROUTFS4_BYTESNUMBEROUTFULL_BLOCKSNUMBEROUTFULL_BYTESNUMBEROUTPARTITION_NAMEVARCHAR2INDEFAULTPROCEDUREUNUSED_SPACEArgumentNameTypeIn/OutDefault?-------------------------------------------------------------------SEGMENT_OWNERVARCHAR2INSEGMENT_NAMEVARCHAR2INSEGMENT_TYPEVARCHAR2INTOTAL_BLOCKSNUMBEROUTTOTAL_BYTESNUMBEROUTUNUSED_BLOCKSNUMBEROUTUNUSED_BYTESNUMBEROUTLAST_USED_EXTENT_FILE_IDNUMBEROUTLAST_USED_EXTENT_BLOCK_IDNUMBEROUTLAST_USED_BLOCKNUMBEROUTPARTITION_NAMEVARCHAR2INDEFAULTSQL>

Followup:
createorreplaceprocedureshow_space(p_segnameinvarchar2,p_ownerinvarchar2defaultuser,p_typeinvarchar2defaultTABLE,p_partitioninvarchar2defaultNULL)--thisprocedureusesauthidcurrentusersoitcanqueryDBA_*--viewsusingprivilegesfromaROLEandsoitcanbeinstalled--onceperdatabase,insteadofonceperuserthatwantedtouseitauthidcurrent_userasl_free_blksnumber;l_total_blocksnumber;l_total_bytesnumber;l_unused_blocksnumber;l_unused_bytesnumber;l_LastUsedExtFileIdnumber;l_LastUsedExtBlockIdnumber;l_LAST_USED_BLOCKnumber;l_segment_space_mgmtvarchar2(255);l_unformatted_blocksnumber;l_unformatted_bytesnumber;l_fs1_blocksnumber;l_fs1_bytesnumber;l_fs2_blocksnumber;l_fs2_bytesnumber;l_fs3_blocksnumber;l_fs3_bytesnumber;l_fs4_blocksnumber;l_fs4_bytesnumber;l_full_blocksnumber;l_full_bytesnumber;--inlineproceduretoprintoutnumbersnicelyformatted--withasimplelabelprocedurep(p_labelinvarchar2,p_numinnumber)isbegindbms_output.put_line(rpad(p_label,40,.)||to_char(p_num,999,999,999,999));end;begin--thisqueryisexecuteddynamicallyinordertoallowthisprocedure--tobecreatedbyauserwhohasaccesstoDBA_SEGMENTS/TABLESPACES--viaaroleasiscustomary.--NOTE:atruntime,theinvokerMUSThaveaccesstothesetwo--views!--thisquerydeterminesiftheobjectisaASSMobjectornotbeginexecuteimmediateselectts.segment_space_managementfromdba_segmentsseg,dba_tablespacestswhereseg.segment_name=:p_segnameand(:p_partitionisnullorseg.partition_name=:p_partition)andseg.owner=:p_ownerandseg.tablespace_name=ts.tablespace_nameintol_segment_space_mgmtusingp_segname,p_partition,p_partition,p_owner;exceptionwhentoo_many_rowsthendbms_output.put_line(Thismustbeapartitionedtable,usep_partition=>);return;end;--iftheobjectisinanASSMtablespace,wemustusethisAPI--calltogetspaceinformation,elseweusetheFREE_BLOCKS--APIfortheusermanagedsegmentsifl_segment_space_mgmt=AUTOthendbms_space.space_usage(p_owner,p_segname,p_type,l_unformatted_blocks,l_unformatted_bytes,l_fs1_blocks,l_fs1_bytes,l_fs2_blocks,l_fs2_bytes,l_fs3_blocks,l_fs3_bytes,l_fs4_blocks,l_fs4_bytes,l_full_blocks,l_full_bytes,p_partition);p(UnformattedBlocks,l_unformatted_blocks);p(FS1Blocks(0-25),l_fs1_blocks);p(FS2Blocks(25-50),l_fs2_blocks);p(FS3Blocks(50-75),l_fs3_blocks);p(FS4Blocks(75-100),l_fs4_blocks);p(FullBlocks,l_full_blocks);elsedbms_space.free_blocks(segment_owner=>p_owner,segment_name=>p_segname,segment_type=>p_type,freelist_group_id=>0,free_blks=>l_free_blks);p(FreeBlocks,l_free_blks);endif;--andthentheunusedspaceAPIcalltogettherestofthe--informationdbms_space.unused_space(segment_owner=>p_owner,segment_name=>p_segname,segment_type=>p_type,partition_name=>p_partition,total_blocks=>l_total_blocks,total_bytes=>l_total_bytes,unused_blocks=>l_unused_blocks,unused_bytes=>l_unused_bytes,LAST_USED_EXTENT_FILE_ID=>l_LastUsedExtFileId,LAST_USED_EXTENT_BLOCK_ID=>l_LastUsedExtBlockId,LAST_USED_BLOCK=>l_LAST_USED_BLOCK);p(TotalBlocks,l_total_blocks);p(TotalBytes,l_total_bytes);p(TotalMBytes,trunc(l_total_bytes/1024/1024));p(UnusedBlocks,l_unused_blocks);p(UnusedBytes,l_unused_bytes);p(LastUsedExtFileId,l_LastUsedExtFileId);p(LastUsedExtBlockId,l_LastUsedExtBlockId);p(LastUsedBlock,l_LAST_USED_BLOCK);end;/trythatone,youmustbein9iusingASSMwiththeolderversionfrom8i--assmdoesnothavefreelists

BookmarkReview|Bottom|TopThankyousomuchJuly24,2003
Reviewer:SaminathanSeeranganfromNJ,USA

SmalltypointheaboveprocedureLine#46selectts.segment_space_managementappears2times.

Followup:
thanks--ifixedthatup

BookmarkReview|Bottom|TopAccesstodba_*viewsneededSeptember17,2003
Reviewer:DusanfromCzechrepublic

Well,thisisabitfrustrating!Iusedtousethisexcellentutility(show_space)atmanyclientsites(8i),toinvestigatespaceissues.Basically,everyschemamightuseit!Now,on9i,Ihavetoaskaccessforthoseviews.Isntitpossibletoworkaroundthis?

Followup:
insteadofmequeryingthedba_views,youcanpassinthatinformation(whattypeoftablespaceisitin)usethe"8i"likeversion,justparameterizeittocalltheproperAPI

BookmarkReview|Bottom|TopSeemsitdoesnotworkforpartitionedtableSeptember17,2003
Reviewer:DusanfromCzechrep.

ProblemsappearedwhenItriedtousethisutilityforpartitionedtable.1selectTABLE_OWNER,PARTITION_NAME2fromall_tab_partitions3wheretable_owner=VALASEKD4*andtable_name=DV_ALL_OBJECTS_PARTsys@DV9IR2.US.ORACLE.COM>/TABLE_OWNERPARTITION_NAME----------------------------------------------VALASEKDP030915VALASEKDP030916VALASEKDPMAX1selectts.segment_space_management2fromdba_segmentsseg,dba_tablespacests3whereseg.segment_name=DV_ALL_OBJECTS_PART4and(PMAXisnullor5seg.partition_name=PMAX)6andseg.owner=VALASEKD7*andseg.tablespace_name=ts.tablespace_namesys@DV9IR2.US.ORACLE.COM>/SEGMEN------AUTOsys@DV9IR2.US.ORACLE.COM>edZapsánosouborafiedt.buf1*execshow_space(DV_ALL_OBJECTS_PART,VALASEKD,TABLE,PMAX);sys@DV9IR2.US.ORACLE.COM>execshow_space(DV_ALL_OBJECTS_PART,VALASEKD,TABLE,PMAX);BEGINshow_space(DV_ALL_OBJECTS_PART,VALASEKD,TABLE,PMAX);END;*ERRORnaøádku1:ORA-03204:zadánítypusegmentubymìlovyznaèovatdìlenínapartitionORA-06512:na"SYS.DBMS_SPACE",line97ORA-06512:na"SYS.SHOW_SPACE",line64ORA-06512:naline1

BookmarkReview|Bottom|TopDIfferentExtentSizesOctober02,2003
Reviewer:KailashfromEastBrunswick,NJ

Thanksforawonderfulresponse.However,inresponsetoyourfirstreview,IamnotclearastowhytheDBA_EXTENTSshowsdifferentextentsizes(ie.thenumberofblocksintheextentare5,5,5,10,7atotalof32extents).Inthisexample,wedidnotspecifyanyPCTINCREASE.Somyunderstandingisthatallextentsmustbeofequalsizeand1oftheextentsmusthavelessspacecomparedtootherextentsbecauseofthe1insert.However,thequeryshowsadifferentresult.IwouldappreciateifyoucouldexplainmehowDBA_EXTENTScalculatedtheabovementionedextentsizes.

Followup:
didyoureadthefollowupIpostedwithalinktothedocs?whenyouaskfor40kinadictionarymanagedtablespaceandwefindafreeextentthatiswithin5blocksofyourrequest(eg:40k=5*8kblocks--anythingthatisfreeandhas5,6,7,8,9,10blockswillbeused).Thisistoavoidthesituationwherebyyouhaveafreeextentofsay7blocks--youaskedfor5,sowetake5leaving"2totallyuselessblocks"hangingoutthere.Ifthefreeextentwas11blocks--wewouldhavegivenyouyour5andleft6outthereforthenextguy.Otherwiseyouwouldendupwithlotsof1/2/3blockfreeextentsthatcouldntbeusedforanything.

BookmarkReview|Bottom|TopMoreexplanationpleaseJune01,2004
Reviewer:paragjayanatpatankarfromIndia

HiTom,Thanksforexplainingthisthingingreatway.Ihavedoneanalysisonmytableandfoundfollowing11:21:50test@prod>execshow_space(V9SPD140);FreeBlocks.............................4051TotalBlocks............................7418TotalBytes.............................30384128UnusedBlocks...........................0UnusedBytes............................0LastUsedExtFileId....................5LastUsedExtBlockId...................89500LastUsedBlock.........................3Iamconfusedwithabovedataasitisnotmatchingwithyourformulaasexplainedinthisthread:-ops$tkyte@8i>execshow_space(T)FreeBlocks.............................19TotalBlocks............................32TotalBytes.............................262144UnusedBlocks...........................12UnusedBytes............................98304LastUsedExtFileId....................1LastUsedExtBlockId...................64681LastUsedBlock.........................5PL/SQLproceduresuccessfullycompleted.Theaboveshowsthatthedeletesimplyputallofourblocksonthefreelist.Wehave19blocksonthefreelist+12unusedblocks+1systemblock=32blocks.InmydataTotalBlocksare:7418=Blocksonfreelist:4051+UnusedBlocks:0+SystemBlock:1Thenwhereareotherblocksgone?TotalblocksshouldbematchedasperyourformulaKindlyexplainasIhavemissedoutsomeimppart.thanks&regardsparagjayantpatankar

Followup:
theotherblockshavedataonthemandjustarenotonthefreelist(notavailableforinserts)

BookmarkReview|Bottom|TopItsamazingwhatyoufindwhenyouactuallyreadthedocumentation..;)July13,2004
Reviewer:GregfromToronto,Ontario

Funnythat...Iwashavingtroublewithsomeprivilegerightsonthisdbms_spacepackageandpullingmyhairout..Ifinallycomehereanddoasearch....DoIfindmyproblemconvenientlyansweredbyTom?No.WhatdoIgetinstead:"haveyoureadaboutthedbms_*packagesIuse(theyaredocumented).Itisprettystraightforwardstuff."...right,soaslapintheface,andtheanswersittingrightundermynoseinthedocs...hehe*sigh*...Thanks,Tom!!..Ineededthat!!:)

BookmarkReview|Bottom|TopGoodexplanationAugust02,2004
Reviewer:CyrillefromFRANCE

Thanksforallyourexplanation.

BookmarkReview|Bottom|TopHWMbeloworabove?August26,2004
Reviewer:RDfromNZ

HiTom,Atthebeginningofthethreadyousaid"Truncateit.ThatputsalloftheblocksbelowtheHWM.Nowwehave31unusedblocks+1systemblock=32blockstotal.Noneonthefreelistsincenoneofthemhaveanydata."Shouldthathavebeen"thatputsalloftheblocksABOVEtheHWM"costruncatewillalwayslowertheHWMoramIwrong??Thanks,Regards,RD.

Followup:
correct,theHWMwouldnoteven"exist"really.

BookmarkReview|Bottom|TopFreeSpaceAugust30,2004
Reviewer:NazmulHoquefromBangladesh

DearTom,INeedYourHelp,IamuseingOracle8.1.7,IhavethreeTablespaceinmydatabasesizeare38MB,204MB&51MB,Butmydatabaseuseingspacefrommyservermorethan3GB.AndfilesizeTemp01.dbfis1.19GBandusers01.dbffileis1.17GB.Myideathisisabnormal,Pleasehelpmetofreespacefrommyserver.Thanks/Hoque

Followup:
doyouhaveadbayoucanworkwith?youcontrolthesizeoftemp,thatisentirelyuptoyou.ifyoudontliketempbeingsolarge,dropitandrecreateit.searchthissiteformaxshrink,itlltellyouifyoucanshrinkyourusers01.dbffile.itisnormalforadatabasetoconsumespace.youhaveindexes,youhavefreespaceinthere.3gigistiny.

BookmarkReview|Bottom|TopQuestionsAugust30,2004
Reviewer:Areader

1.WhydoyouhavetheEXCEPTIONWHENOTHERSTHENNULLinyourcodeabove?Youareverymuchagainsthidingexceptions,arentyou?2.IunderstandtheAUTHIDCURRENT_USERpartsothatitcanbeinstalledonceperdatabaseandeachcallinguserdoesntgetanyextraprivilegesduetoitbeingdefinersright.ButhowdoesAUTHIDCURRENT_USERaddress"thisprocedureusesauthidcurrentusersoitcanqueryDBA_*viewsusingprivilegesfromaROLE"3."--thisqueryisexecuteddynamicallyinordertoallowthisproceduretobecreatedbyauserwhohasaccesstoDBA_SEGMENTS/TABLESPACESviaaroleasiscustomary"Instead,whynotsimplyusetheALL_*viewssothattheEXECUTEIMMEDIATEisnotneededatall?Everyusercallingthisprocautomaticallyseeswhattheyaresupposedtoseei.e.theALL_*viewsdoalltheheavyliftinginsteadofus?4.WhatbenefitisPIPELINEDgivingusinthe9iR2versionofthisutility?5.WhyisthePRAGMAAUTONOMOUS_TRANSACTIONneeded?Thanks

Followup:
1)becausehereIhaveasimplereport,ratherthenblowuppartwaythruonanobjectthatcannotbe"freespacereported"oniskipit.2)l_sql:=selectowner,segment_name,segment_type,partition_namefromdba_segments;add_predicate(segment_name,p_segname);add_predicate(owner,p_owner);add_predicate(segment_type,p_type);add_predicate(partition,p_partition);executeimmediatealtersessionsetcursor_sharing=force;openl_cursorforl_sql;executeimmediatealtersessionsetcursor_sharing=exact;Iuseddynamicsql--sotheCREATORoftheprocedureneednothaveDBA_*access,justtheINVOKERoftheprocedure.andtheycanusetheprivsfromarolesincerolescanbeinplaceduringtheexecutionofaninvokersrightsroutine.3)becausethiswaswrittentobeaDBA_utility?DBA_viewshavenofiltersonthem.4)moreefficient,searchforpipelinedformanyexamples5)itisnt,dontknowwhatIwasthinkingleavingitin.

BookmarkReview|Bottom|TopFreeSpace(TableSpace)August31,2004
Reviewer:NazmulHoquefromDhaka-Bangladesh

**doyouhaveadbayoucanworkwith?IamtheprogrammerandDBAboth!!!****youcontrolthesizeoftemp,thatisentirelyuptoyou.ifyoudontliketempbeingsolarge,dropitandrecreateit.PleaseAdvisemehowIDROPandRRCREATE,Tempiscreatedbythestystem,IfIdtopthat,isitcreateanyproblem?***searchthissiteformaxshrink,itlltellyouifyoucanshrinkyourusers01.dbffile.Pleasewritewithdetails.....***itisnormalforadatabasetoconsumespace.youhaveindexes,youhavefreespaceinthere.3gigistiny.Inmyservernowonly400MBisfreeonthatdrive.PLEASEHELPME....ThanksNazmulHoque

Followup:
dbaandprogrammerbothisgoingtobeaproblem.thisisstraightforwarddbastuffreally.todownsizeyourtemp,createnew:ops$tkyte@ORA9IR2>createtemporarytablespacetemptempfilesize100m;Tablespacecreated.andcreatenewops$tkyte@ORA9IR2>droptablespacetemp2;Tablespacedropped.thedetailsonmaxshrinkareasfollows:searchthissiteformaxshrink,readthefirstcoupleofpagesyoufind.perhapsyouneed"moredisk".

BookmarkReview|Bottom|TopdefineSeptember02,2004
Reviewer:ABC

TomPleaseprovidesinglelinedefinitioofHWM

Followup:
HighWaterMark--thebiggestthetableeverwas,HWMsarepushed"up"ifyoufillupatablewith1,000blocksofdataandthendeleteallofthedata--therewillstillbe1,000blocksof"data"therethatwouldbefullscanned.

BookmarkReview|Bottom|TopTempTablespaceCreateandDropSeptember02,2004
Reviewer:NazmulHoquefromBangladesh,Dhaka

dbaandprogrammerbothisgoingtobeaproblem.thisisstraightforwarddbastuffreally.--Nootheroptiontodownsizeyourtemp,createnew:ops$tkyte@ORA9IR2>createtemporarytablespacetemptempfilesize100m;Tablespacecreated.andcreatenew---gettingbelowmessage:SQL>createtemporarytablespacetemptempfilesize100m;createtemporarytablespacetemptempfilesize100m*ERRORatline1:ORA-02236:invalidfilename****Pleasehelpme****ops$tkyte@ORA9IR2>droptablespacetemp2;Tablespacedropped.thedetailsonmaxshrinkareasfollows:searchthissiteformaxshrink,readthefirstcoupleofpagesyoufind.perhapsyouneed"moredisk".***WEareusingserverCompaqProliant1600,NowadaysitsodifficaulttoaddHDDatMyend.****DearTom,IknowhowcreatetablespaceanddroptabelspacefromSQLandFromDBAStudioboth,MyproblemisTemptablespace,InevercreateTemptablespaceordropbefore.SofarIhaveseenitiscreatedbythesystemattimeofinstallation.IfidropandRecreatetempwith100MBspacemyproblemwillbesolved,PleasegivesomemoretripstoDropandCreatTempTablespace.PleasePleasePlease..ThanksNazmulHoque

Followup:
youllneedtorefertoyoursqlreference,Icannot(willnot)beyourdba.youareusing817,youdidnthaveOracleManagedFileswaybackthen.Youneedtonameyourtempfile.Thesqlreference(availableonotn.oracle.com)willgiveyouthesyntax.

BookmarkReview|Bottom|TopTEMPORARYTABLESPACESeptember03,2004
Reviewer:NAZMULHOQUEfromBangladesh

MYTABLESPACELISTASBELOW:================================TABLESPACEINITIAL_EXTENTNEXT_EXTENTMIN_EXTENTSMAX_EXTENTSPCT_INCREASEMIN_EXTLENSTATUS---------------------------------------------------------------------------------------SYSTEM655366553612.147E+095065536ONLINERBS5242885242888409650524288ONLINEUSERS131072131072140960131072ONLINETEMP65536655361065536ONLINETOOLS327683276814096032768ONLINEINDX131072131072140960131072ONLINEDRSYS655366553612.147E+095065536ONLINEDHAKA40960409601505500ONLINESSLDATA40960409601505500ONLINE================================NORMALYWEUSEBELOWCOMMANDFROMSQLTOCREATEATABLESPACE(PATHMENTIONEDASPEROURSERVERLOCATION):CREATETABLESPACETESTDATAFILED:ORACLEORADATANTSERVERDATA1.DATSIZE100MAUTOEXTENDONNEXT10MSHALLIUSEBELOWFORTEMPORARYTABLESPACE==========================================CREATETEMPORARYTABLESPACETEMPDATAFILED:ORACLEORADATANTSERVERTEMP01.DBFSIZE100MAUTOEXTENDONNEXT5MTODROPTABELSPACE:DropTablespaceTEST;FORTEMPORARYTABLESPACE:========================DropTEMPORARYTablespaceTEMP;DEARTOM,PLEASEADVISEME,IAMDOINGWRONGORRIGHT.IFWRONGTHANPLEASEWRITEONLYTWOLINESTOCORRECTME.THANKSNAMZULHOQUE

Followup:
createtemporarytablespacetempTEMPFILE............^^^^^^^^tempfile,notdatafile.documentationisveryusefulforthisstuff
http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/statem5e.htm#2063472
(hint,hittheshiftlockkey...)


由MySQL用来存储数据的文件格式以已经被广泛地测试过，但是总是有外部情况可以导致数据库表被破坏：

比如，MicrosoftSQLServer2008的某一个版本可以满足现在的这个业务的需要，而且价格还比Oracle11g要便宜，那么这一产品就是适合的。

where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

两个月啃那本sqlserver2005技术内部-存储引擎，花了几个月啃四本书

但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

如果我们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个SQL语句会改变一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。

如果我们从集合论（关系代数）的角度来看，一张数据库的表就是一组数据元的关系，而每个SQL语句会改变一种或数种关系，从而产生出新的数据元的关系（即产生新的表）。

这一点很好的加强了profiler的功能。但是提到profiler提醒大家注意一点。windows2003要安装sp1补丁才能启动profiler。否则点击没有反应。

语句级快照和事务级快照终于为SQLServer的并发性能带来了突破。个人感觉语句级快照大家应该应用。事务级快照，如果是高并发系统还要慎用。如果一个用户总是被提示修改不成功要求重试时，会杀人的！