book

Oracle Database 10g: A Beginner's Guide

by Ian Abramson, Michael Abbey, Michael Corey

April 2004

Beginner to intermediate

416 pages

10h 8m

English

McGraw Hill Computing

Read now

Unlock full access

The Control FilesThe Online Redo LogsThe SYSTEM TablespaceThe SYSAUX TablespaceDefault Temporary TablespaceUndo TablespaceThe System Parameter FileBackground ProcessesProject 1-1 Reviewing the Oracle Database 10g ArchitectureThe Database Administrator
varchar2numberdatetimestampclobblob
Tables Related to part_master
ViewsTriggersProceduresFunctionsPackages
IndexesUsersTablespace QuotasSynonymsRolesDefault User Environments
SelectInsertUpdateDeleteSystem Privileges
DDLDML
insertselect
The where Clause with NOTThe where Clause with a Range SearchThe where Clause with a Search ListThe where Clause with a Pattern SearchThe where Clause: Common Operators
updatedelete
String FunctionsNumeric FunctionsAggregate Functions
Date FunctionsSpecial Formats with the Date Data TypeNested Functions
Inner JoinsOuter JoinsProject 2-1 Joining Data Using Inner and Outer JoinsProject 2-2 Joining Data Using ANSI SQL JoinsSelf-JoinsCritical Skill 2.9 Learn the group by and having Clausesgroup byhavingProject 2-3 Grouping Data in Your select StatementsCritical Skill 2.10 Learn Subqueries: Simple and Correlated Comparison with JoinsSimple SubqueryCorrelated Subqueries with JoinsCritical Skill 2.11 Use Set Operators: Union, Intersect, Minusunionunion allintersectminusProject 2-4 Using the union Function in Your SQLCritical Skill 2.12 Use ViewsCritical Skill 2.13 Learn Sequences: Just Simple StuffCritical Skill 2.14 Employ Constraints: Linkage to Entity Models, Types, Deferred, Enforced, Gathering ExceptionsLinkage to Entity ModelsTypesDeferredCritical Skill 2.15 Format Your Output with SQL*PlusPage and Line SizePage TitlesPage FootersFormatting ColumnsProject 2-5 Formatting Your SQL OutputWriting SQL*Plus Output to a FileChapter 2 Mastery Check
Architecture and DesignCapacity PlanningBackup and RecoverySecurityPerformance and TuningManaging Database ObjectsStorage ManagementChange ManagementSchedule JobsNetwork ManagementTroubleshooting
SchemasStorage Structures
Modes of OperationDatabase and Instance Shutdown
Instance ConfigurationUser SessionsResource Consumer GroupsSchema, Security, and Storage ManagementDistributed ManagementWarehouse FeaturesOther Tools
ControlfilesRedo LogsUndo ManagementSchema Objects
Archive LogsTablespaces and Datafiles
Create a UserEdit Users
Grant AuthorityRolesProfilesIn ConclusionProject 3-1 Creating Essential Objects
Network ProtocolsOptimize Network BandwidthCharacter SetsConnectionsMaintain ConnectionsDefine a Location
Dedicated ServerShared ServerSet DispatchersViews to Monitor the Shared Server
A Connect DescriptorDefine a Connect DescriptorThe Oracle Connection ManagerSession MultiplexingFirewall Access Control
Password AuthenticationMultiple ListenersConnection Pooling
Directory Naming MethodDirectory Information TreesDistinguished NamesHow to Find the Directory Naming InformationNet Service Alias EntriesThe Local Naming MethodThe Easy Naming MethodThe External Naming MethodWhich Naming Method to Use
Syntax for Configuration Files
The Oracle Enterprise ManagerThe Oracle Net ManagerThe OEM ConsoleThe OEM ComponentsThe Oracle Net Configuration AssistantThe Oracle Internet Directory Configuration AssistantCommand-Line UtilitiesThe Oracle Advanced Security OptionDispatchersProject 4-1 Testing a Connection
Control Access
Where Do I Start?Backup ArchitectureOracle BinariesThe Parameter FilesControl FilesRedo LogsUndo SegmentsCheckpointsArchive LogsDatafiles, Tablespaces, Segments, Extents, and BlocksTrace Files
Types of User-Managed BackupsCold BackupsHot BackupsRecovery from a Cold BackupRecovery from a Hot BackupSeven Steps to RecoveryRecovery Using Backup Control Files
Some Export Examples
A Data Pump Export and Import Example
How to Run the Original UtilitiesExamples Using Original Export and Import
RMAN ArchitectureSet Up a Recovery Catalog and Target DatabaseKey RMAN FeaturesBackupsPerforming BackupsRestore and RecoveryProject 5-1 RMAN End-to-End
The PL/SQL Character Set
Project 6-1 Creating a PL/SQL ProgramSQL in Your PL/SQL ProgramsThe Cursor FOR Loop
Error Handling Oracle-Supplied Variables
Program ControlProject 6-2 Using Conditions and Loops in PL/SQL
Project 6-3 Creating and Using a Function
Platform IndependenceJava in All Three TiersJava 2 Platform, Standard EditionJava 2 Platform, Enterprise EditionThe J2EE ServerDifferent Types of Java ProgramsStandalone ApplicationsAppletsJavaBeansJavaServer Pages and ServletsEnterprise JavaBeansAdvantages of N-Tiered ArchitecturesJava, XML, Web Services, and Oracle 10gOpportunities for Oracle DBAs
Java Initialization ParametersEnvironmental Variables
Native CompilationGarbage CollectionThings to Watch Out ForMain Components of the JVM
JDBC Thin DriverJDBC Thick DriverJDBC Server-Side DriverUse the Proper JDBC Driver
How to Write Database ProgramsProject 7-1 Accessing the Database with Java
SQLJ TranslatorSample SQLJ CodeSQLJ Directions
Java Utilities for DBAsloadjava and dropjava Examplesojvmjava ExamplesPrivilegesResolver SpecificationsProject 7-2 Creating a Java-Stored Procedure
create java classcreate java sourcecreate java resource
Oracle Application Server 10gOracle JDeveloper 10g
The SQL/XML Standardxmlelement ()xmlattributes ()xmlforest ()xmlagg ()Project 8-1 Creating an XML Listing
The Native XMLTypeThe XML DB RepositoryRegistering an XML SchemaLoading XML DataProject 8-2 Storing XMLUse the RESOURCE_VIEWPath-Based AccessUpdate the XML Document
Project 8-3 Using Simple Queries
Why Use Data PartitioningImplement Data PartitioningProject 9-1 Creating a Range-Partitioned Table and a Local Partitioned Index
Data CompressionIndex Key Compression
Parallel Processing Database ComponentsParallel Processing ConfigurationInvoke Parallel Execution
Uses for Materialized ViewsQuery RewriteWhen to Create Materialized ViewsCreate Materialized Views
RAC Architecture
ASM Architecture
Self-Managing Databases
Aggregation FunctionsAnalysis FunctionsOther Functions

Content preview from Oracle Database 10g: A Beginner's Guide

CRITICAL SKILL 9.10

Create SQL Models

One of the more powerful data analysis features introduced in Oracle Database 10g is SQL models. SQL models allow a user to create multidimensional arrays from query results. Formulas, both simple and complex, can then be applied to the arrays to generate results in which the user is interested. SQL models allow inter-row calculations to be applied without doing expensive self-joins.

SQL models are similar to other multidimensional structures used in business intelligence applications. However, because they are part of the database, they can take advantage of Oracle Database 10g’s built-in features of scalability, manageability security, and so on. In addition, using SQL models, there is no need to transfer ...