Oracle Database 11g Administrator Certified Associate

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