 |
Introduction to Oracle9i:
SQL®
| Writing Basic SQL Select
Statements |
| * |
List the capabilities of SQL SELECT
statements |
| * |
Execute a basic SELECT statement
|
| * |
Differentiate between SQL
statements and iSQL*Plus commands |
| Restricting and Sorting Data |
| * |
Limit the rows retrieved by a query
|
| * |
Sort the rows retrieved by a query
|
| Single-Row Functions |
| * |
Describe various types of functions available in SQL
|
| * |
Use character, number, and date functions in SELECT
statements |
| * |
Use conversion functions |
| Displaying Data from
Multiple Tables |
| * |
Write SELECT statements to access
data from more than one table using equality and nonequality joins
|
| * |
View data that generally does not
meet a join condition by using outer joins |
| * |
Join a table to itself using a
self-join |
| Aggregating Data using Group
Functions |
| * |
Identify the available group
functions |
| * |
Use group functions |
| * |
Group data using the GROUP BY
clause |
| * |
Include or exclude grouped rows by
using the HAVING clause | |
| Subqueries |
| * |
Describe the types of problems that subqueries can
solve |
| * |
Define subqueries |
| * |
List the types of subqueries |
| * |
Write single-row and multiple-row subqueries
|
| Producing Readable
Output with iSQL*Plus |
| * |
Produce queries that require a
substitution variable |
| * |
Produce more readable output
|
| * |
Create and execute script files
|
| Manipulating Data |
| * |
Describe each DML statement |
| * |
Insert rows into a table |
| * |
Update rows in a table |
| * |
Delete rows from a table |
| * |
Merge rows in a table |
| * |
Control transactions |
| Creating and Managing
Tables |
| * |
Describe the main database objects
|
| * |
Create tables |
| * |
Describe the datatypes that can be
used when specifying column definition |
| * |
Alter table definitions
|
| * |
Drop, rename and truncate tables
|
| Including Constraints |
| * |
Describe constraints |
| * |
Create and maintain constraints
|
|
| Creating Views |
| * |
Describe a view |
| * |
Create, alter the definition, and drop a view
|
| * |
Retrieve data through a view |
| * |
Insert, update and delete data through a view
|
| Creating Other
Database Objects |
| * |
Create, maintain and use sequences
|
| * |
Create and maintain indexes
|
| * |
Create private and public synonyms
| |
Oracle9i Database: Fundamentals I™
| Oracle Architectural Components
|
| * |
Describe the Oracle architecture
and its main components |
| * |
Describe the structures involved in
connecting a user to an Oracle instance |
| Getting Started With
the Oracle Server |
| * |
Identify common database
administrative tools available to a DBA |
| * |
Identify the features of the Oracle
Universal Installer |
| * |
Explain the benefits of Optimal
Flexible Architecture |
| * |
Set up password file authentication
|
| * |
List the main components of the
Oracle Enterprise Manager and their uses |
| Maintaining the Control File |
| * |
Explain the uses of the control
file |
| * |
Describe the contents of the
control file |
| * |
Multiplex and manage the control
file |
| * |
Manage the control file with Oracle
Managed Files |
| * |
Obtain control file information
|
| Managing Tablespaces
and Data files |
| * |
Describe the logical structure of
tablespaces within the database |
| * |
Create tablespaces |
| * |
Change the size of the tablespace
|
| * |
Allocate space for temporary
segments |
| * |
Change the status of tablespaces
|
| * |
Change the storage settings of
tablespaces |
| * |
Implement Oracle Managed Files
|
| Managing Tables |
| * |
Identify the various methods of storing data
|
| * |
Describe Oracle data types |
| * |
Distinguish between an extended versus a restricted
ROWID |
| * |
Describe the structure of a row |
| * |
Create regular and temporary tables |
| * |
Manage storage structures within a table
|
| * |
Reorganize, truncate, drop a table |
| * |
Drop a column within a table
|
|
| Managing an Oracle Instance
|
| * |
Create and manage initialization
parameter files |
| * |
Configure OMF |
| * |
Start up and shut down an instance
|
| * |
Monitor the use of diagnostic files
|
| Creating a Database |
| * |
Describe the prerequisites necessary for database
creation |
| * |
Create a database using Oracle Database Configuration
Assistant |
| * |
Create a database manually
|
| Maintaining Redo Log Files
|
| * |
Explain the purpose of online redo
log files |
| * |
Describe the structure of online
redo log files |
| * |
Control log switches and
checkpoints |
| * |
Multiplex and maintain online redo
log files |
| * |
Manage online redo log files with
OMF |
| Storage Structure and
Relationships |
| * |
Describe the logical structure of
segments within the database |
| * |
Describe the segment types and
their uses |
| * |
List the keywords that control
block space usage |
| * |
Obtain information about storage
structures from the data dictionary |
| Managing Undo Data |
| * |
Describe the purpose of undo data |
| * |
Implement Automatic Undo Management
|
| Managing Indexes |
| * |
Describe the different types of indexes and their
uses |
| * |
Create various types of indexes |
| * |
Reorganize indexes |
| * |
Drop indexes |
| * |
Get index information from the data dictionary
|
| * |
Monitor the usage of an index
|
| Data Dictionary
Content and Usage |
| * |
Identify key data dictionary
components |
| * |
Identify the contents and uses of
the data dictionary |
| * |
Query the data dictionary
|
|
| Managing Password
Security and Resources |
| * |
Manage passwords using profiles
|
| * |
Administer profiles |
| * |
Control use of resources using
profiles |
| * |
Obtain information about profiles,
password management and resources |
| Managing Users |
| * |
Create new database users |
| * |
Alter and drop existing database users |
| * |
Monitor information about existing users
|
| Managing Privileges |
| * |
Identify system and object privileges |
| * |
Grant and revoke privileges |
| * |
Identify auditing capabilities
|
| Managing Roles |
| * |
Create and modify roles |
| * |
Control availability of roles |
| * |
Remove roles |
| * |
Use predefined roles |
| * |
Display role information from the data dictionary
|
| Maintaining Data Integrity |
| * |
Implement data integrity
constraints |
| * |
Maintain integrity constraints
|
| * |
Obtain constraint information from
the data dictionary |
| Using Globalization Support
|
| * |
Choose database character set and
national character set for a database |
| * |
Specify the language- dependent
behavior using initialization parameters, environment variables and
the ALTER SESSION command |
| * |
Use the different types of National
Language Support (NLS) parameters |
| * |
Explain the influence on
language-dependent application behavior |
| * |
Obtain information about
Globalization Support usage
| |
Oracle9i Database: Fundamentals II ™
| Networking Overview |
| * |
Explain solutions included with Oracle9i for managing
complex networks |
| * |
Describe Oracle networking add-on solutions
|
| Basic Oracle Net Architecture
|
| * |
Explain the key components of the
Oracle Net layered architecture |
| * |
Explain Oracle Net Services role in
client server connections |
| * |
Describe how web client connections
are established through Oracle networking products
|
| Basic Net Server-Side
Configuration |
| * |
Identify how the listener responds
to incoming connections |
| * |
Configure the listener using Oracle
Net Manager |
| * |
Control the listener using the
Listener Control Utility (lsnrctl) |
| * |
Describe Dynamic Service
Registration |
| * |
Configure the listener for IIOP and
HTTP connections |
| Basic Oracle Net
Services Client-Side Configuration |
| * |
Describe the difference between
host naming and local service name resolution |
| * |
Use Oracle Net Configuration Assistant to configure:
Host Naming, Local naming method, Net service names |
| * |
Perform simple connection troubleshooting
|
| Usage and
Configuration of the Oracle Shared Server |
| * |
Identify the components of the
Oracle Shared Server |
| * |
Describe the Oracle Shared Server
architecture |
| * |
Configure the Oracle Shared Server
|
| * |
Identify and explain usefulness of
related dictionary views |
| Backup and Recovery Overview |
| * |
Describe the basics of database
backup, restore and recovery |
| * |
List the types of failure that may
occur in an Oracle environment |
| * |
Define a backup and recovery
strategy |
| Instance and Media
Recovery Structures |
| * |
Describe the Oracle processes,
memory structures, and files relating to recovery |
| * |
Identify the importance of
checkpoints, redo log files, and archived log files |
| * |
Describe ways to tune instance
recovery |
|
| Configuring the
Database Archiving Mode |
| * |
Describe the differences between
Archivelog and Noarchivelog modes |
| * |
Configure a database for Archivelog
mode |
| * |
Enable automatic archiving
|
| * |
Perform manual archiving of logs
|
| * |
Configure multiple archive
processes |
| * |
Configure multiple destinations,
including remote destinations |
| Oracle Recovery
Manager Overview and Configuration |
| * |
Identify the features and
components of RMAN |
| * |
Describe the RMAN repository and
control file usage |
| * |
Describe channel allocation
|
| * |
Describe the Media Management
Library interface |
| * |
Connect to RMAN without the
recovery catalog |
| * |
Configure the RMAN environment
|
| User-Managed
Backups |
| * |
Describe user-managed backup and
recovery operations |
| * |
Discuss backup issues associated
with read tablespaces |
| * |
Perform closed database backups
|
| * |
Perform open database backups |
| * |
Back up the control file |
| * |
Perform cleanup after a failed online backup
|
| * |
Use the DBVERIFY utility to detect corruption
|
| RMAN Backups |
| * |
Identify types of RMAN specific backups |
| * |
Use the RMAN BACKUP command to create sets
|
| * |
Back up the control file |
| * |
Back up the archived redo log files |
| * |
Use the RMAN COPY command to create image copies
|
| User-Managed Complete Recovery
|
| * |
Describe media recovery
|
| * |
Perform recovery in Noarchivelog
mode |
| * |
Perform complete recovery in
Archivelog mode |
| * |
Restore datafiles to different
locations |
| * |
Relocate and recover a tablespace
by using archived redo log files |
| * |
Describe read-only tablespace
recovery |
| RMAN Complete Recovery |
| * |
Describe the use of RMAN for restoration and recovery
|
| * |
Perform recovery in Noarchivelog mode |
| * |
Perform complete recovery in Archivelog mode
|
| * |
Restore data files to different locations
|
| * |
Reloate and recover a tablespace by using archived
redo log files |
|
| User-Managed
Incomplete Recovery |
| * |
Describe the steps of incomplete
recovery |
| * |
Perform an incomplete database
recovery |
| * |
Identify the loss of current online
redo log files |
| RMAN Incomplete Recovery
|
| * |
Perform an incomplete database recovery using UNTIL
TIME |
| * |
Perform an incomplete database recovery using UNTIL
SEQUENCE |
| RMAN Maintenance |
| * |
Perform cross checking of backups and copies
|
| * |
Update the repository when backups have been deleted
|
| * |
Change the availability status of backups and copies
|
| * |
Make a backup or copy exempt from the retention
policy |
| * |
Catalog backups made with operating system commands
|
| Recovery Catalog
Creation and Maintenance |
| * |
Describe the contents of the
recovery catalog |
| * |
Create the recovery catalog
|
| * |
Maintain the recovery catalog by
using RMAN commands |
| * |
Use RMAN to register,
resynchronize, and reset a database |
| * |
Query the recovery catalog to
generate reports and lists |
| * |
Create, store, and run scripts
|
| * |
Describe methods for backing up and
recovering the recovery catalog |
| Transporting Data
Between Databases |
| * |
Describe the uses of the Export and
Import utilities |
| * |
Describe Export and Import concepts
and structures |
| * |
Perform simple Export and Import
operations |
| * |
List guidelines for using Export
and Import |
| Loading Data into a Database
|
| * |
Demonstrate usage of direct- load
insert operations |
| * |
Describe the usage of SQL*Loader
|
| * |
Perform basic SQL*Loader operations
|
| * |
List guidelines for using
SQL*Loader and direct-load insert |
|
Oracle9i Database: Performance Tuning™
| Overview of Oracle9i Performance
Tuning |
| * |
Describe the roles associated with
the database tuning process |
| * |
Describe the dependency between
tuning in different development phases |
| * |
Describe service level agreements
|
| * |
Describe appropriate tuning goals
|
| * |
Describe the most common tuning
problems |
| * |
Describe the tuning considerations
during development and production |
| * |
Describe performance and safety
tradeoffs |
| Sizing the Buffer Cache
|
| * |
Describe how the buffer cache is used by different
Oracle processes |
| * |
Describe the tuning issues related to the buffer
cache |
| * |
Monitor the use of the buffer cache, also the
different pools within the buffer cache |
| * |
Implement dynamic SGA allocation |
| * |
Set the DB_CACHE_ADVICE parameter |
| * |
Create and size multiple buffer pools |
| * |
Detect and resolve free list contention
|
| Sizing other SGA Structures
|
| * |
Monitor and size the redo log
buffer |
| * |
Monitor and size the java pool
|
| * |
Control the amount of Java session
memory used by a session |
| * |
Configure the instance to use I/O
Slaves |
| * |
Configure and use multiple DBW
processors |
| Monitoring and
Detecting Lock Contention |
| * |
Define levels of locking
|
| * |
Describe possible causes of
contention |
| * |
Use Oracle utilities to detect lock
contention |
| * |
Resolve contention in an emergency
|
| * |
Prevent locking problems
|
| * |
Recognize Oracle errors arising
from deadlocks |
| Using Oracle Blocks Efficiently |
| * |
Describe the correct usage of
extents and Oracle blocks |
| * |
Explain space usage and the high
water mark |
| * |
Determine the high water mark
|
| * |
Recover space from sparsely
populated segments |
| * |
Describe and detect chaining and
migration of Oracle blocks |
| * |
Perform index reorganization
|
| * |
Monitor indexes to determine usage
| |
| Diagnostic and Tuning Tools
|
| * |
Explain how the alert.log file is
used |
| * |
Explain how background trace files
are used |
| * |
Explain how user trace files are
used |
| * |
Describe the statistics kept in the
dynamic performance views |
| * |
Explain how StatsPack collects
statisticsd |
| * |
Collect statistics using StatsPack
|
| * |
Collect statistics using Enterprise
Manager |
| * |
Use other tuning tools
|
| Database Configuration
and I/O Issues |
| * |
Explain the advantages of
distributing different Oracle file types |
| * |
Describe reasons for partitioning
data in tablespaces |
| * |
Diagnose tablespace usage problems
|
| * |
Describe how checkpoints work
|
| * |
Monitor and tune checkpoints
|
| * |
Monitor and tune redo logs
|
| Optimize Sort Operations
|
| * |
Describe how sorts are performed |
| * |
Identify the SQL operations which require sorts
|
| * |
Differentiate between disk and memory sorts
|
| * |
Create and monitor temporary tablespaces
|
| * |
Reduce total sorts and disk sorts |
| * |
Determine the number of sorts performed in memory
|
| * |
Set old and new sort parameters |
| |
|
| Tuning Oracle Shared Server
|
| * |
Identify issues associated with
managing users in a Shared Server environment |
| * |
Diagnose and resolve performance
issues with Oracle Shared Server processes |
| * |
Configure the Oracle Shared Server
environment to optimize performance |
| Application Tuning |
| * |
Describe the role of the DBA in tuning Applications
|
| * |
Explain different storage structures, and why one
storage structure may be preferred over another |
| * |
Explain the different types of indexes |
| * |
Explain Index Organized Tables |
| * |
Describe partitioning methods |
| * |
Explain the use of the DBMS_STATS procedure
|
| * |
Describe Materialized Views and use of Query Rewrites
|
| * |
List requirements for OLTP, DSS and Hybrid Systems
|
|
| SQL Statement Tuning |
| * |
Describe how the Optimizer is used |
| * |
Explain the concept of plan stability |
| * |
Use stored outlines |
| * |
Describe how hints are used |
| * |
Use SQL Trace and TKPROF |
| * |
Collect statistics on indexes and tables
|
| * |
Describe the use of histograms |
| * |
Copy statistics between databases
|
| Sizing the Shared Pool |
| * |
Measure and tune the library cache hit ratio
|
| * |
Measure and tune the dictionary cache hit ratio
|
| * |
Size and pin objects in the shared pool |
| * |
Tune the shared pool reserve space |
| * |
Describe the UGA and session memory considerations
|
| * |
Exp lain other tuning issues related to the shared
pool |
| * |
Set the large pool |
| Diagnosing Contention For Latches
|
| * |
Describe the purpose of latches
|
| * |
Describe the different types of
latch request |
| * |
Diagnose contention for latches
|
| * |
Tune the appropriate resources to
minimize latch contention |
| Tuning the Operating
System and Using Resource Manager |
| * |
Describe different system
architectures |
| * |
Describe the primary steps of OS
tuning |
| * |
Identify similarities between OS
and DB tuning |
| * |
Understand virtual memory and
paging |
| * |
Explain the difference between a
process and a thread |
| * |
Set up Database Resource Manager
|
| * |
Assign users to Resources Manager
groups |
| * |
Create resource plans within groups
| |
Oracle Database 10g: New Features for Administrators™
| Installation |
| * |
Describe installation
new features support |
| * |
Describe installation
performance enhancements |
| Server Configuration
|
| * |
Simplify instance
configuration using a subset of initialization
parameters |
| * |
Use policy-based
database configuration framework |
| * |
View database usage statistics through
EM |
| Load and Unload
Data |
| * |
Transport tablespaces
across different platforms |
| * |
Explain Data Pump
architecture |
| * |
Monitor a Data Pump job
|
| * |
Use Data Pump export and import
|
| * |
Create external tables for data
population |
| * |
Define your external table properties
| | | | |
