Oracle Certified Associate (OCA)



   
   




About OCA: Oracle Certified Asssociate


The Oracle Certified Associates (OCA) exam is conducted in two levels. Both levels of the exam MUST be passed in order to pass the exam. Candidate have two options for second level. Candidate can select any one for the second level.


  1. OCA  Level I - Introduction to Oracle 9i SQL (1z0-007)

  2. OCA Level II - Oracle database 10g : Administrator I (1z0-042)
  3. OR
    OCA Level II - Program with PL/SQL (1z0-147)



BACK TO TOP

Levels

Associate Certification Path
STEP 1 – PASS THIS EXAM
Choose one of these exams
1Z0-007 Introduction to Oracle9i SQL®
or
1Z0-047 Oracle Database SQL Expert
or
1Z0-051 Oracle Database 11g: SQL Fundamentals I
V
STEP 2 – PASS THIS EXAM
Choose one of these exams
1Z0-147 Program with PL/SQL
or
1Z0-144 Oracle Database 11g: Program with PL/SQL
Oracle PL/SQL Developer Certified Associate
Next Steps
Oracle Forms Developer Certified Professional
Oracle Advanced PL/SQL Developer Certified Professional
 
Associate Certification Path
STEP 1 - PASS THIS EXAM
Choose one of these exams
1Z0-007Introduction to Oracle9i SQL
or
1Z0-047Oracle Database SQL Expert
or
1Z0-051Oracle Database 11g: SQL Fundamentals I
V
STEP 2 - PASS THIS EXAM
Oracle Database 10g: Administration I 1Z0-042
Oracle Database 10g Administrator Certified Associate
Next Steps
Oracle Certified Professional

BACK TO TOP

Syllabus

  • OCA Level I
  • OCA Level II (DBA)
  • OCA Level II (Developer)

OCA  I  Level
Introduction to Oracle 9i SQL
(1z0-007)

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


OCA II Level
Oracle database 10g : Administrator I
(1z0-042)

Architecture
[  ] Outline the Oracle Architecture and its main Components
[  ] Explain the Oracle instance architecture

Installing the Oracle Database Software
[  ] Identify common database administrative tools available to a DBA
[  ] Use optimal flexible architecture
[  ] Install software with Oracle Universal Installer
[  ] Identify and configure commonly used environment variables
[  ] Use Installer Log

Creating an Oracle Database
[  ] Use DBCA to Create a database
[  ] Use DBCA to Delete a database
[  ] Use DBCA to manage templates

Managing the Oracle Instance
[  ] Use Enterprise Manager
[  ] Use SQL*Plus and iSQL*Plus to access the Oracle Database
[  ] Modify database initialization parameters
[  ] Describe the stages of database startup
[  ] Describe the database shutdown options
[  ] View the database alert log
[  ] Use dynamic performance views

Managing Database Storage Structures
[  ] Describe how table row data is stored in blocks
[  ] Define the purpose of tablespaces and data files
[  ] Explain space management in tablespaces
[  ] Create tablespaces
[  ] Manage tablespaces: alter, drop, take offline, put online, add data files, make read-only or read-write, generate DDL
[  ] Obtain tablespace information
[  ] Explain key features and benefits of ASM

Administering User Security
[  ] Create and manage database user accounts
[  ] Create and manage roles
[  ] Grant and revoke privileges
[  ] Create and manage profiles
Managing Schema Objects
[  ] Create and modify tables
[  ] Define constraints and states of constraints
[  ] Dropping and truncating tables
[  ] Create and use B-Tree and Bitmap indexes
[  ] Create Views
[  ] Create sequences
[  ] Use data dictionary

Managing Data and Concurrency
[  ] Manipulate data through the use of SQL
[  ] Identify and administer PL/SQL objects
[  ] Describe triggers and triggering events
[  ] Define levels of locking
[  ] List possible causes of lock conflict
[  ] Monitor and resolve lock conflicts

Managing Undo Data
[  ] Monitor and administer undo
[  ] Configure undo retention
[  ] Describe the relationship between undo and transactions
[  ] Size the undo tablespace

Implementing Oracle Database Security
[  ] Apply the principle of least privilege
[  ] Audit database activity
[  ] Implement Fine-Grained Auditing

Configuring the Oracle Network Environment
[  ] Use Database Control to Create additional listeners
[  ] Use Database Control to Create Oracle Net service aliases
[  ] Control Oracle Net Listeners
[  ] Identify when to use shared servers versus dedicated servers

Proactive Maintenance
[  ] Gather optimizer statistics
[  ] Manage the Automatic Workload Repository
[  ] Use the Automatic Database Diagnostic Monitor (ADDM)
[  ] Set warning and critical alert thresholds
[  ] React to performance issues
Performance Management
[  ] Use enterprise manager to view performance
[  ] Tune SQL by using SQL tuning advisor
[  ] Tune SQL by using SQL access advisor
[  ] Use automatic shared memory management
[  ] Use the memory advisor to size memory buffer

Backup and Recovery Concepts
[  ] Describe the types of failure that may occur in an Oracle Database
[  ] Identify the importance of checkpoints, redo log files, and archived log files
[  ] Tuning instance recovery
[  ] Configure a database for recoverability
[  ] Configure ARCHIVELOG mode

Performing Database Backup
[  ] Create consistent database backups
[  ] Back up your database without shutting it down
[  ] Create incremental backups
[  ] Automate database backups
[  ] Backup a control file to trace
[  ] Monitor flash recovery area

Performing Database Recovery
[  ] Recover from loss of a Control file
[  ] Recover from loss of a Redo log file
[  ] Recover from loss of a system-critical data file
[  ] Recover from loss of a non system-critical data file

