book

Oracle Essentials, 5th Edition

by Rick Greenwald, Robert Stackowiak, Jonathan Stern

September 2013

Intermediate to advanced

432 pages

14h 42m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Goals of This BookAudience for This BookAbout the Fifth Edition (Oracle Database 12c)Structure of This BookConventions Used in This BookUsing Code ExamplesSafari® Books OnlineHow to Contact UsAcknowledgments
The Evolution of the Relational DatabaseRelational BasicsHow Oracle GrewThe Oracle Database FamilySummary of Oracle Database FeaturesDatabase Application Development FeaturesDatabase ProgrammingSQLPL/SQLJavaOracle and Web ServicesLarge objectsObject-oriented programmingThird-generation languages (3GLs)Database driversThe Oracle Call InterfaceNational Language SupportDatabase ExtensibilityOracle MultimediaOracle Spatial and Graph OptionXML DBDatabase Connection FeaturesOracle Net ServicesOracle Internet DirectoryOracle Connection ManagerThe Role of Oracle Fusion MiddlewareOracle’s WebLogic ServerOracle TuxedoData Integration ToolsBusiness Intelligence ToolsWebCenterIdentity ManagementDistributed Database FeaturesDistributed Queries and TransactionsHeterogeneous ServicesData Movement FeaturesTransportable TablespacesAdvanced Queuing and Oracle StreamsDatabase Performance FeaturesDatabase ParallelizationData WarehousingBitmap indexesStar query optimizationMaterialized viewsAnalytic functionsOLAP OptionAdvanced Analytics OptionManaging the Oracle DatabaseOracle Enterprise Manager 12cReal Application Testing OptionPluggable DatabasesStorage ManagementHigh AvailabilityFlashbackRecovery ManagerOracle Secure BackupData GuardFail SafeOracle Real Application ClustersDatabase Security FeaturesAdvanced Security OptionLabel Security OptionDatabase Vault OptionAudit Vault and Database Firewall OptionOracle Database Development ToolsOracle SQL DeveloperOracle Application ExpressOther Oracle DatabasesOracle MySQLBerkeley DB & Oracle NoSQL DatabaseOracle TimesTenCloudera Distribution of Hadoop
Databases and InstancesOracle Database StructuresSchemasTablespacesFiles of a databasePluggable DatabasesImplementationPDBs and Oracle featuresDatabase InitializationDeploying Physical ComponentsControl FilesDatafilesDatafile structureExtents and segmentsRedo LogfilesMultiplexing redo logfilesHow Oracle uses the redo logsNaming conventions for redo logsArchived redo logsARCHIVELOG mode and automatic archivingInstance Memory and ProcessesMemory Structures for an InstanceDatabase buffer cacheShared poolRedo log bufferOther pools in the SGAAutomatic PGA managementBackground Processes for an InstanceConfiguration, Engineered Systems, and the CloudThe Data Dictionary
Installing OracleOptimal Flexible ArchitectureSupporting Multiple Oracle Versions on a MachineUpgrading an Oracle DatabaseCreating a DatabasePlanning the DatabaseThe Value of EstimatingTools for Creating DatabasesOracle Net Services and Oracle NetResolving Oracle Net Service NamesGlobal Data ServicesOracle Net ManagerOracle Connection PoolingAuto-Discovery and AgentsOracle Net Configuration FilesStarting Up the DatabaseShutting Down the DatabaseAccessing a DatabaseServer Processes and ClientsServer processClient processApplication Servers and Web Servers As ClientsOracle Net and Establishing Network ConnectionsThe Shared Server/Multi-Threaded ServerSession memory for shared server processes versus dedicated server processesData dictionary information about the shared serverDatabase Resident Connection PoolingOracle in the CloudOracle at WorkOracle and TransactionsFlashbackA Transaction, Step by Step
DatatypesCharacter DatatypesNumeric DatatypeDate DatatypeTemporal validityOther DatatypesType ConversionConcatenation and ComparisonsNULLsBasic Data StructuresTablesEditionsViewsIndexesB*-tree indexesReverse key indexesBitmap indexesFunction-based indexesInvisible indexesStorage indexesPartitioningAdditional Data StructuresSequencesSynonymsClustersHash ClustersExtended Logic for DataRules ManagerThe Expression FilterData DesignConstraintsTriggersQuery OptimizationRule-Based OptimizationCost-Based OptimizationHow statistics are usedInfluencing the cost-based optimizerSpecifying an Optimizer ModeNewer database releases and the cost-based optimizerSaving the OptimizationComparing OptimizationsPerformance and OptimizationSQL TranslationUnderstanding the Execution PlanSQL AdvisorsData Dictionary Tables
Manageability FeaturesDatabase AdvisorsAutomatic Storage ManagementOracle Enterprise ManagerEnterprise Manager ArchitectureOracle Enterprise Manager ConsolesEM ExpressBackup and RecoveryTypes of Backup and Recovery OptionsOracle Secure BackupInformation Lifecycle ManagementILM in Oracle Database 12cWorking with Oracle SupportReporting ProblemsAutomated Patching
SecurityUsernames, Privileges, Groups, and RolesIdentity ManagementSecurity PrivilegesSpecial Roles: DBA, SYSDBA, and SYSOPERPoliciesRestricting Data-Specific AccessView-based securityFine-grained access controlLabel Security OptionSecurity and Application Roles and PrivilegesDistributed Database and Multitier SecurityDistributed security managementMultitier securityAdvanced Security OptionEncryptionData RedactionSecure BackupAuditingComplianceOracle Database Vault OptionOracle Audit Vault ServerFlashback Data ArchiveTransparent Sensitive Data Protection
Oracle and Resource UsageOracle and Disk I/O ResourcesI/O Planning Principles for an Oracle DatabaseVolume managersStorage subsystemsOracle’s engineered systems and storageOracle and ParallelismBlock-Range ParallelismParallelism for Tables and Partitions of TablesWhat Can Be Parallelized?Degree of parallelismSelf-tuning adaptive parallelismPartition-Based ParallelismParallelism for partitions and subpartitions of a tableFast full-index scans for nonpartitioned tablesParallel insert for nonpartitioned and partitioned tablesOracle and Memory ResourcesHow Oracle Uses the System Global AreaAutomatic sizing for the SGAThe database buffer cacheThe shared poolThe redo log bufferQuery results cachingHow Oracle Uses the Program Global AreaMemory for SQL statementsMemory for sorting within the PGAOracle and CPU ResourcesPerformance Tuning BasicsDefining Performance and Performance ProblemsMonitoring and Tuning the Oracle Database for PerformanceUsing the Oracle Database Resource ManagerAdditional Monitoring and Tuning Available for Oracle ExadataA Final Note on Performance Tuning
Basics of Concurrent AccessTransactionsLocksConcurrency and ContentionIntegrity ProblemsSerializationOracle and Concurrent User AccessOracle’s Isolation LevelsOracle Concurrency FeaturesHow Oracle Handles LockingA Simple Write OperationA Conflicting Write OperationA Read OperationConcurrent Access and PerformanceWorkspacesWorkspace ImplementationWorkspace OperationsWorkspace Enhancements

