Copyright 2001GlobalBusinessSolutions Inc DefineDatabaseObjects Copyright 2001GlobalBusinessSolutions Inc DefineDatabaseObjects OBJECTIVESDefineTablespacesDefineTablesandIndexesDefineSegments Extents andBlocks Copyright 2001GlobalBusinessSolutions Inc DefineDatabaseObjectsOverview Database Tablespace Tables indexes etc Datafiles Blocks Copyright 2001GlobalBusinessSolutions Inc Tablespaces LargestlogicalunitofspaceConsistofoneormoredatafilesSYSTEMtablespacemustexist Tablespace Copyright 2001GlobalBusinessSolutions Inc TypesofTablespaces System NonsystemOnline OfflineRead Write Read OnlyTransportable Copyright 2001GlobalBusinessSolutions Inc SystemandNonsystemTablespaces SystemAtleastoneDataDictionaryRollbackNonsystemFacilitatesAdministrationUser ApplicationData IndexesRollbackandTemporarySegments Copyright 2001GlobalBusinessSolutions Inc OnlineandOfflineTablespaces OnlineAvailabletoapplicationsTypicallytablespacesonline OfflineNotavailabletousers evenifdatabaseisavailableUseformaintenance troubleshooting orhistoricaldatanolongerneeded Online Offline Copyright 2001GlobalBusinessSolutions Inc Read WriteandRead OnlyTablespaces Read WriteCreate alter anddropdatabaseobjectsDefault Read OnlyCannotcreateoralterdatabaseobjectsCandropcertainobjectsCanresideonWormdeviceorCDROMEliminateredundantdatabackupsIdealforDataWarehouseapplicationsandLookuptables Read Write Read Only Copyright 2001GlobalBusinessSolutions Inc TipsforRead OnlyTablespaces OrganizebymodificationrequirementsFrequencyofdatachangeBackuprequirementSecurityrequirementUserrequirementAssociateddatafilecannotbe ResizedorhaveautoextendsettoonRenamedtoanewdatafilenameordirectoryOffline Copyright 2001GlobalBusinessSolutions Inc TransportableTablespaces SystemTablespace TransportableTablespace SystemTablespace ExportImport CopyUtility TransportableTablespace SourceDatabase TargetDatabase Copyright 2001GlobalBusinessSolutions Inc UsesofTransportableTablespaces ArchiveOLTPanddatawarehousesystemsTransferdatafromOLTPtodatawarehousestagingsystems LoadingdatamartsfromcentraldatawarehousesPublishdataMoveindexdataandavoidrebuilds Copyright 2001GlobalBusinessSolutions Inc Tables StoredinTablespacesOrganizedinrowsandcolumnsContaindata Tablespace Tables indexes etc Datafiles Blocks Copyright 2001GlobalBusinessSolutions Inc TableDatatypes CHARVARCHAR2NUMBERDATALOB CustomersNameAddressPhone George 123Cherry555 5555 Copyright 2001GlobalBusinessSolutions Inc LargeObjectDatatype StoreUnstructuredInformationTextdocumentsStaticimagesVideoAudio CustomersNameAddress PhotoGreetingGeorge 123Cherry Copyright 2001GlobalBusinessSolutions Inc LOBTypes CLOBstorescharacterdataNCLOBstoresdouble bytecharacterdata BLOBstoresbinarydata graphics video soundBFILEstorespointerstoexternalfiles ExternalFileSystem CustomersName HistoryPhotoGreetingGeorge Copyright 2001GlobalBusinessSolutions Inc ExternalFileSystem LOBLocators LOBdatastoredseparatefromnon LOBdata BFILEdatastoredseparatefromdatabase TablespaceA TableNameAdd LocatorspointtoLOBandBFILELocations TablespaceB Copyright 2001GlobalBusinessSolutions Inc TemporaryTables Storesession transactionspecificdataShowdataonlytocreatingsession Discarddataoncommitorpreservedatauntilsessionends TablespaceA Temp01 Temp02 Copyright 2001GlobalBusinessSolutions Inc CustomersIndex Name1AcmeInc 2Zachary s3Marie s4Chimney Indexes OptimizequeriesROWID Block Row File Storedindependentoftables ConsumeminimalspaceReduceI OIndexonWHEREclausecolumns CustomersTable NameAddre 3Marie s4456Si1AcmeInc 1236Ch4Chimney9024F2Zachary s1234Br Copyright 2001GlobalBusinessSolutions Inc IndexTypes UniqueDuplicatesnotallowedNon uniqueDuplicatesallowed X Copyright 2001GlobalBusinessSolutions Inc IndexTypes SingleColumn Copyright 2001GlobalBusinessSolutions Inc IndexTypes Multi ColumnConcatenated composite IndexRowid OrderNoItemNoCust 007000001USA007000002GBSI007000003TEST007234005WEST006989001ABC006989002XYZ ORDERTABLE Copyright 2001GlobalBusinessSolutions Inc BalancedIndexTree B Tree ROOT BRANCH ROWIDPOINTER DATASEGMENT 0 000020004000 00002000 200124993000 300140014999 500070009000 50005999 700072008100 90009999 ROWID 1 4 RolledSheet 100Feet 5Days C O D LEAF Copyright 2001GlobalBusinessSolutions Inc BitmapIndex BitmapcreatedforeachcolumnvalueCommonUsageVeryLargeTablesLow CardinalityColumnsFeaturesRequiresminimalstorageImprovesperformance F BILLLISAANNTED 10010110 M GENDER Copyright 2001GlobalBusinessSolutions Inc FunctionBasedIndex CreateonfunctionsorexpressionsUsergeneratedPL SQLSQLfunctions non aggregate Externalprogram Ccallout 600500300250900 660550330275990 SALARY 1 10 SALARY Function Based Single SQL SELECTsalary salary 1 10FROMemployees Copyright 2001GlobalBusinessSolutions Inc PartitionData Decomposetablesandindexes Eachpartitioncanbestoredindifferenttablespace CustomerCustNoCustNameState 1KodakNYOracleCAUSSteelPA6QuestWA7MotorolaTX CustomersAL IN CustomersKS NY CustomersOH WY Copyright 2001GlobalBusinessSolutions Inc AdvantagesofPartitions Reducetimefordataloadsandindexcr