Overview
This course presents some of the most advanced features and capabilities of the Oracle 10g database. The content is geared towards senior, experienced SQL developers and database administrators who wish to make the most of the latest, most powerful elements of Oracle 10g.
Oracle’s powerful and multi-faceted Flashback technology which provides developer self-service recovery from erroneous commits is described along with alternatives for backing out bogus data changes. The potent elements of the latest SQL enhancements are covered along with extensive coverage of the SQL92 join syntax. Oracle’s recent extensions to it’s hierarchical SQL processing syntax are presented. Options and uses for new and complex data types and support for persistent object storage is offered. The many types of indexing made available in recent Oracle releases are compared. Alternative table organization methods are reviewed.
Throughout the course there is comprehensive coverage of Oracle’s data dictionary to review and monitor the usage of features.
Audience
This course is appropriate for experienced Oracle SQL developers and database administrators.
Course style
Lecture with frequent hands on labs.
Prerequisites
Skill with GUI interfaces
12 months of experience writing SQL
Note: Attendees without a strong SQL background will probably not have a successful class experience.
Course Topics
v Oracle 10g concepts
v The PetSaver database
v Flashback technology
v Advanced data types
v Working with objects
v Working with LOBs
v SQL92 join syntax
v Advanced multi-table SELECT statements
v Advanced hierarchical queries
v Enhancements to SQL
v Materialized views
v Alternative index types
Topic contents
v Oracle 10g concepts
v Grid computing
v Real application clusters (RAC)
v The Oracle application server
v The PetSaver database
v Flashback technology
v Flashback concepts
v Flashback query
v Flashback table
v Flashback drop
v Flashback versions
v Flashback transaction
v Advanced data types
v Timestamps and intervals
v The CAST function
v Collections
v Working with objects
v Defining a type
v Instantiating and storing an object
v Manipulating objects using SQL
v Working with LOBs
v Understanding LOBs
v Creating LOBs
v Manipulating LOBs with DBMS_LOB
v LOBs and NULL values
v SQL92 join syntax
v NATURAL joins
v CROSS joins
v INNER joins
v OUTER joins
v Advanced multi-table SELECT statements
v Sub-query predicates
v Correlated sub-queries
v Using set operators
v Advanced hierarchical queries
v Hierarchical data concepts
v Querying hierarchical data
v Hierarchical pseudocolumns
v Resolving hierarchical data errors
v Enhancements to SQL
v New NULL functions
v The CASE statement
v The MERGE statement
v Multi-table INSERTs
v Materialized views
v Creating materialized views
v Refresh strategies
v Alternative index types
v B-tree index review
v Monitoring index usage
v Bitmap indexes
v Function-based indexes
v Deterministic functions
v Reverse key indexes
v Bitmap join indexes
