To Register

For more information or to Register,
Click Here.

Follow Me

OR552 Oracle 10g Database Fundamentals for Developers

OVERVIEW

This course is targeted for developers who need an understanding of the tasks to design and manage the data structures that support Oracle database applications. This class will combine lecture with hands-on labs to facilitate the student’s understanding of the Oracle database server architecture and SQL statement processing, and learn how to organize and create tables and related storage structures in the database. On completion of this course, the students will know how to use and design a schema, load and extract data from the database, enforce business rules with constraints, and have the foundation to communicate with their DBA’s.

DURATION:  2 Days

OBJECTIVES

Describe the major architectural components of Oracle9i, including the SGA, background processes and files that make up the physical database.

  • Understand the role and importance of control files and add additional control files.
  • Query the data dictionary (e.g. DBA_TABLES, DBA_TABLESPACES) to determine the structure of the database.
  • Create constraints on tables to enforce business rules.
  • Load and extract data
  • Understanding the new DataPump in Oracle 10g
  • Start and stop a database, including the use of various shutdown modes such as IMMEDIATE, TRANSACTIONAL and ABORT.
  • Create and manage locally managed tablespaces including adding free space, monitoring free/used space
  • Create simple B-Tree indexes on tables. This includes understanding the fundamental rules for when and where to create and when to avoid using indexes.
  • Manage storage for objects (e.g. tables and indexes)
  • Manage transactions and understanding the locking
  • Understand the concepts of Server-generated Alerts in Oracle 10g

INTENDED AUDIENCE:  This course is for Oracle developers.

PREREQUISITES

Before beginning this course, students should either have a working knowledge of both SQL and Oracle's PL/SQL. 

FORMAT/MATERIALS

This course includes a combination of lecture, discussion and numerous computer workshops.  Students will receive an extensive course manual covering Oracle DBA duties, which will be a valuable reference after class. 

CONTENT  

Overview of the Oracle Database Architecture

  • Differentiate between database architectures
  • List Oracle application configurations
  • Describe Parallel Query and DML
  • List Oracle application components

SQL Statement Processing

  • List main components of Oracle architecture
  • List the major components of the Oracle instance
  • List the major components of the Oracle database
  • List the stages in processing a query, DML statements, and COMMITs
  • Differentiate between other background processes

Transactions

  • Describe transactions
  • Choose the appropriate table locks
  • Recognize a deadlock
  • Determine when to use a regular, read-only, or serializable transaction

Organizing Storage

  • Identify Oracle Storage Structures
  • Use the STORAGE clause
  • Use locally manged tablespaces
  • List methods used to size Oracle tables
  • Select storage information from the Data Dictionary
  • Use OEM to select storage information

Controlling Use of Block Space

  • Apply block utiliaztion parameters
  • Calculate appropriate values for block utilization parameters
  • Differentiate between migration and chaining
  • Use OEM to select block space information

Selecting an Index Type

  • List different types of indexes and their uses
  • Create B-Tree, Bitmap, Function-based, Reverse key indexes
  • Choose storage parameters for indexes
  • Allocate and deallocate index space

Enforcing Business Rules with Constraints

  • Differentiate between constraint types
  • Enable or disable constraints with or without validation
  • Defer the enforcement of constraints
  • Describe how Oracle enforces PK and unique constraints
  • Decide when to use a foreign key constraint
  • Handle constraint exceptions

Transporting Data

  • Select the appropriate transfer technique
  • Use SQL*Plus to extract data
  • Unload data by copying from existing table
  • Importing and exporting with DataPump(10g)
  • Use SQL*Loader to load data
  • Use direct path load API and direct load inserts
  • Use OEM to load data

Reorganizing Data

  • Determine when to use export and importDescribe how direct export works
  • Export a schema or table
  • Import data only
  • Import objects
  • Transportable tablespaces
  • Understand concepts of 10g tablespaces and temporary tablespace groups