1表空间及其管理.docx
《1表空间及其管理.docx》由会员分享,可在线阅读,更多相关《1表空间及其管理.docx(17页珍藏版)》请在冰豆网上搜索。
1表空间及其管理
Src:
Oracle8iConcepts
TablespacesandDatafiles
∙IntroductiontoDatabases,Tablespaces,andDatafiles
∙Tablespaces
∙Datafiles
IntroductiontoDatabases,Tablespaces,andDatafiles
●Oraclestoresdatalogicallyintablespacesandphysicallyindatafilesassociatedwiththecorrespondingtablespace.
databasesandtablespaces
AnOracledatabaseconsistsofoneormorelogicalstorageunitscalledtablespaces
tablespacesanddatafiles
EachtablespaceinanOracledatabaseconsistsofoneormorefilescalleddatafiles
databasesanddatafiles
Adatabase'sdataiscollectivelystoredinthedatafiles
AllocatingMoreSpaceforaDatabase(threeways)
∙Addanewtablespace
∙Addadatafiletoatablespace
∙Increasethesizeofadatafile
Tablespaces
●Tablespacesaredividedintologicalunitsofstoragecalledsegments,whicharefurtherdividedintoextents.
∙TheSYSTEMTablespace
∙UsingMultipleTablespaces
∙SpaceManagementinTablespaces
∙OnlineandOfflineTablespaces
∙Read-OnlyTablespaces
∙TemporaryTablespaces
∙TransportingTablespacesbetweenDatabases
TheSYSTEMTablespace
●EveryOracledatabasecontainsatablespacenamedSYSTEM,whichOraclecreatesautomaticallywhenthedatabaseiscreated.
TheDataDictionary
●TheSYSTEMtablespacecontainsthedatadictionarytablesforthedatabase.Thedatadictionarytablesarestoredindatafile1.
PL/SQLProgramUnits
●AlldatastoredonbehalfofstoredPL/SQLprogramunits(procedures,functions,packages,andtriggers)residesintheSYSTEMtablespace.
UsingMultipleTablespaces
●AsmalldatabasemightneedonlytheSYSTEMtablespace
●Youshouldcreateatleastoneadditionaltablespacetostoreuserdataseparatefromdatadictionaryinformation
●Youcanusemultipletablespacesto:
1.Controldiskspaceallocationfordatabasedata
2.Assignspecificspacequotasfordatabaseusers
3.Controlavailabilityofdatabytakingtablespacesonlineoroffline
4.Performpartialdatabasebackuporrecoveryoperations
5.Allocatedatastorageacrossdevicestoimproveperformance
SpaceManagementinTablespaces
●Tablespacesallocatespaceinextents.Tablespacescanusetwodifferentmethodstokeeptrackoftheirfreeandusedspace.
●Whenyoucreateatablespace,youchooseoneofthesemethodsofspacemanagement.Youcannotalterthemethodatalatertime.
Dictionary-ManagedTablespaces(defaultmethod)
●Oracleupdatestheappropriatetablesinthedatadictionarywheneveranextentisallocatedorfreedforreuse.
●Oraclealsostoresrollbackinformationabouteachupdateofthedictionarytables.(why?
)
Locally-ManagedTablespaces
●Atablespacemaintainsabitmapineachdatafiletokeeptrackofthefreeorusedstatusofblocksinthatdatafile.Eachbitinthebitmapcorrespondstoablockoragroupofblocks.
●Oracledonotgeneraterollbackinformationbecausetheydonotupdatetablesinthedatadictionary.
OnlineandOfflineTablespaces
●AdatabaseadministratorcanbringanytablespaceotherthantheSYSTEMtablespaceonline(accessible)oroffline(notaccessible)wheneverthedatabaseisopen.
●TheSYSTEMtablespaceisalwaysonlinewhenthedatabaseisopenbecausethedatadictionarymustalwaysbeavailabletoOracle.
●Adatabaseadministratormighttakeatablespaceoffline:
1.Tomakeaportionofthedatabaseunavailable,whileallowingnormalaccesstotheremainderofthedatabase
2.Toperformanofflinetablespacebackup(althoughatablespacecanbebackedupwhileonlineandinuse)
3.Tomakeanapplicationanditsgroupoftablestemporarilyunavailablewhileupdatingormaintainingtheapplication
●Youcannottakeatablespaceofflineifitcontainsanyrollbacksegmentsthatareinuse.
Read-OnlyTablespaces
●Theprimarypurposeofread-onlytablespacesistoeliminatetheneedtoperformbackupandrecoveryoflarge,staticportionsofadatabase.(CDROMsorWORMdrives:
一写多读).
●TheALTERTABLESPACE...READONLYstatementplacesthetablespaceinatransitionalread-onlymode.
●TheALTERTABLESPACE...READWRITEstatementmakesaread-onlytablespaceread-writeagain.
●Makingatablespaceread-onlydoesnotchangeitsofflineoronlinestatus.Offlinedatafilescannotbeaccessed.
TemporaryTablespaces
●Youcanmanagespaceforsortoperationsmoreefficientlybydesignatingtemporarytablespacesexclusivelyforsorts.
●Alloperationsthatusesorts--includingjoins,indexbuilds,ordering(ORDERBY),thecomputationofaggregates(GROUPBY),andtheANALYZEstatementforcollectingoptimizerstatistics--benefitfromtemporarytablespaces.
SortSegments
●Atemporarytablespacecanbeusedonlyforsortsegments.Nopermanentschemaobjectscanresideinatemporarytablespace.
CreatingandAlteringTemporaryTablespaces
●YoucreatetemporarytablespacesbyusingtheCREATETABLESPACEorCREATETEMPORARYTABLESPACEstatement:
1.Foradictionary-managedtemporarytablespace,usetheTEMPORARYclauseofCREATETABLESPACE.
2.Foralocallymanagedtemporarytablespace,useCREATETEMPORARYTABLESPACE.ThisstatementspecifiesTEMPFILESinsteadofDATAFILES.
●YoucanalsochangeatablespacefromPERMANENTtoTEMPORARYorviceversabyusingtheALTERTABLESPACEstatementforanytemporarytablespace.
TransportingTablespacesbetweenDatabases
●ThetransportabletablespacefeatureenablesyoutomoveasubsetofanOracledatabasefromoneOracledatabasetoanother.
●Movingdatabytransportingtablespacescanbeordersofmagnitudefasterthaneitherexport/importorunload/loadofthesamedata(onlycopyingdatafilesandintegratingthetablespacemetadata).
●Inthecurrentrelease,youcantransporttablespacesonlybetweenOracledatabasesthatusethesamedatablocksizeandcharacterset,andthatrunoncompatibleplatformsfromthesamehardwarevendor.
BenefitsofTransportingTablespaces
∙Datawarehouses
∙Datamarts
∙Datapublication
DataWarehousesandDataMarts
●Anenterprisedatawarehousecontainshistoricaldetaileddataaboutthecompany.
●Adatamartcontainsasubsetofcorporatedatathatisofvaluetoaspecificbusinessunit,department,orsetofusers.
DataPublication
●Contentprovidersacquiredataandmakeitavailableinausefulformat.
Datafiles(SELF)
●Oraclecreatesadatafileforatablespacebyallocatingthespecifiedamountofdiskspaceplustheoverheadrequiredforthefileheader.
●Whenadatafileiscreated,theoperatingsysteminwhichOracleisrunningisresponsibleforclearingoldinformationandauthorizationsfromafilebeforeallocatingittoOracle.
DatafileContents
●Thedataassociatedwithschemaobjectsinatablespaceisphysicallystoredinoneormoreofthedatafilesthatconstitutethetablespace.
●Aschemaobjectcanspanoneormoredatafiles.Unlesstablestripingisused(wheredataisspreadacrossmorethanonedisk),thedatabaseadministratorandenduserscannotcontrolwhichdatafilestoresaschemaobject.
SizeofDatafiles
●Youcanalterthesizeofadatafileafteritscreationoryoucanspecifythatadatafileshoulddynamicallygrowasschemaobjectsinthetablespacegrow.
OfflineDatafiles
●Youcantaketablespacesoffline(makeunavailable)orbringthemonline(makeavailable)atanytime,exceptfortheSYSTEMtablespace.
SRC:
Oracle8iAdministrator'sGuide
ManagingTablespaces(Howtodo—self)
∙GuidelinesforManagingTablespaces
∙CreatingTablespaces
∙ManagingTablespaceAllocation
∙AlteringTablespaceAvailability
∙Read-OnlyTablespaces
∙DroppingTablespaces
∙TransportingTablespacesBetweenDatabases
∙ViewingInformationAboutTablespaces
GuidelinesforManagingTablespaces
∙UseMultipleTablespaces
∙SpecifyTablespaceStorageParameters
∙AssignTablespaceQuotastoUsers
UseMultipleTablespaces
Youcanusemultipletablespacestoperformthefollowingtasks:
∙Separateuserdatafromdatadictionarydatatoreducecontentionamongdictionaryobjectsandschemaobjectsforthesamedatafiles.
∙Separateoneapplication'sdatafromanother'stopreventmultipleapplicationsfrombeingaffectedifatablespacemusttobetakenoffline.
∙Storedifferenttablespaces'datafilesonseparatediskdrivestoreduceI/Ocontention.
∙Separaterollbacksegmentdatafromuserdata,preventingasinglediskfailurefromcausingpermanentlossofdata.
∙Takeindividualtablespacesofflinewhileothersremainonline,providingbetteroverallavailability.
∙Reserveatablespaceforaparticulartypeofdatabaseuse,suchashighupdateactivity,read-onlyactivity,ortemporarysegmentstorage;thusallowingyoutooptimizeusageofthetablespace.
∙Backupindividualtablespaces.
SpecifyTablespaceStorageParameters
●Storageparametersspecifiedwhenanobjectiscreatedoverridethedefaultstorageparametersofthetablespacecontainingtheobject.
●Ifyoudonotspecifystorageparameterswhencreatinganobject,theobject'ssegmentautomaticallyusesthedefaultstorageparametersforthetablespace.
●Youcanalsoalteryourdefaultstorageparametersatalatertime.
AssignTablespaceQuotastoUsers
●Granttouserswhowillbecreatingtables,clusters,snapshots,indexes,andotherobjectstheprivilegetocreatetheobjectandaquota(spaceallowanceorlimit)inthetablespace.
CreatingTablespaces
●Tocreateanewtablespace,usetheSQLstatementCREATETABLESPACEorCREATETEMPORARYTABLESPACE.YoumusthavetheCREATETABLESPACEsystemprivilegetocreateatablespace.
●YoucanusetheALTERTABLESPACEorALTERDATABASEstatementstoalterthetablespace.YoumusthavetheALTERTABLESPACEorALTERDATABASEsystemprivilege.
●Youcanalsocreatetemporarytablespaces,whichcanbeeitherdictionary-managedorlocallyman