Oracle 10g Database Administration
(128 Hours)

Series: Database Series

Audience and Prerequisites Overview Course Outline Detail

George Mason University partners with Oracle in the Oracle University program. Official Oracle curriculum is utilized in our Oracle 10g Database Administration program. Our instructors are Oracle Certified Professionals with real-world database administration experience. Students receive a 40% discount coupon on certification examinations as an added benefit of George Mason's membership with Oracle University.

The Oracle 10g Database Administration program delivers knowledge of database installation, management and administration tasks and an understanding of the Oracle database architecture. By earning an Oracle Certified Professional credential, database professionals can accelerate their careers and significantly enhance their professional growth.

An Oracle Certified Professional (OCP) database administrator with the 10g credential has mastered the skill sets needed to address critical database functions such as manageability, performance, reliability, security, and availability using the latest Oracle technology.

Return to Top

AUDIENCE AND PREREQUISITES

This course is designed for individuals who have some computer and database experience and wish to prepare for advanced skills in the installation, configuration and administration of Oracle Enterprise Databases. Related experience might include familiarity with Microsoft Windows, Microsoft Office, programming and/or other databases. Contact our Program Director at (703)-842-7404 for more information on this course or Oracle career paths.

Return to Top

OVERVIEW

This hands-on course will teach you to effectively plan, install, configure, administer, query, troubleshoot and manage Oracle Databases. All books and materials are included.

Through four modules, including hands-on exercises and professional guidance, students will gain a broad, deep, foundation in Oracle Database planning, design, installation and administration. The course includes modules on SQL query languages and both levels of Oracle 10g certification: Oracle Certified Associate (OCA) and Oracle Certified Professional (OCP). The course will prepare students for the Oracle exams, as well as deliver a solid foundation in the real world application of their database skills. Learn how to:

• Install, configure, and administer Oracle Database systems
• Manage and maintain an Oracle Database
• Plan, design and create tables
• Create and update reports from Oracle Databases
• Query Oracle databases using SQL and iSQL*Plus to retrieve critical business data
• Backup and recovery of database systems
• Tune and optimize a Oracle Database for most efficient use of computer resources

Return to Top

COURSE OUTLINE

Module 1: Introduction to Oracle 10g Structured Query Language (SQL)

Introduction

• List the Oracle Database 10g main features
• Provide an overview of: components, internet platform, apps server and developer suite
• Describe relational and object relational database designs
• Review the system development life cycle
• Describe different means of storing data
• Review the relational database concept
• Define the term data models
• Show how multiple tables can be related

Retrieving Data Using the SQL SELECT Statement

• Define projection, selection, and join terminology
• Review the syntaxes for the basic SQL SELECT statements
• Use Arithmetic and Concatenation operators in SQL statements
• List the differences between SQL and iSQL*Plus
• Log into the database using iSQL*Plus
• Explain the iSQL*Plus interface
• Categorize the different types of iSQL*Plus commands
• Save SQL statements to script files

Restricting and Sorting Data

• Limit rows using a selection
• Using the WHERE clause to retrieve specific rows
• Using the comparison conditions in the WHERE clause
• Use the LIKE condition to compare literal values
• List the logical conditions AND, OR, NOT
• Describe the rules of precedence for the conditions shown in this lesson
• Sort rows with the ORDER BY clause
• Use ampersand substitution in iSQL*Plus to restrict and sort output at run time

Using Single Row Functions to Customize Reports

• Show the differences between single row and multiple row SQL functions
• Categorize the character functions into case manipulation and character manipulation types
• Use the character manipulation functions in the SELECT and WHERE clauses
• Explain and use the DATE and numeric functions
• Use the SYSDATE function to retrieve the current date in the default format
• Introduce the DUAL table as a means to view function results
• List the rules for applying the arithmetic operators on dates
• Use the arithmetic operators with dates in the SELECT clause

Reporting Aggregated Data Using the Group Functions

• Describe and categorize the group functions
• Use the group functions
• Utilize the DISTINCT keyword with the group functions
• Describe how nulls are handled with the group functions
• Create groups of data with the GROUP BY clause
• Group data by more than one column
• Avoid illegal queries with the group functions
• Exclude groups of data with the HAVING clause

Displaying Data from Multiple Tables