Performing Flashback
[  ] Describe flashback database
[  ] Resotore the table contents to a specific point in time
[  ] Recover from a dropped table
[  ] Use Flashback Query to view the contents of the database as of any single point of time
[  ] View transaction history or row with flashback transaction query

Moving Data
[  ] Describe the general architecture of Data Pump
[  ] Use Data Pump export and import to move data between Oracle databases
[  ] Load data with SQL Loader
[  ] Use external tables to move data

OCA II Level
Program with PL/SQL
(1z0-147)

Overview of PL/SQL Programs
[  ] Describe a PL/SQL program construct
[  ] List the components of a PL/SQL block
[  ] List the benefits of subprograms
[  ] Describe how a stored procedure/function is invoked

Creating Procedures
[  ] Define what a stored procedure is
[  ] List the development steps for creating a procedure
[  ] Create a procedure
[  ] Describe the difference between formal and actual parameters
[  ] List the types of parameter modes
[  ] List the methods for calling a procedure with parameters
[  ] Describe the DEFAULT option for parameters
[  ] Create a procedure with parameters
[  ] Invoke a procedure that has parameters
[  ] Define a subprogram in the declarative section of a procedure
[  ] Describe how exceptions are propagated
[  ] Remove a procedure

Creating Functions
[  ] Define what a stored function is
[  ] Create a function
[  ] List how a function can be invoked
[  ] List the advantages of user-defined functions in SQL statements
[  ] List where user-defined functions can be called from within an SQL statement
[  ] Describe the restrictions on calling functions from SQL statements
[  ] Remove a function
[  ] Describe the differences between procedures and functions

Managing Subprograms
[  ] Contrast system privileges with object privileges
[  ] Grant privileges
[  ] Contrast invokers rights with definers rights
[  ] Identify views in the data dictionary to manage stored objects


Creating Packages
[  ] Use DESCRIBE command to describe packages and list their possible components
[  ] Identify a package specification and body
[  ] Create packages: Create related variables , cursors, constants, exceptions, procedures, and functions
[  ] Designate a package construct as either public or private
[  ] Invoke a package construct
[  ] Use a bodiless package
[  ] Drop Packages
[  ] Identify benefits of Packages

More Package Concepts
[  ] Write packages that use the overloading feature
[  ] Use Forward Referencing
[  ] Describe errors with mutually referential subprograms
[  ] Initialize variables with a one-time-only procedure
[  ] Identify persistent states in package variables and cursors
[  ] Identify restrictions on using Packaged functions in SQL statements
[  ] Invoke packaged functions from SQL
[  ] Use PL/SQL tables and records in Packages

Oracle Supplied Packages
[  ] Describe the benefits of Execute Immediate over DBMS_SQL for Native Dynamic SQL
[  ] Identify the flow of execution
[  ] Use EXECUTE IMMEDIATE
[  ] Describe the use and application of some Oracle server-supplied packages: DBMS_SQL, DBMS_OUTPUT, UTL_FILE

Manipulating Large Objects
[  ] Compare and contrast LONG and large object (LOB) data types
[  ] Describe LOB datatypes and how they are used
[  ] Differentiate between internal and external LOBs
[  ] Identify and Manage Bfiles
[  ] Migrate from LONG To LOB
[  ] Use the DBMS_LOB PL/SQL package
[  ] Create LOB columns and populate them
[  ] Perform SQL operations on LOBS: Update LOBs with SQL, Select from LOBS, Delete LOBS
[  ] Describe the use of temporary LOBs
Creating Database Triggers
[  ] Describe the different types of triggers
[  ] Describe database triggers and their uses
[  ] List guidelines for designing triggers
[  ] Create a DML trigger
[  ] List the DML trigger components
[  ] Describe the trigger firing sequence options
[  ] Use conditional predicates in a DML trigger
[  ] Create a row level trigger
[  ] Create a statement level trigger
[  ] Use the OLD and NEW qualifiers in a database trigger
[  ] Create an INSTEAD OF trigger
[  ] Describe the difference between stored procedures and triggers
[  ] Describe the trigger execution model
[  ] Alter a trigger status
[  ] Remove a trigger

More Trigger Concepts
[  ] Define what a database trigger is
[  ] Describe events that cause database triggers to fire
[  ] Create a trigger for a DDL statement
[  ] Create a trigger for a system event
[  ] Describe the functionality of the CALL statement
[  ] Describe the cause of a mutating table
[  ] List what triggers can be implemented for
[  ] List the privileges associated with triggers
[  ] View trigger information in the dictionary views

Managing Dependencies
[  ] Track procedural dependencies
[  ] Describe dependent objects and referenced objects
[  ] View dependency information in the dictionary views
[  ] Describe how the UTLDTREE script is used
[  ] Describe how the IDEPTREE and DEPTREE procedures are used
[  ] Describe a remote dependency
[  ] List how remote dependencies are governed
[  ] Describe when a remote dependency is unsuccessfully recompiled
[  ] Describe when a remote dependency is successfully recompiled
[  ] List how to minimize dependency failures

BACK TO TOP

Question Pattern


OCA  I  Level
Introduction to Oracle 9i SQL
(1z0-007)

Exam code Name No. of questions Duration Pass mark
1z0-007 Introduction to Oracle 9i SQL 52 Questions 120 minutes Passing at 71%

OCA II Level
Oracle database 10g : Administration I
(1z0-042)

Exam code Name No. of questions Duration Pass mark
1z0-042 Oracle database 10g:
Administration I
84 Questions 120 minutes Passing at 68%

OCA II Level
Program with PL/SQL
(1z0-147)

Exam code Name No. of questions Duration Pass mark
1z0-147 Program with PL/SQL 66 Questions 90 minutes Passing at 77%

BACK TO TOP

© 2012 - 2013 Dr. M.G.R. University
Designed by : Salim & Rahul