Oracle 10g DBA (OCP Certification)
Modules
·
Developing SQL Queries for Oracle Databases
In this module, you learn to use the full potential of SQL to
write complex and robust queries for your Oracle databases. You also learn how to
choose the best query method for each application and test queries to ensure
accuracy and avoid common errors and pitfalls.
·
Oracle PL/SQL Programming
The PL/SQL, a
flexible procedural extension to SQL, increases productivity, performance,
scalability, portability and security. PL/SQL's tight integration with Oracle
databases allows application developers to build and deploy distributed
applications with considerable strength and flexibility.
In this course, you learn techniques
to design PL/SQL applications that solve complex business problems.
·
Oracle
Database 10g Introduction
This module will give you knowledge and skills to take advantage of the Oracle
Database 10g server. Hands-on exercises provide practical experience in
applying relational database concepts, leveraging the SQL language and
programming with PL/SQL.
·
Oracle
Database 10g Administration
This module will give you the knowledge and skills to create and configure
Oracle 10g databases, manage memory and storage effectively, and deploy essential
procedures to maintain your organization's database and ensure its
availability.
·
Oracle
Database 10g: Backup, Recovery and Server Tuning
This module will give you extensive experience employing backup and
recovery strategies on Oracle systems. You apply techniques to solve a range of
issues, including media and site failures, invalid user input, lock contention,
and poor memory and storage allocation.
INTRODUCTION AND OVERVIEW
The uses of SQL
queries
SQL's central role
Why SQL can be both easy and
difficult
Enhancing query
performance
Query optimization
Choosing the best alternative
Guaranteeing read
consistency
Within a SQL statement
Between multiple statements
USING ADVANCED SQL FUNCTIONS TO BUILD QUERIES
Single-row
functions
String manipulation functions
Simulating IF...THEN...ELSE
with functions
Handling regular expressions
with Oracle 10g functions
Aggregate
functions
Grouping in several levels
Grouping and NULLs
CUBE and ROLLUP
Performance and grouping
PERFORMING EXTENSIVE ANALYSIS WITH ANALYTIC FUNCTIONS
Calculating ranks
RANK and DENSE_RANK
ROW_NUMBER depending on ORDER
BY
Calculating percentiles
Defining sliding
window boundaries
By row number
By value
By time interval
Extending the use
of aggregates
Partitioning in multiple
levels
Computing running totals
Comparing row and aggregate
values
DEVELOPING COMPLEX JOINS
Using inner and
outer joins
Building multiple table joins
When to use theta joins
Grouping and joins
Joins and performance
How and when to
use self-joins
Joining a table to itself
Implementing recursive
self-joins with CONNECT BY
CONNECT BY and join
simultaneously
Oracle 10g
enhancements to CONNECT BY
Applying the ANSI
standard join syntax
INNER JOIN
CROSS JOIN
LEFT, RIGHT and FULL OUTER
JOIN
Subtle differences between
new ANSI and old Oracle style
Using the set
operators
INTERSECT
MINUS
BUILDING SUBQUERIES
Non-correlated
subqueries
Subqueries in several levels
Subqueries that return NULL
Multiple row subqueries
Subqueries in the
FROM clause
Breaking up a complex problem
into manageable pieces
Factoring subqueries for
reusability
An alternative to views
Using correlated
subqueries
Fetching main query values
The EXISTS operator
Subqueries in updates
Subqueries as
expressions
Subqueries in the column list
Correlated and non-correlated
subqueries in expressions
USING VIEWS AND TEMPORARY TABLES
Overcoming
obstacles with views
Multiple group levels in one
query
How views impact performance
Temporary tables
as alternatives to views
Avoiding interference from
other users
Tailoring temporary tables
for improved performance
INTRODUCTION AND OVERVIEW
PL/SQL
fundamentals
Declaring variables
Flow control constructs
Oracle9i
and 10g PL/SQL features
Bulk bind native dynamic SQL
CASE statement process flow
Referencing PL/SQL records in
DML
Associative arrays
subscripted by VARCHAR2
Multiset operators for
collections
DATA MANIPULATION TECHNIQUES
Maintaining data
with DML statements
Employing the RETURNING INTO
clause
Solving the
fetch-across-commit problem
Managing data
retrieval with cursors
Implications of explicit and
implicit cursors
Cursor attributes
Cursor FOR LOOPs
Embedding cursor expressions
in SELECT statements
Cursor variables
Strong vs. weak cursor
variables
Passing cursor variables to
other programs
DEVELOPING WELL-STRUCTURED AND ERROR-FREE CODE
Error handling
using EXCEPTIONs
Predefined and user
EXCEPTIONs
Propagation and scope
"Retrying" problem transactions
with EXCEPTION processing
Debugging PL/SQL
blocks
Viewing the source code from
the perspective of the compiler
Interpreting compiler
messages
ACHIEVING MAXIMUM REUSABILITY
Writing stored
procedures and functions
Calling and invoking server-side
logic
Passing input and output
parameters
Implementing an autonomous
transaction
Definer rights vs. invoker
rights
Developing safe
triggers
Using :OLD and :NEW bind variables in
row-level triggers
Implementing complex business
rules
Exploiting new trigger types:
schema and database level
Coding
user-written functions
Calling PL/SQL functions from
SQL
Overcoming SQL limitations
with user-written functions
Building table-valued
functions
EXPLOITING COMPLEX DATATYPES
Collection types
PL/SQL tables, nested tables,
VARRAYs
Stepping through dense and
non-consecutive collections
Bulk binding for
high performance
Moving data into and out of
PL/SQL blocks
BULK COLLECT INTO
FORALL
BULK cursor attributes
INVOKING NATIVE DYNAMIC SQL
Finessing the
compiler
The EXECUTE IMMEDIATE
statement
The RETURNING INTO clause
Types of dynamic
SQL
Building SQL statements
during runtime
Autogenerating standard code
PACKAGE TIPS AND TECHNIQUES
Creating packages
Package structure: SPEC and
BODY
Overloading for polymorphic
effects
Deploying PL/SQL
in a distributed environment
Fat-client vs. thin-client
approaches
Structuring packages to meet
requirements
Designing
maintainable applications
Evaluating application
frameworks
Bodiless packages for all
application definitions
Declaring global variables
ORACLE 10g TECHNOLOGY OVERVIEW
Installation, configuration
& architecture
Relational database concepts
Logical data modeling
Specifying attributes,
entities, relationships and candidate keys
RETRIEVING AND CONTROLLING DATA
Working with
SQL*Plus and iSQL*Plus
Configuring environment
settings
Defining connections using
login.sql
Implementing case-insensitive
queries
Assessing SQL
fundamentals
Selecting, filtering and
ordering results
Avoiding pitfalls in null
values
Employing built-in SQL
functions such as ADD_MONTHS, TO_DATE, NVL, COALESCE
Pattern matching with regular
expressions
Combining result sets with
set operators
Exploiting SQL
techniques
Oracle joins vs. ANSI joins
Constructing outer joins and
self joins
Grouping data, applying
aggregate functions and the HAVING clause
Performing simple &
correlated subqueries
Comparing CASE and DECODE
functions
Manipulating data
Inserting, updating and
deleting data
Streamlining DML with MERGE
Locking data and managing
transactions
DESIGNING AND FORMULATING THE DATABASE
Applying the
physical design
Mapping logical model to
physical design
Establishing a storage
framework
Creating users and schemas
Identifying datatypes such as
LOB and TIMESTAMP
Constructing and
managing tables
Building tables with CREATE
TABLE
Altering and dropping tables
Restoring data with Flashback
Accessing OS files with
external tables
Leveraging the data
dictionary
Creating scripts against the
data dictionary
Developing views
and sequences
Assembling views to filter
data
Generating unique IDs using
sequences
Maintaining
integrity
Determining referential
integrity with primary, unique and foreign keys
Imposing business rules with
CHECK constraints
Handling transactions and
data with deferred and enforced constraints
ADVANCING SECURITY AND PERFORMANCE
Enforcing
security
Verifying with password aging
Classifying system and object
privileges
Controlling access with roles
Accessing schemas via
synonyms
Enriching
performance
Guidelines for defining
optimal indexes
Creating unique, non-unique
and composite indexes
Monitoring index usage
OPTIMIZING SERVER PROGRAMMING
Invoking PL/SQL
fundamentals
Declaring variables and constants
Defining stored procedures
and functions
Operating with
cursor types
Controlling implicit and
explicit cursors
Passing parameters to cursors
Simplifying cursors with FOR
LOOPs
Improving insert and delete
performance with CURRENT OF or ROWID
Establishing program control
IF...THEN...ELSE vs. CASE
statements
Creating WHILE LOOPs and FOR
LOOPs
Error handling with
predefined exceptions
IMPLEMENTING SERVER DEVELOPMENT TECHNIQUES
Processing arrays
Defining and managing PL/SQL
tables
Using records for multicolumn
arrays
Assembling
triggers
Distinguishing statement and
row triggers
Regulating triggers with
predicates
Building INSTEAD OF triggers
Constructing
packages
Creating and administering
packages
Advantages of employing packages
APPLYING ORACLE OBJECTS
Defining object types, object
columns, REF columns and object tables
Processing VARRAYs and nested
tables
Developing stored procedures
with objects
OVERVIEW OF ORACLE 10g ADMINISTRATION
Your
responsibilities as an Oracle DBA
Configuring Oracle 10g
databases
Controlling user access and
operations
Monitoring system usage
Ensuring database
availability
The Oracle 10g
architecture
Instances vs. databases
Oracle processes and memory
usage
Determining database file structure
Processing transactions with
Oracle 10g
CONSTRUCTING AN ORACLE 10g DATABASE
Creating and
dropping the database
Setting the initialization
parameters
Dynamic and static parameters
Creating control files and
log files
Reducing administration with
Oracle managed files
Defining
tablespace structure
Supporting applications that
require multiple block sizes
Creating UNDO and SYSAUX
tablespaces
Starting and
stopping the database
Mounting and opening the
database
Text-based and server
parameter files
Connecting with SYSDBA
privilege
PERFORMING SPACE MANAGEMENT
Logical and
physical storage structures
Improving sort performance
with temporary tablespace groups
Bigfile and locally managed
tablespaces
Space management with blocks
& extents
Controlling
storage for database objects
Managing space with
PCTFREE/PCTUSED
Moving and redefining tables
online
Shrinking tables online to
regain space
Resuming operations after
space-related failures
Managing LOB storage with
chunks
AUTOMATING DATABASE MANAGEMENT WITH ORACLE ENTERPRISE MANAGER
(OEM) 10g DATABASE CONTROL
The OEM
architecture
Strengths and weaknesses of
OEM
Navigating the graphical
interface
Administering
with Database Control
Setting thresholds and
generating alerts
Verifying changes in the data
dictionary
MANAGING USERS AND RESOURCES
Creating user
accounts
Implementing password
controls
Allocating space quotas on
tablespaces
Limiting resource usage with
profiles
Maintaining
security
System and object privileges
Setting up and working with
roles
ENHANCING AVAILABILITY AND ADMINISTRATION WITH PARTITIONS
Building table
partitions and subpartitions
Partitioning types: range,
hash and list
Defining partition key values
Tailoring storage for
individual partitions
Splitting and merging
partitions
Configuring index
partitions
Deploying local, global,
prefixed and non-prefixed indexes
Rebuilding unusable indexes
BACKING UP AND RECOVERING THE DATABASE
Safeguarding the
database
Causes of failure
Role of the redo log and
control file
Ensuring recovery using
archiving
Protecting and tracing the
control file
Selecting a
backup strategy
Implementing hot and cold
backups
Partial online and offline
backups
Restoring tables with Data
Pump
Performing Oracle
10g recovery
Full database recovery
Recovering corrupted
tablespaces
Tracking row history with
flashback
Restoring dropped tables from
the recycle bin
FUNDAMENTAL TUNING CONCEPTS
Sizing the buffer cache
Balancing disk I/O &
memory allocation
Shared pool sizing
Index storage considerations
THE ORACLE 10g ARCHITECTURE
Instances and databases
The System Global Area (SGA)
and background processes
Automated storage
configurations
Optimal flexible architecture
IMPLEMENTING BACKUP AND RECOVERY STRATEGIES
Protecting the
database
Choosing appropriate backup
strategies
Identifying points of
possible failure
Safeguarding redo logs and
control files
Managing archive logs
Performing
backups
Generating full database
backups
Implementing partial online
backups
Backing up and creating
control files
Transporting
tablespaces
Establishing a consistent
dataset
Migrating across hardware
platforms
Achieving full
database recovery
Recovering tablespaces and
datafiles
Reconstructing datafiles
without backups
Repairing online redo logs
Flashing back the database
DISASTER RECOVERY USING HOT STANDBY DATABASES
Configuring the
standby environment
Creating and synchronizing
the standby database
Maintaining a read-only
standby database
Preserving the performance of
the primary database
Physical vs. logical standby
databases
Moving operations
to the standby
Failing over and switching to
the standby facility
Achieving No-Data-Loss
recovery
AUTOMATING BACKUP AND RECOVERY WITH RECOVERY MANAGER (RMAN)
Setting up Oracle
Recovery Manager
Creating the Recovery Catalog
Registering databases for
recovery
Configuring channels and
redundancy
Recovery Manager backups
Full and incremental backups
Scripting the backup activity
Maintaining the catalog
Listing and reporting on
backups
Performing
automated recovery
Restoring and recovering data
from backup sets and image copies
Monitoring the recovery
process
Rolling forward image copies
with incremental backups
Tuning backup processes with
block change tracking
ANALYZING REDO LOG FILES WITH LOGMINER
Configuring
LogMiner
Creating the LogMiner
dictionary
Manually listing log files to
be analyzed
Automatically choosing log files
using continuous mining
Adding supplemental log data
Interpreting
LogMiner information
Auditing changes to specific
columns
Performing fine-grained
recovery
TUNING THE ORACLE 10g SERVER
Tuning the SGA
Maximizing the use of the
shared pool
Tuning the buffer cache with
the Buffer Cache Advisor
Reducing I/O with multiple
buffer pools
Monitoring latch contention
Configuring Startup with
database level triggers
Automating memory
management
Managing Program Global Area
(PGA)
Enabling dynamic memory
allocation
Optimizing
transactions and queries
Determining block contention
Delayed block cleanout
Compressing table data
Monitoring latch contention
and waits
APPLYING DIAGNOSTIC TECHNIQUES
Managing the workload
repository
Identifying contention with
the Automatic Database Diagnostic Monitor (ADDM)
Examining the alert log