• Show the join tables syntax using SQL 99 syntax
• Use table aliases to write shorter code and explicitly identify columns from multiple tables
• Issue a SQL CROSS JOIN statement to produce a Cartesian product
• Use the NATURAL JOIN clause to retrieve data from tables with the same named columns
• Create a join with the USING clause to identify specific columns between tables
• Create a three way join with the ON clause to retrieve information from 3 tables
• List the types of outer joins LEFT, RIGHT, and FULL
• Add additional conditions when joining tables with the AND clause

Using Sub queries to Solve Queries

• List the syntax for sub queries in a SELECT statements WHERE clause
• List the guidelines for using sub queries
• Describe the types of sub queries
• Execute single row sub queries and use the group functions in a sub query
• Identify illegal statements with sub queries
• Execute multiple row sub queries
• Analyze how the ANY and ALL operators work in multiple row sub queries
• Explain how null values are handled in sub queries

Using the SET Operators

• Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
• Use the UNION ALL operator to return all rows from multiple tables
• Describe the INTERSECT operator
• Use the INTERSECT operator
• Explain the MINUS operator
• Use the MINUS operator
• List the SET operator guidelines
• Order results when using the UNION operator

Manipulating Data

• Write INSERT statements to add rows to a table
• Copy rows from another table
• Create UPDATE statements to change data in a table
• Generate DELETE statements to remove rows from a table
• Use a script to manipulate data
• Save and discard changes to a table through transaction processing
• Show how read consistency works
• Describe the TRUNCATE statement

Using DDL Statements to Create and Manage Tables

• List the main database objects and describe the naming rules for database objects
• Introduce the schema concept
• Display the basic syntax for creating a table and show the DEFAULT option
• Explain the different types of constraints
• Show resulting exceptions when constraints are violated with DML statements
• Create a table with a sub query
• Describe the ALTER TABLE functionality
• Remove a table with the DROP statement and Rename a table

Creating Other Schema Objects

• List the main database objects and describe the naming rules for database objects
• Introduce the schema concept
• Display the basic syntax for creating a table and show the DEFAULT option
• Explain the different types of constraints
• Show resulting exceptions when constraints are violated with DML statements
• Create a table with a sub query and remove a table with the DROP statement
• Describe the ALTERTABLE functionality
• Rename a table

Managing Objects with Data Dictionary Views

• Describe the structure of each of the dictionary views
• List the purpose of each of the dictionary views
• Write queries that retrieve information from the dictionary views on the schema objects

Controlling User Access

• Controlling user access
• System versus objects privileges
• Creating user sessions and granting system privileges
• Using roles to define user groups
• Creating and granting privileges to a role
• Granting and revoking object privileges
• Changing your password
• Using Database Links

Manage Schema Objects

• Creating directories
• Creating and querying external tables
• Creating Index Organized Tables
• Creating Function based indexes
• Dropping Columns
• Altering the structure of tables and adding constraints
• Performing FLASHBACK Statement
• Materialized Views overview

Manipulating Large Data Sets

• Using the MERGE Statement
• Performing DML with Subqueries
• Performing DML with a RETURNING Clause
• Overview of Multitable INSERT Statements
• Tracking Changes in DML

Generating Reports by Grouping Related Data

• Overview of GROUP BY and Having Clause
• Aggregating data with ROLLUP and CUBE Operators
• Determine subtotal groups using GROUPING Functions
• Compute multiple groupings with GROUPING SETS
• Define levels of aggregation with Composite Columns
• Create combinations with Concatenated Groupings

Managing Data in Different Time Zones

• TIME ZONES
• Oracle9i Date time Support
• Conversion operations

Searching Data Using Advanced Sub queries

• Subquery Overview
• Using a Sub query
• Comparing several columns using Multiple-Column Sub queries
• Defining a Data source Using a Sub query in the FROM Clause
• Returning one Value using Scalar Sub query Expressions
• Performing ROW by-row processing with Correlated Sub queries
• Reusing query blocks using the WITH Clause

Hierarchical Data Retrieval

• Sample Data from the EMPLOYEES Table
• The Tree Structure of Employee data
• Hierarchical Queries
• Ranking Rows with LEVEL
• Formatting Hierarchical Reports Using LEVEL and LPAD
• Pruning Branches with the WHERE and CONNECT BY clauses

