DefineDatabaseObjectsDefineDatabaseObjectsOBJECTIVESDefineTablespacesDefineTablesandIndexesDefineSegments,Extents,andBlocksDefineDatabaseObjectsOverviewDatabaseTablespaceTables,indexes,etc.DatafilesSegmentExtentBlocksTablespacesLargestlogicalunitofspaceConsistofoneormoredatafilesSYSTEMtablespacemustexistTablespaceTypesofTablespacesSystem/NonsystemOnline/OfflineRead-Write/Read-OnlyTransportableSystemandNonsystemTablespacesSystemAtleastoneDataDictionaryRollbackNonsystemFacilitatesAdministrationUser/ApplicationData,IndexesRollbackandTemporarySegmentsOnlineandOfflineTablespacesOnlineAvailabletoapplicationsTypicallytablespacesonlineOfflineNotavailabletousers,evenifdatabaseisavailableUseformaintenance,troubleshooting,orhistoricaldatanolongerneededOnlineOfflineRead-WriteandRead-OnlyTablespacesRead-WriteCreate,alter,anddropdatabaseobjectsDefaultRead-OnlyCannotcreateoralterdatabaseobjectsCandropcertainobjectsCanresideonWormdeviceorCDROMEliminateredundantdatabackupsIdealforDataWarehouseapplicationsandLookuptablesRead-WriteRead-OnlyTipsforRead-OnlyTablespacesOrganizebymodificationrequirementsFrequencyofdatachangeBackuprequirementSecurityrequirementUserrequirementAssociateddatafilecannotbe:ResizedorhaveautoextendsettoonRenamedtoanewdatafilenameordirectoryOfflineTransportableTablespacesSystemTablespaceTransportableTablespaceSystemTablespaceExportImportCopyUtilityTransportableTablespaceSourceDatabaseTargetDatabaseUsesofTransportableTablespacesArchiveOLTPanddatawarehousesystemsTransferdatafromOLTPtodatawarehousestagingsystemsLoadingdatamartsfromcentraldatawarehousesPublishdataMoveindexdataandavoidrebuildsTablesStoredinTablespacesOrganizedinrowsandcolumnsContaindataTablespaceTables,indexes,etc.DatafilesSegmentExtentBlocksTableDatatypesCHARVARCHAR2NUMBERDATALOBCustomersNameAddressPhone…..George..123Cherry555-5555LargeObjectDatatypeStoreUnstructuredInformationTextdocumentsStaticimagesVideoAudioCustomersNameAddress...PhotoGreetingGeorge..123CherryLOBTypesCLOBstorescharacterdataNCLOBstoresdouble-bytecharacterdataBLOBstoresbinarydata:graphics,video,soundBFILEstorespointerstoexternalfilesExternalFileSystemCustomersName…HistoryPhotoGreetingGeorgeExternalFileSystemLOBLocatorsLOBdatastoredseparatefromnon-LOBdataBFILEdatastoredseparatefromdatabaseTablespaceATableNameAdd…*(LocatorspointtoLOBandBFILELocations)TablespaceBTemporaryTablesStoresession/transactionspecificdataShowdataonlytocreatingsessionDiscarddataoncommitorpreservedatauntilsessionendsTablespaceATemp01Temp02CustomersIndex#Name1AcmeInc.2Zachary’s3Marie’s4ChimneyIndexesOptimizequeriesROWID(Block#.Row#.File#)StoredindependentoftablesConsumeminimalspaceReduceI/OIndexonWHEREclausecolumnsCustomersTable#NameAddre..3Marie’s4456Si1AcmeInc. 1236Ch4Chimney9024F2Zachary’s 1234BrIndexTypesUniqueDuplicatesnotallowedNon-uniqueDuplicatesallowedXIndexTypesSingleColumnIndex RowidOrderNo Block#.Seq#.File#007000 0000A.0004.0003007000 0000A.0005.0003OrderNoItemNoCust.006989 001 ABC006989 002 XYZ006989 003 USA007000 001 USA007000 002 GBSI007000 003 TEST007000 004 XYZ007234 005 WESTORDERTABLEIndexTypesMulti-ColumnConcatenated,compositeIndexRowidOrderNoItemNoBlock#.Seq#.File#006989 001 0000A .0005 .0003006989 002 0000A .0006 .0003OrderNoItemNoCust.007000001USA007000002GBSI007000003TEST007234005WEST006989001ABC006989002XYZORDERTABLEBalancedIndexTree(B*Tree)ROOTBRANCHROWIDPOINTERDATASEGMENT0000020004000000020002001249930003001400149995000700090005000599970007200810090009999ROWID|1/4”RolledSheet|100Feet|5Days|C.O.D.LEAFBitmapIndexBitmapcreatedforeachcolumnvalueCommonUsageVeryLargeTablesLow-CardinalityColumnsFeaturesRequiresminimalstorageImprovesperformance‘F’BILLLISAANNTED10010110‘M’GENDERFunctionBasedIndexCreateonfunctionsorexpressionsUsergeneratedPL/SQLSQLfunctions(non-aggregate)Externalprogram(Ccallout)600500300250900660550330275990SALARY*1.10SALARYFunction-BasedSingleSQL>SELECTsalary,salary*1.10FROMemployees;PartitionDataDecomposetablesandindexesEachpartitioncanbestoredindifferenttablespaceCustomerCustNoCustNameState…1KodakNYOracleCAUSSteelPA6QuestWA7MotorolaTXCustomersAL…INCustomersKS…NYCustomersOH…WYAdvantagesofPartitionsReducetimefordataloadsandindexcreationIncreaseavailabilityofmission-criticaldatabasesCreatepartitionindependenceEnableconcurrentoperationsonseparatepartitionsExecutequeriesonpartitionsinsteadoffulltablescanAdvantagesofPartitionsControldatastorageacrossphysicaldevicesMaintainpartitiontransparencyProgramsdonotneedtoreferencepartitionsProgramscantakeadvantageofpartitionsEquipartitionsDatabaseobjectshavesamelogicalpartitionsExamplesMasteranddetailtableTableandindexOrderTableItemTableCustomerTableCustomerbyStateGlobalPartitionedIndexesCanimproveperformanceCancontainkeysfromallpartitionsofcorrespondingtableCustomerSalesIndexCustomersAL……INCustomersKS……NYCustomersOH……WYIndexOrganizedTablesDatastoredwithB*TreeindexTablemusthaveprimarykey,whichactsasindexAppropriateforcomplexorunstructureddataSpatialOLAPDataDataData90009999DataDataDataDataDataData3001400149990000020004000DataDataData5000700090005000799900002999TablespaceandDatafileRelationshipTablespaceTables,indexes,etc.DatafilesSegmentExtentAtablespaceiscreatedwithoneormoredatafilesDatafilesaremadeupofblocksBlocksStorageObjectandSegmentRelationshipCreateastorageobjectintablespaceTables,indexes,etc.arepreallocatedtosegmentsSegmentExtentSegmentMySegmentBlocksDatabaseSegmentsSetofextentscontainingspecifictypesofdataBootstrapSegmentTemporarySegmentRollbackSegmentIndexSegmentDataSegmentExtentExtentExtentDataSegmentsStoresrowsforTablesTablefeaturesContainsuserdataContainsDataDictionarydataRowFeaturesContainunlimitednumberofrowsSpanblocks(chaining)ColumnsstoredsidebysideBootstrapSegmentRollbackSegmentIndexSegmentDataSegmentIndexSegmentsCreatedforeveryindexExistasseparatephysicalsegmentsfromdatasegmentUsuallymuchsmallerthanassociateddatasegmentBootstrapSegmentRollbackSegmentIndexSegmentIndexSegmentRollbackSegmentsRecordbeforeimageofmodifieddataAllowchangestobeundonebeforecommitSupportreadconsistencySupportdataconcurrencySupportdatabaserecoveryASYSTEMrollbacksegmentisrequiredBootstrapSegmentRollbackSegmentIndexSegmentRollbackSegmentRollbackSegmentTypesPrivateMustincludesegmentnameinROLLBACK_SEGMENTSparameterPublicApoolofrollbacksegmentsthatmaybeacquiredbyanyinstancemountingdatabaseDeferredCreatedinSYSTEMtablespacewhentablespacetakenofflineandtransactionscannotberolledbackRollbackSegmentUsageUndodataiswrittentosegmentEachextentsupportsmultipletransactionsEachtransactionwritestoitsownblock(s)Ifextentfills,automaticallyextendsIfallextentsfill,segmentwrapsIfsegmentwraps,undodataoverwrittenIfundodatanotavailable,erroroccurs“Snapshottooold”“Cannotallocateextent”ReadConsistencyWhenqueryexecutes:CurrenttimedeterminedBlockswithsametimereadNewerblocksreconstructedwithrollbacksegmentsCustomers#NameAddress…1AcmeInc.123Ch2Zachary’’s1234Br3Marie’s4456Si4Chimney9024FSELECT*FROMcustomerUPDATEcustomer...TemporarySegmentsProvidetemporaryworkspaceCreatedautomaticallyforjoins,indexes,sortsCreatedondiskwhenmemoryfullSizedbyDEFAULTSTORAGEclauseoftablespaceReclaimedbySMONBootstrapSegmentRollbackSegmentIndexSegmentTemporarySegmentBootstrapSegmentContainsdictionarydefinitionsloadedonopenIsnotuseraccessibleResidesinSYSTEMtablespaceRequiresnoDBAmaintenanceBootstrapSegmentRollbackSegmentIndexSegmentBootstrapSegmentDatabaseExtentsConsistofaset