OLTP BasicsWhat Is a Transaction?What Does OLTP Mean?General characteristicsOnline and batch transaction processingOLTP Versus Business IntelligenceTransactions and High AvailabilityOracle’s OLTP HeritageArchitectures for OLTPTraditional Two-Tier Client/ServerStored ProceduresThree-Tier SystemsApplication Servers and Web ServersThe GridOLTP and the CloudOracle Features for OLTPGeneral Concurrency and PerformanceScalabilityMulti-Threaded Server/shared serverDatabase Resource ManagerReal Application ClustersExadata and OLTPHigh Availability
Data Warehousing BasicsThe Evolution of Data Warehousing and Business IntelligenceA Topology for Business IntelligenceData MartsThe Operational Data Store and Enterprise WarehouseOLTP Systems and Business IntelligenceBig Data and the Data WarehouseData Warehouse DesignQuery OptimizationBitmap Indexes and ParallelismOptimization Provided by the Exadata Storage Server SoftwareDimensional Data and Hierarchies in the DatabaseSummary TablesMaterialized ViewsOLAP OptionAnalytics and Statistics in the DatabaseBasic Analytic and Statistical FunctionsOther SQL ExtensionsAdvanced Analytics OptionOther Datatypes and Big DataLoading Data into the Data WarehouseManaging the Data WarehouseBusiness Intelligence ToolsOracle Business Intelligence Foundation SuiteBusiness Intelligence ApplicationsData Discovery and Oracle Endeca Information DiscoveryOracle ExalyticsThe Metadata ChallengePutting It All TogetherA Complete Analytics InfrastructureBest PracticesCommon MisconceptionsEffective Strategy
What Is High Availability?Measuring High AvailabilityThe System Stack and AvailabilityServer Hardware, Storage, and Database Instance FailureWhat Is Instance Recovery?Phases of Instance RecoveryProtecting Against System FailureComponent RedundancyDisk RedundancyAutomatic Storage ManagementSite and Computer Server FailoverOracle Data Guard and Site FailuresOracle Active Data Guard and Zero Data LossOracle GoldenGate and ReplicationReal Application Clusters and Instance FailuresOracle Transparent Application FailoverHow TAF worksTAF and various Oracle configurationsOracle Application ContinuityRecovering from Failures and Data CorruptionDeveloping a Backup-and-Recovery StrategyTaking Oracle BackupsUsing Backups to RecoverRecovery ManagerRead-Only Tablespaces and BackupsOld-Fashioned Data RedundancyPoint-in-Time RecoveryFlashbackPlanned Downtime
System BasicsSymmetric Multiprocessing Systems and NodesClustered Solutions, Grid Computing, and the CloudDisk and Storage TechnologyOracle’s Engineered SystemsOracle Exadata Database MachineOracle ExalogicOracle SuperClusterOracle Database ApplianceOther Engineered SystemsChoosing and Defining the Right PlatformSizing and Planning for GrowthMaximum Availability Architecture ConsiderationsJustifying an Oracle Engineered System
Accessing Distributed DatabasesDistributed Data Across Multiple Oracle DatabasesAccess to and from Non-Oracle DatabasesTwo-Phase CommitOracle TuxedoReplication and Data TransportReplication BasicsHistory of Oracle Replication OfferingsOracle GoldenGateGlobal Data ServicesData Transport Using Database Features
Object-Oriented DevelopmentObject-Relational FeaturesObjects in OracleOther extensibility featuresJava’s Role and Web ServicesJavaBeansExtensibility Features and OptionsOracle MultimediaOracle TextXML DBOracle Spatial and Graph OptionThe Extensibility Architecture Framework
Cloud DefinitionsCommon CharacteristicsCloud LevelsIs the Cloud New?MultitenancyStatelessUse Cases for Cloud ComputingOracle Database in the CloudOracle as a DBaaSOracle as a PaaSConsumer and ProviderOracle Database Cloud ServiceHistory of Application ExpressArchitectureDatabase Cloud architectureAccess architectureSecurity and architectureAPEX architectureDevelopment with the Database Cloud ServiceAPEX application developmentSQL WorkshopPackaged applicationsRESTful Web ServicesPortability with the Database Cloud ServiceSQL Developer and the Database CloudImplementing Provider Clouds
Chapter 1: Introducing OracleChapter 2: Oracle ArchitectureChapter 3: Installing and Running OracleChapter 4: Data StructuresChapter 5: Managing OracleChapter 6: Oracle Security, Auditing, and ComplianceChapter 7: Oracle PerformanceChapter 8: Oracle Multiuser ConcurrencyChapter 9: Oracle and Transaction ProcessingChapter 10: Oracle Data Warehousing and Business IntelligenceChapter 11: Oracle and High AvailabilityChapter 12: Oracle and Hardware ArchitectureChapter 13: Oracle Distributed Databases and Distributed DataChapter 14: Oracle Extended DatatypesChapter 15: Oracle and the Cloud
Web SitesBooks and Oracle DocumentationChapter 1: Introducing OracleChapter 2: Oracle ArchitectureChapter 3: Installing and Running OracleChapter 4: Data StructuresChapter 5: Managing OracleChapter 6: Oracle Security, Auditing, and ComplianceChapter 7: Oracle PerformanceChapter 8: Oracle Multiuser ConcurrencyChapter 9: Oracle and Transaction ProcessingChapter 10: Oracle Data Warehousing and Business IntelligenceChapter 11: Oracle and High AvailabilityChapter 12: Oracle and Hardware ArchitectureChapter 13: Oracle Distributed Databases and Distributed DataChapter 14: Oracle Extended DatatypesChapter 15: Oracle and the Cloud