9i DBA Certification

Oracle9i Database Administrator Certified Professional
[{Introduction to Oracle9i SQL1Z0-007 OR
Introduction to Oracle: SQL and PL/SQL1Z0-001 +
Oracle9i Database: Fundamentals I1Z0-031 }
= Oracle9i Database Administrator Certified Associate

+

{Oracle9i Database: Fundamentals II1Z0-032 +
Oracle9i Database: Performance Tuning1Z0-033 }]
= Oracle9i Database Administrator Certified Professional


Exam:1Z0-007

Exam Price: $95.00 USD
Duration: 120 minutes
Number of Questions: 52
Passing Score: 71%


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




Exam: 1Z0-001


Exam Price: $125.00 USD
Duration: 120 minutes
Number of Questions: 57
Passing Score: 68%

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


Create and maintain constraints
Describe a view
Create a view
Retrieve data through a view
Insert, update, and delete data through a view
Drop a view


Oracle Data Dictionary
Describe the data dictionary views a user may access
Query data from the data dictionary


Writing Control Structures
Identify the uses and types of control structures
Construct an IF statement
Construct and identify different loop statements
Use logic tables
Control block flow using nested loops and labels


Working with Composite Datatypes
Create user-defined PL/SQL records
Create a record with the % ROWTYPE attribute
Create a PL/SQL table
Create a PL/SQL table of records
Describe the difference between records, tables, and tables of records


Overview of Relational Databases, SQL, and PL/SQL
Discuss the theoretical and physical aspects of a relational database
Describe the Oracle implementation of the RDBMS and ORDBMS
Describe the use and benefits of PL/SQL


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


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


Other Database Objects
Describe database objects and their uses
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


Declaring Variables
List the benefits of PL/SQL
Describe the basic PL/SQL block and its sections
Describe the significance of variables in PL/SQL
Declare PL/SQL variables
Execute a PL/SQL block


Writing Explicit Cursors
Distinguish between an implicit and an explicit cursor
Use a PL/SQL record variable
Write a cursor FOR loop


Advanced Explicit Cursor Concepts
Write a cursor that uses parameters
Determine when a FOR UPDATE clause in a cursor in required
Determine when to use the WHERE CURRENT OF clause
Write a cursor that uses a subquery


Writing Basic SQL Statements
List the capabilities of SQL SELECT statements
Execute a basic SELECT statement
Differentiate between SQL statements and SQL*Plus commands


Restricting and Sorting Data
Limit the rows retrieved by a query
Sort the rows retrieved by a query


Single Row Functions
Describe the various types of functions available in SQL
Use character, number, and date functions in SELECT statements
Describe the use of conversion functions


Displaying Data from Multiple Tables
Write SELECT statements to access data from more than one table by using equality and nonequality joins
View data that generally does not meet a join condition by using outer joins
Join a table to itself


Writing Executable Statements
Describe the significance of the executable section
Write statements in the executable section
Describe the rules of nested blocks
Execute and test a PL/SQL block
Use coding conventions


Interacting with the Oracle Server
Write a successful SELECT statement in PL/SQL
Declare the datatype and size of a PL/SQL variable dynamically
Write DML statements in PL/SQL
Control transactions in PL/SQL
Determine the outcome of SQL DML statements


Handing Exceptions
Define PL/SQL exceptions
Recognize unhandled exceptions
List and use different types of PL/SQL exception handlers
Trap unanticipated errors
Describe the effect of exception progagation in nested blocks
Customize PL/SQL exception messages


Subqueries
Describe the types of problems that subqueries can solve
Define subqueries
List the types of subqueries
Write single-row and multiple-row subqueries


Multiple-Column Subqueries
Write multiple-column subqueries
Describe and explain the behavior of subqueries when null values are retrieved
Write subqueries in a FROM clause


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


Including Constraints
Describe constraints
Create and maintain constraints




Exam: 1Z0-031


Exam Price: $125.00 USD
Duration: 105 minutes
Number of Questions: 65
Passing Score: 60%


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



Exam: 1Z0-032
Exam Price: $125.00 USD
Duration: 120 minutes
Number of Questions: 63
Passing Score: 66%


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



Exam Number: 1Z0-033

Exam Price: $125.00 USD
Duration: 120 minutes
Number of Questions: 70
Passing Score: 64%


Exam Topics
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
Explain 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