Performing Regular Expression Support and Case Insensitive

• Regular Expression Support Overview
• Describing simple and complex patterns for searching and manipulating data

Module 2: Program with PL/SQL

What is PL/SQL

• PL/SQL Environment
• Benefits of PL/SQL
• Stored Procedures, Functions, and Triggers
• Stored Procedures and SQL Scripts

Structure of a PL/SQL Block

• Header Section
• Declaration Section
• Execution Section
• Exception Section

Return to Top

Creating a PL/SQL Procedure

Creating a PL/SQL Function

PL/SQL Variables and Constants

• Declaring PL/SQL Variables
• Declaring PL/SQL Constants
• Assigning Values to Variables
• Using Variables

Control Structures in PL/SQL

• IF Statement
• Loops
• Cursors
• Nested Loops

Error Handling

• Exceptions
• System Defined Exceptions
• Programmer Defined Exceptions

Other PL/SQL Tools

• PL/SQL Packages
• Triggers
• Views used with PL/SQL

Module 3: Oracle 10g Certified Associate

Architecture

• Outline the Oracle Architecture and its main Components
• Explain the Oracle instance architecture

Installing the Oracle Database Software

• Identify common database administrative tools available to a DBA
• Use optimal flexible architecture
• Install software with Oracle Universal Installer
• Identify and configure commonly used environment variables
• Use Installer Log

Creating an Oracle Database

• Use DBCA to Create a database
• Use DBCA to Delete a database
• Use DBCA to manage templates

Managing the Oracle Instance

• Use Enterprise Manager
• Use SQL*Plus and iSQL*Plus to access the Oracle Database
• Modify database initialization parameters
• Describe the stages of database startup
• Describe the database shutdown options
• View the database alert log
• Use dynamic performance views

Managing Database Storage Structures

• Describe how table row data is stored in blocks
• Define the purpose of tablespaces and data files
• Explain space management in tablespaces
• Create tablespaces
• Manage tablespaces: alter, drop, take offline, put online, add data files, make read-only or read-write, generate DDL
• Obtain tablespace information
• Explain key features and benefits of ASM

Administering User Security

• Create and manage database user accounts
• Create and manage roles
• Grant and revoke privileges
• Create and manage profiles

Managing Schema Objects

• Create and modify tables
• Define constraints and states of constraints
• Dropping and truncating tables
• Create and use B-Tree and Bitmap indexes
• Create Views
• Create sequences
• Use data dictionary

Managing Data and Concurrency

• Manipulate data through the use of SQL
• Identify and administer PL/SQL objects
• Describe triggers and triggering events
• Define levels of locking
• List possible causes of lock conflict
• Monitor and resolve lock conflicts

Managing Undo Data

• Monitor and administer undo
• Configure undo retention
• Describe the relationship between undo and transactions
• Size the undo tablespace

Implementing Oracle Database Security

• Apply the principle of least privilege
• Audit database activity
• Implement Fine-Grained Auditing

Configuring the Oracle Network Environment

• Use Database Control to Create additional listeners
• Use Database Control to Create Oracle Net service aliases
• Control Oracle Net Listeners
• Identify when to use shared servers versus dedicated servers

Proactive Maintenance

• Gather optimizer statistics
• Manage the Automatic Workload Repository
• Use the Automatic Database Diagnostic Monitor (ADDM)
• Set warning and critical alert thresholds
• React to performance issues

Performance Management

• Use enterprise manager to view performance
• Tune SQL by using SQL tuning advisor
• Tune SQL by using SQL access advisor
• Use automatic shared memory management
• Use the memory advisor to size memory buffer

Backup and Recovery Concepts

• Describe the types of failure that may occur in an Oracle Database
• Identify the importance of checkpoints, redo log files, and archived log files
• Tuning instance recovery
• Configure a database for recoverability
• Configure ARCHIVELOG mode

Performing Database Backup

• Create consistent database backups
• Back up your database without shutting it down
• Create incremental backups
• Automate database backups
• Backup a control file to trace
• Monitor flash recovery area

Performing Database Recovery

• Recover from loss of a Control file
• Recover from loss of a Redo log file
• Recover from loss of a system-critical data file
• Recover from loss of a non system-critical data file

Performing Flashback

