Oracle 9i OCA Certification
(Oracle Certified Associate)

Oracle OCA Course Overview
This course gives the Oracle database administrator (DBA) a firm foundation in fundamental database administrative tasks. Students learn about loading and transporting for databases, and the utilities used to perform these activities. Students are also introduced to networking concepts and configuration parameters, as well as how to solve some common network problems.
After completing this course, students should be able to:

OCP Exam# 1Z0-007: Introduction to Oracle 9i: 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
Create and use an inline view
Perform Top 'N' Analysis

Creating Other Database Objects
Create, maintain and use sequences
Create and maintain indexes
Create private and public synonyms

Controlling User Access
Create users
Create Roles to ease setup and maintenance of the security model
Use the GRANT and REVOKE statements to grant and revoke object privileges

OCP Exam #1Z0-031: Oracle 9i: DBA Fundamentals l

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

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

Data Dictionary Content and Usage
Identify key data dictionary components
Identify the contents and uses of the data dictionary
Query the data dictionary

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

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

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

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 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 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

Maintaining Data Integrity
Implement data integrity constraints, Maintain integrity constraints
Obtain constraint information from 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

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