To Register

For more information or to Register,
Click Here.

Follow Me

OR652 Oracle Database Fundamentals for Developers

Course 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

Course Objectives 

  • Describe the major architectural components of Oracle, including the SGA, background processes and files that make up the physical database.
  • Understand the concepts of start and stop of a database, including the use of various shutdown modes such as IMMEDIATE, TRANSACTIONAL and ABORT.
  • Query the data dictionary (e.g. DBA_TABLES, DBA_TABLESPACES) to determine the structure of the database.
  • Query the structure and objects of the database using TOAD and SQL*Plus.
  • Understand how to navigate the Oracle dictionary using TOAD and SQL*Plus.
  • Understand and use CREATE and ALTER statements for creating objects including schemas, tables, indexes, views, and constraints on tables to enforce business rules. 
  • Understand how to disable and enable constraints and when it is appropriate.
  • 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.
  • Understand transaction processing and the locking for INSERT, UPDATE and DELETE.
  • Understand deadlocks and learn how to manage transactions thru COMMITS and ROLLBACKS
  • Describe and choose the appropriate table locks
  • Load and extract data using SQL Loader.
  • Load and extract data using EXP, IMP and DataPump.

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

  • List main components of Oracle architecture
  • List the major components of the Oracle instance
  • List the major components of the Oracle database
  • Understand the different control files and when to use them
  • Understand the start and stop of a database including:
    •   IMMEDIATE
    •   TRANSACTIONAL
    •    ABORT
  • List Oracle application configurations
  • List Oracle application components
  • Understand network configurations and Oracle Net Listener configurations

Organizing Storage

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

Controlling Use of Block Space

  • Understand the concepts of block space and utilization parameters
  • Apply block utilization parameters
  • Calculate appropriate values for block utilization parameters
  • Differentiate between migration and chaining

SQL Statement for CREATE and ALTER

  • Review the definition of the structures: TABLE, VIEW and USER.
  • Understand how to use the CREATE statement to create:
    • TABLE
    • VIEW
    • USER
  • Understand how to use the ALTER statement to create the above objects
  • Use these statements to create and alter the above objects

Enforcing Business Rules with Constraints

  • Differentiate between constraint types
  • Understand how to use the CREATE to create a constraint with the table
  • Understand how to use the ALTER statement add a constraint to an existing table
  • 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 FK index
  • Handle constraint exceptions

Defining Indexes

  • List different types of indexes and their uses
  • Understand how to use the CREATE statement to create an index
  • Create B-Tree, Bitmap, Function-based, Reverse key indexes
  • Choose storage parameters for indexes
  • Allocate and deallocate index space
  • Understand when to re-create indexes

DMLTransaction Processing

  • Describe DML transactions (INSERT, UPDATE, DELETE and SELECT)
  • Describe locking that occurs with DML
  • List the stages in processing a query, DML statements, and COMMITs
  • Differentiate between other background processes
  • Describe and choose the appropriate table locks
  • Recognize a deadlock
  • Understand  and determine when to use a regular, read-only, or serializable transaction

Transporting Data

  • Define the different ways of transporting data:
  • SQL*Loader
  • DataPump.
  • EXP and IMP
  • Understand how to select the appropriate transfer technique
  • Use SQL*Loader to extract data
  • Use SQL*Loader to load data
  • Use DataPump to extract data
  • Use DataPump to load data

Reorganizing Data

  • Determine when to use export and import (EXP and IMP)
  • Describe how direct export works
  • Export a schema or table
  • Import data only
  • Import objects
  • Transportable tablespaces