1表空间及其管理文档格式.docx
《1表空间及其管理文档格式.docx》由会员分享，可在线阅读，更多相关《1表空间及其管理文档格式.docx（17页珍藏版）》请在冰豆网上搜索。
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
∙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
升级会员 