UMBC Training Centers logo

Oracle Database 11g Administrator Certified Associate

Oracle

Course Description | Outline | IT Certification Bootcamps

Introducing Oracle Database 11g

  • List the features of Oracle Database 11g
  • Discuss the basic design, theoretical and physical aspects of a relational database
  • Categorize the different types of SQL statements
  • Describe the data set used by the course
  • Log onto the database using the SQL Developer environment
  • Save queries to files and use script files in SQL Developer

Retrieving Data Using the SQL SELECT Statement

  • List the capabilities of SQL SELECT statements
  • Generate a report of data from the output of a basic SELECT statement
  • Select All Columns
  • Select Specific Columns
  • Use Column Heading Defaults
  • Use Arithmetic Operators
  • Understand Operator Precedence
  • Learn the DESCRIBE command to display the table structure

Restricting and Sorting Data

  • Write queries that contain a WHERE clause to limit the output retrieved
  • List the comparison operators and logical operators that are used in a WHERE clause
  • Describe the rules of precedence for comparison and logical operators
  • Use character string literals in the WHERE clause
  • Write queries that contain an ORDER BY clause sort the output of a SELECT statement
  • Sort output in descending and ascending order

Using Single-Row Functions to Customize Output

  • Describe the differences between single row and multiple row functions
  • Manipulate strings with character function in the SELECT and WHERE clauses
  • Manipulate numbers with the ROUND, TRUNC and MOD functions
  • Perform arithmetic with date data
  • Manipulate dates with the date functions

Using Conversion Functions and Conditional Expressions

  • Describe implicit and explicit data type conversion
  • Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
  • Nest multiple functions
  • Apply the NVL, NULLIF, and COALESCE functions to data
  • Use conditional IF THEN ELSE logic in a SELECT statement

Reporting Aggregated Data Using the Group Functions

  • Use the aggregation functions in SELECT statements to produce meaningful reports
  • Create queries that divide the data in groups by using the GROUP BY clause
  • Create queries that exclude groups of date by using the HAVING clause

Displaying Data From Multiple Tables

  • Write SELECT statements to access data from more than one table
  • View data that generally does not meet a join condition by using outer joins
  • Join a table by using a self join

Using Sub-queries to Solve Queries

  • Describe the types of problem that sub-queries can solve
  • Define sub-queries
  • List the types of sub-queries
  • Write single-row and multiple-row sub-queries

Using the SET Operators

  • Describe the SET operators
  • Use a SET operator to combine multiple queries into a single query
  • Control the order of rows returned when using the SET operators

Manipulating Data

  • Describe each DML statement
  • Insert rows into a table with the INSERT statement
  • Use the UPDATE statement to change rows in a table
  • Delete rows from a table with the DELETE statement
  • Save and discard changes with the COMMIT and ROLLBACK statements
  • Explain read consistency

Using DDL Statements to Create and Manage Tables

  • Categorize the main database objects
  • Review the table structure
  • List the data types available for columns
  • Create a simple table
  • Decipher how constraints can be created at table creation
  • Describe how schema objects work

Creating Other Schema Objects

  • Create a simple and complex view
  • Retrieve data from views
  • Create, maintain, and use sequences
  • Create and maintain indexes
  • Create private and public synonyms

Controlling User Access

  • Differentiate system privileges from object privileges
  • Grant privileges on tables
  • View privileges in the data dictionary
  • Grant roles
  • Distinguish between privileges and roles

Managing Schema Objects

  • Add constraints
  • Create indexes
  • Create indexes using the CREATE TABLE statement
  • Create function-based indexes
  • Drop columns and set column UNUSED
  • Perform FLASHBACK operations
  • Create and use external tables

Managing Objects with Data Dictionary Views

  • Explain the data dictionary
  • Find table information
  • Report on column information
  • View constraint information
  • Find view information
  • Verify sequence information
  • Understand synonyms
  • Add comments

Manipulating Large Data Sets

  • Manipulate data using sub-queries
  • Describe the features of multi-table inserts
  • Use the different types of multi-table inserts
  • Merge rows in a table
  • Track the changes to data over a period of time

Managing Data in Different Time Zones

  • Use data types similar to DATE that store fractional seconds and track time zones
  • Use data types that store the difference between two date-time values
  • Practice using the multiple data-time functions for globalize applications

Retrieving Data Using Sub-queries

  • Write a multiple-column sub-query
  • Use scalar sub-queries in SQL
  • Solve problems with correlated sub-queries
  • Update and delete rows using correlated sub-queries
  • Use the EXISTS and NOT EXISTS operators
  • Use the WITH clause

Regular Expression Support

  • List the benefits of using regular expressions
  • Use regular expressions to search for, match, and replace strings

Exploring the Oracle Database Architecture

  • Oracle Database Architecture Overview
  • Interacting with an Oracle Database
  • Process Architecture
  • Database Storage Architecture
  • Logical and Physical Database Structures
  • Tablespaces and Data Files
  • SYSTEM and SYSAUX Tablespaces
  • Segments, Extents, and Blocks