• Describe flashback database
• Resotore the table contents to a specific point in time
• Recover from a dropped table
• Use Flashback Query to view the contents of the database as of any single point of time
• View transaction history or row with flashback transaction query

Moving Data

• Describe the general architecture of Data Pump
• Use Data Pump export and import to move data between Oracle databases
• Load data with SQL Loader
• Use external tables to move data

Module 4: Oracle 10g Certified Professional

Using Globalization Support

• Datetimes with Timezones
• Specifying Language-Dependent Behavior
• Locale Variants
• Linguistic Sorting
• Case and Accent Insensitive Sorts
• Linguistic Comparisons
• Obtaining Information about the Current NLS Configuration

Securing the Oracle Listener

• Listener Password Authentication
• Controlling Database Access
• Securing the EXTPROC Service Entry

Configuring Recovery Manager

• Using a Flash Recovery Area with RMAN
• Setting Parameters for RMAN
• Starting RMAN
• Configuring Persistent Settings for RMAN
• Control File Autobackups
• Retention Policies

Using Recovery Manager

• Issuing Recovery Manager Commands
• Parallelization of Backup Sets
• Compressed Backups
• Copying the Whole Database
• Making Incremental Backups
• Block Change Tracking
• Incrementally Updating Backups
• Monitoring RMAN Backups

Diagnostic Sources

• The Alert Log
• Viewing Alerts with EM
• Alerts Notification
• Editing Thresholds
• Trace Files

Recovering from non-critical losses

• Creating New Temporary Tablespace
• Recreating Redo Log Files
• Recovering an Index Tablespace
• Read-Only Tablespace Recovery
• Loss of Password Authentication File

Database Recovery

• Recovery Steps
• User-Managed Recovery Procedures: RECOVER Command
• Types of incomplete recovery
• Incomplete Recovery Best Practices
• Recovery Using EM
• Simple Recovery Through RESETLOGS
• Point-in-time recovery using RMAN

Flashback database

• When to Use Flashback Technology
• Configuring Flashback Database
• Monitoring Flashback Database
• Best Practices for the Database and Flash Recovery Area
• Flash Recovery Area Space Usage
• Flashback Database Examples

Recovering from User Errors

• Recycle Bin
• Flashback Dropped Tables Using EM
• Querying Dropped Tables
• Flashback Versions Query
• Flashback Transaction Query
• Using Flashback Versions Query and Flashback Transaction Query
• Flashback Table
• Using EM To Flashback Tables

Dealing with Database Corruption

• What is block corruption?
• Interpreting DBVERIFY
• The ANALYZE command
• How to Handle Corruptions
• The DBMS_REPAIR Package
• Block Media Recovery (BMR)
• Detecting Database Corruptions Using DBVERIFY
• Using RMAN to Repair Corrupt Blocks

Automatic Database Management

• Automatic Optimizer Statistics Collection
• Workload Repository
• Database Control and Advisors
• Using the SQL Tuning Advisor
• Using the SQL Access Advisor
• Automatic Undo Retention Tuning

Monitoring and Managing Storage

• Redo Logfile Size Advisor
• Resumable Statements
• Tablespace Space Usage Monitoring
• Accessing the Segment Advisor
• Shrinking Segments Using SQL
• Segment Resource Estimation
• Monitoring Index Space
• Identifying Unused Indexes

Automatic Storage Management

• ASM Concepts
• ASM General Architecture
• Creating an ASM instance
• Creating tablespaces that use ASM storage
• Viewing ASM information
• Migrating a tablespace to use ASM storage

Monitoring and Managing Memory

• Oracle Memory Structures
• Automatic PGA Memory Management
• Using the Memory Advisor
• Using Automatic Shared Memory Management to avoid long running query issues

Managing Resources

• Creating a New Resource Plan
• Creating Resource Consumer Groups
• Assigning Users to Resource Consumer Groups
• Adaptive Consumer Group Mapping
• Using Sub-Plans to limit CPU Utilization
• Administering the Resource Manager
• Resource Plan Directives

Automating Tasks with the Scheduler

• Creating a Scheduler Job
• Using Scheduler Programs
• Creating and Using Schedules
• Creating a Job Class
• Prioritizing Jobs within a Window
• Viewing Job Execution Details
• Creating a job that runs a program outside of the database

Return to Top