Preparing the Database Environment

  • Tasks of an Oracle Database Administrator
  • Tools Used to Administer an Oracle Database
  • Installation: System Requirements
  • Optimal Flexible Architecture (OFA)
  • Setting Environment Variables
  • Oracle Universal Installer (OUI)
  • Database Configuration Options
  • Advanced Installation Options

Create an Oracle Database

  • Planning the Database
  • Configuring the Listener
  • Using the DBCA to Create a Database
  • Password Management
  • Creating a Database Design Template
  • Using the DBCA to Delete a Database
  • Using DBCA For Additional Tasks

Managing the Oracle Instance

  • Management Framework
  • Oracle Enterprise Manager
  • Initialization Parameter
  • Database Startup and Shutdown
  • Shutdown Modes
  • Viewing the Alert History
  • Using Trace Files
  • Data Dictionary: Overview

Configuring the Oracle Network Environment

  • Oracle Net Services & Listener
  • Establishing Net Connections
  • Tools for Configuring and Managing the Oracle Network
  • Net Services Administration Pages
  • Naming Methods
  • Directory Naming
  • SGA and PGA
  • When Not to Use a Shared Server

Managing Database Storage Structures

  • Storage Structures
  • How Table Data Is Stored
  • Anatomy of a Database Block
  • Space Management in Tablespaces
  • Tablespaces in the Preconfigured Database
  • Actions with Tablespaces
  • Oracle Managed Files (OMF)
  • ASM: Concepts

Administering User Security

  • Database User Accounts
  • Predefined Accounts: SYS and SYSTEM
  • Administrator Authentication
  • Benefits of Roles
  • Predefined Roles
  • Implementing Password Security Features
  • Assigning Quota to Users

Managing Schema Objects

  • What Is a Schema?
  • Accessing Schema Objects
  • Specifying Data Types in Tables
  • Creating and Modifying Tables
  • Understanding Data Integrity
  • Indexes & Views
  • Sequences
  • Temporary Tables

Managing Data and Concurrency

  • Manipulating Data Through SQL
  • PL/SQL
  • Administering PL/SQL Objects
  • Functions, Procedures, Packages & Triggers
  • Data Concurrency
  • Enqueue Mechanism
  • Resolving Lock Conflicts Using SQL
  • Deadlocks

Managing Undo Data

  • Data Manipulation
  • Transactions and Undo Data
  • Undo Data Versus Redo Data
  • Configuring Undo Retention
  • Guaranteeing Undo Retention
  • Using the Undo Advisor
  • Viewing System Activity

Implementing Oracle Database Security

  • Industry Security Requirements
  • Principle of Least Privilege
  • Monitoring for Compliance
  • Value-Based Auditing
  • Fine-Grained Auditing
  • DBA Auditing
  • Security Updates
  • Applying Security Patches

Database Maintenance

  • Introducing Terminology
  • Optimizer Statistics
  • Automatic Workload Repository (AWR)
  • Automatic Database Diagnostic Monitor (ADDM)
  • Advisory Framework
  • Automated Maintenance Tasks
  • Server-Generated Alerts
  • Reacting to Alerts

Performance Management

  • Performance Monitoring
  • Managing Memory Components
  • Enabling Automatic Memory Management (AMM)
  • Automatic Shared Memory Advisor
  • Using Memory Advisors
  • Dynamic Performance Statistics
  • Troubleshooting and Tuning Views
  • Invalid and Unusable Objects

Backup and Recovery Concepts

  • Part of Your Job
  • Statement Failure
  • User Error
  • Understanding Instance Recovery
  • Phases of Instance Recovery
  • Using the MTTR Advisor
  • Media Failure
  • Archive Log Files

Performing Database Backups

  • Backup Solutions: Overview
  • Oracle Secure Backup
  • User-Managed Backup
  • Terminology
  • Recovery Manager (RMAN)
  • Configuring Backup Settings
  • Backing Up the Control File to a Trace File
  • Monitoring the Flash Recovery Area

Performing Database Recovery

  • Opening a Database
  • Data Recovery Advisor
  • Loss of a Control File
  • Loss of a Redo Log File
  • Data Recovery Advisor
  • Data Failures
  • Listing Data Failures
  • Data Recovery Advisor Views

Moving Data

  • Moving Data: General Architecture
  • Directory Object: Overview
  • SQL*Loader: Overview
  • Data Pump: Overview
  • Performance Initialization Parameters
  • Using Enterprise Manager to Monitor Data Pump Jobs
  • Data Dictionary

Enhancing Database Capabilities

  • Using EM Support Workbench
  • Create a Service Request
  • Package and upload diagnostic data to Oracle Support
  • Track the SR and Implement Repairs
  • Incident Packaging Configuration
  • Working with Oracle Support
  • MetaLink Integration
  • Managing Patches