Oracle Developer/Administrator Certified Associate

Why should you become a OCA

As a Oracle Certified Associate, you will cultivate industry-supported skills and credentials that you will be able to transfer to future employment opportunities. With your OCA certification, you will be able to demonstrate and promote the fact that you have the necessary skills to do your job effectively and you are certified by Oracle Coprporation the leader in Database Management Systems. 

Oracle Developer/Administrator Certified Associate (Exams & Courses)

Exam#	Course, Duration, Click for detailed outlines
1Z0-051	SQL Fundamentals I
1Z0-147	Program with PL/SQL
1Z0-052	Oracle Database Administration Fundamentals

Exam Scoring: 

Exam Number	Exam Title	Total Questions	Questions Correct	Passing %	Duration
1Z0-051	SQL Fundamentals I	70	42	60%	2 hours
1Z0-147	Program with PL/SQL	66	51	77%	1.5 hours
1Z0-052	Oracle Database: Fundamentals I	70	47	66%	1.5 hours

Oracle routinely publishes new versions of exams, and the passing score across versions may differ.

Oracle Developer/Administrator Certified Associate (OCA)

SQL Fundamental I

This course introduces students to the fundamentals of SQL using Oracle Database 11g database technology. In this course students learn the concepts of relational databases and the powerful SQL programming language. This course provides the essential SQL skills that allow developers to write queries against single and multiple tables, manipulate data in tables, and create database objects.The students also learn to use single row functions to customize output, use conversion functions and conditional expressions and use group functions to report aggregated data. Demonstrations and hands-on practice reinforce the fundamental concepts. In this course, students use Oracle SQL Developer and SQL *Plus as the tool. Course Objectives: Retrieve row and column data from tables with the SELECT statement Create reports of sorted and restricted data Employ SQL functions to generate and retrieve customized data Display data from multiple tables using the ANSI SQL 99 JOIN syntax Create reports of aggregated data Use the SET operators to create subsets of data Run data manipulation statements (DML) to update data in the Oracle Database 11g Run data definition language (DDL) statements to create and manage schema objects Identify the major structural components of the Oracle Database 11g

Course Topics:

Introduction Listing the features of Oracle Database 11g Discussing the basic design, theoretical and physical aspects of a relational database Describing the development environments for SQL Describing Oracle SQL Developer, Describing the data set used by the course Retrieving Data Using the SQL SELECT Statement Listing the capabilities of SQL SELECT statements. Generating a report of data from the output of a basic SELECT statement Using arithmetic expressions and NULL values in the SELECT statement Using Column aliases Using concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword Displaying the table structure using the DESCRIBE command Restricting and Sorting Data Writing queries with a WHERE clause to limit the output retrieved Using the comparison operators and logical operators Describing the rules of precedence for comparison and logical operators Using character string literals in the WHERE clause  Writing queries with an ORDER BY clause to sort the output Sorting output in descending and ascending order Using the Substitution Variables Using Single-Row Functions to Customize Output Differentiating between single row and multiple row functions Manipulating strings using character functions Manipulating numbers with the ROUND, TRUNC and MOD functions Performing arithmetic with date data Manipulating dates with the date functions Using Conversion Functions and Conditional Expressions Describing implicit and explicit data type conversion Using the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions Nesting multiple functions Applying the NVL, NULLIF, and COALESCE functions to data Using conditional IF THEN ELSE logic in a SELECT statement Reporting Aggregated Data Using the Group Functions Using the aggregation functions in SELECT statements to produce meaningful reports Using AVG, SUM, MIN, and MAX function Handling Null Values in a group function Creating queries that divide the data in groups by using the GROUP BY clause Creating queries that exclude groups of date by using the HAVING clause Displaying Data from Multiple Tables Writing SELECT statements to access data from more than one table Joining Tables Using SQL:1999 Syntax Viewing data that does not meet a join condition by using outer joins Joining a table by using a self join Creating Cross Joins Using Sub-queries to Solve Queries Using a Subquery to Solve a Problem Executing Single-Row Sub-queries Using Group Functions in a Sub-query Using Multiple-Row Subqueries Using the ANY and ALL Operator in Multiple-Row Sub-queries Using the SET Operators Describing the SET operators Using a SET operator to combine multiple queries into a single query Using UNION, UNION ALL, INTERSECT, and MINUS Operator Using the ORDER BY Clause in Set Operations Manipulating Data Adding New Rows to a Table Using the INSERT statement Changing Data in a Table Using the UPDATE Statement Using DELETE and TRUNCATE Statements Saving and discarding changes with the COMMIT and ROLLBACK statements Implementing Read Consistency Using the FOR UPDATE Clause Using DDL Statements to Create and Manage Tables Categorizing Database Objects Creating Tables using the CREATE TABLE Statement Describing the data types Describing Constraints Creating a table using a subquery Altering and Dropping a table Creating Other Schema Objects Creating, modifying, and retrieving data from a view Performing Data manipulation language (DML) operations on a view Dropping a view Creating, using, and modifying a sequence Creating and dropping indexes Creating and dropping synonyms

Program with PL/SQL

This course introduces students to PL/SQL and helps them understand the benefits of this powerful programming language. In the class, students learn to create PL/SQL blocks of application code that can be shared by multiple forms, reports, and data management applications. Students learn to create anonymous PL/SQL blocks and are introduced to stored procedures and functions. They learn about declaring variables, trapping exceptions and they also learn to declare and control cursors.In class students learn to develop, execute and manage PL\SQL stored program units like procedures, functions, packages and database triggers. Student also learns to manage object dependencies and recompilation of invalid objects. This course also describes the characteristics and ways of manipulation of large objects. Students are introduced to the utilization of some of the Oracle-supplied packages. Course Objectives: Write PL/SQL code to interface with the database Design PL/SQL program units that execute efficiently Use PL/SQL programming constructs and conditional control statements Handle run-time errors Describe stored procedures and functions Write dynamic SQL for more coding flexibility Design PL/SQL code for predefined data types, local subprograms, additional programs and standardized constants and exceptions Use the compiler warnings infrastructure Manipulate large objects Create triggers to solve business challenges Manage dependencies between PL/SQL subprograms Schedule PL/SQL jobs to run independently Create stored procedures and functions Design PL/SQL packages to group and contain related constructs Create overloaded package subprograms for more flexibility Categorize the Oracle supplied PL/SQL packages

Course Topics:

Introduction to PL/SQL What is PL/SQL  PL/SQL Environment Benefits of PL/SQL Overview of the Types of PL/SQL blocks Create and Execute a Simple Anonymous Block Generate Output from a PL/SQL Block iSQL*Plus as PL/SQL Programming Environment Declaring PL/SQL Identifiers Identify the Different Types of Identifiers in a PL/SQL subprogram Use the Declarative Section to Define Identifier List the Uses for Variables Store Data in Variables Declare PL/SQL Variables Writing Executable Statements Describe Basic Block Syntax Guidelines Use Literals in PL/SQL Customize Identifier Assignments with SQL Functions Use Nested Blocks as Statements Reference an Identifier Value in a Nested Block Qualify an Identifier with a Label Use Operators in PL/SQL Use Proper PL/SQL Block Syntax and Guidelines Interacting with the Oracle Server Identify the SQL Statements You Can Use in PL/SQL Include SELECT Statements in PL/SQL Retrieve Data in PL/SQL with the SELECT Statement Avoid Errors by Using Naming Conventions When Using Retrieval and DML Statements Manipulate Data in the Server Using PL/SQL The SQL Cursor concept Use SQL Cursor Attributes to Obtain Feedback on DML Save and Discard Transactions Writing Control Structures Control PL/SQL Flow of Execution Conditional processing Using IF Statements Conditional Processing CASE Statements Handle Nulls to Avoid Common Mistakes Build Boolean Conditions with Logical Operators Use Iterative Control with Looping Statements Working with Composite Data Types Learn the Composite Data Types of PL/SQL Records and Tables Use PL/SQL Records to Hold Multiple Values of Different Types Inserting and Updating with PL/SQL Records Use INDEX BY Tables to Hold Multiple Values of the Same Data Type Using Explicit Cursors Cursor FOR Loops Using Sub-queries Increase the Flexibility of Cursors By Using Parameters Use the FOR UPDATE Clause to Lock Rows Use the WHERE CURRENT Clause to Reference the Current Row Use Explicit Cursors to Process Rows Explicit Cursor Attributes Cursors and Records Handling Exceptions Handling Exceptions with PL/SQL Predefined Exceptions Trapping Non-predefined Oracle Server Errors Functions that Return Information on Encountered Exceptions Trapping User-Defined Exceptions Propagate Exceptions Use The RAISE_APPLICATION_ERROR Procedure To Report Errors To Applications Creating Stored Procedures Describe the block structure for PL/SQL stored procedures Invoke a stored procedure/function from different tools Call a stored procedure with host variables from iSQL*Plus, Forms, Java, C, etc Invoke a stored procedure from an anonymous block or another stored procedure List the CREATE OR REPLACE PROCEDURE syntax Identify the development steps for creating a stored procedure Use the SHOW ERRORS command View source code in the USER_SOURCE dictionary view Creating Stored Functions Describe stored functions List the CREATE OR REPLACE FUNCTION syntax Identify the steps to create a stored function Execute a stored function Identify the advantages of using stored functions in SQL statements Identify the restrictions of calling functions from SQL statements Remove a function Creating Packages List the advantages of packages Describe packages Show the components of a package Diagram the visibility of constructs within a package Develop a package Create the package specification Declare public constructs Create the package body Using More Package Concepts List the benefits of overloading Show overloading example Use forward declarations in packages Create a one-time only procedure (package code initialization) List the restrictions on package functions used in SQL Encapsulate code in a package demonstration Invoke a user-defined package function from a SQL statement Utilize the persistent state of package variables Utilizing Oracle Supplied Packages in Application Development List the various uses for the Oracle supplied packages Reuse pre-packaged code to complete various tasks from developer to DBA purposes Use the DESCRIBE command to view the package specifications and overloading Explain how DBMS_OUTPUT works (in conjunction with SET SERVEROUPUT ON) Interact with operating system files with UTL_MAIL Describe file processing with UTL_FILE Review UTL_FILE routines and exceptions Use UTL_FILE to generate a report to a file Dynamic SQL and Metadata Describe using native dynamic SQL List the execution flow of SQL Show the syntax for the EXECUTE IMMEDIATE statement for native dynamic SQL Create a procedure to generate native dynamic SQL using EXECUTE IMMEDIATE to delete rows from a table Describe the DBMS_SQL package Provide an example of DBMS_SQL List the advantages of using Native Dynamic SQL Over the DBMS_SQL package Design Considerations for PL/SQL Code Standardize constants with a constant package Standardize exceptions with an exception handling package Introduce local sub-programs Use local sub-programs Track run time errors with an exception package Describe the NOCOPY compiler hint Use the NOCOPY compiler hint Explain the effects of NOCOPY Managing Dependencies Define dependent and referenced objects Diagram dependencies with code, views, procedures, and tables Manage local dependencies between a procedure, view, and a table Analyze a scenario of local dependencies Display direct dependencies using the USER_DEPENDENCIES view Run the UTL_DTREE.SQL script to create objects that enable you to view direct and indirect dependencies Predict the effects of changes on dependent objects Manipulating Large Objects Describe a LOB object Diagram the anatomy of a LOB Manage and list the features on internal LOBs Describe, manage, and secure BFILEs Create and use the DIRECTORY object to access and use BFILEs Prepare BFILEs for usage Use the BFILENAME function to load BFILEs Describe the DBMS_LOB package Creating Triggers Describe the different types of triggers and how they execute List the benefits and guidelines of using database triggers Show how triggers are executed with a basic database trigger example Show syntax and create DML triggers, and list the DML trigger components Explain the firing sequence of triggers Create a DML statement and row level triggers Use the OLD and NEW qualifiers to reference column values Use conditional predicates with triggers Applications for Triggers Create triggers for DDL events of CREATE, ALTER, and DROP Create triggers for system events of SERVERERROR, STARTUP, SHUTDOWN, LOGON and LOGOFF Define a mutating table Describe business application scenarios for implementing with triggers Describe the privileges required to manage triggers Understanding and Influencing the PL/SQL Compiler List the features of native compilation Describe the features of the PL/SQL compiler in Oracle Database 10g Identify the 3 parameters used to influence compilation (PLSQL_CODE_TYPE, PLSQL_DEBUG, PLSQL_OPTIMIZE_LEVEL) Show how to set the parameters Describe the dictionary view used to see how code is compiled (USER_PLSQL_OBJECTS) Change the parameter settings, recompile code, and view the results Describe the compiler warning infrastructure in Oracle Database, list the steps used in setting compiler warning levels

Oracle Database Administration Fundamentals

This course is designed to give students a firm foundation in basic administration of Oracle Database 11g. In this class, students learn how to install and maintain Oracle Database 11g. Students gain a conceptual understanding of the Oracle database architecture and how its components work and interact with one another. Students learn how to create an operational database and properly manage the various structures in an effective and efficient manner including performance monitoring, database security, user management, and backup/recovery techniques. The lesson topics are reinforced with structured hands-on practices. Course Objectives: Describe Oracle Database Architecture Install and configure Oracle Database 11g Configure Oracle Net services Manage the database storage structures Create and administer user accounts Perform basic backup and recovery of a database Manage users and schemas Manage data and concurrency Monitor and administer undo data Backup and recover a database Monitor performance Use the database diagnostic monitor

Course Topics:

Exploring the Oracle Database Architecture Oracle Database Architecture Overview Interacting with an Oracle Database Process Architecture Database Storage Architecture Logical and Physical Database Structures Tablespaces and Data Files SYSTEM and SYSAUX Tablespaces Segments, Extents, and Blocks Preparing the Database Environment Tasks of an Oracle Database Administrator Tools Used to Administer an Oracle Database Installation: System Requirements Optimal Flexible Architecture (OFA) Setting Environment Variables Oracle Universal Installer (OUI) Database Configuration Options Advanced Installation Options Creating an Oracle Database Managing the Oracle Instance Management Framework Oracle Enterprise Manager Initialization Parameter Database Startup and Shutdown Shutdown Modes Viewing the Alert History Using Trace Files Data Dictionary: Overview Configuring the Oracle Network Environment Oracle Net Services & Listener Establishing Net Connections Tools for Configuring and Managing the Oracle Network Net Services Administration Pages Naming Methods Directory Naming SGA and PGA When Not to Use a Shared Server Managing Database Storage Structures Storage Structures How Table Data Is Stored Anatomy of a Database Block Space Management in Tablespaces Tablespaces in the Preconfigured Database Actions with Tablespaces Oracle Managed Files (OMF) ASM: Concepts Administering User Security Database User Accounts Predefined Accounts: SYS and SYSTEM Administrator Authentication Benefits of Roles Predefined Roles Implementing Password Security Features Assigning Quota to Users Managing Schema Objects What Is a Schema? Accessing Schema Objects Specifying Data Types in Tables Creating and Modifying Tables Understanding Data Integrity Indexes & Views Sequences Temporary Tables Managing Data and Concurrency Manipulating Data Through SQL PL/SQL Administering PL/SQL Objects Functions, Procedures, Packages & Triggers Data Concurrency  Enqueue Mechanism Resolving Lock Conflicts Using SQL Deadlocks Managing Undo Data Data Manipulation Transactions and Undo Data Undo Data Versus Redo Data Configuring Undo Retention Guaranteeing Undo Retention Using the Undo Advisor Viewing System Activity Implementing Oracle Database Security Industry Security Requirements Principle of Least Privilege Monitoring for Compliance Value-Based Auditing Fine-Grained Auditing DBA Auditing Security Updates Applying Security Patches Database Maintenance Introducing Terminology Optimizer Statistics Automatic Workload Repository (AWR) Automatic Database Diagnostic Monitor (ADDM) Advisory Framework Automated Maintenance Tasks Server-Generated Alerts Reacting to Alerts Performance Management Performance Monitoring Managing Memory Components Enabling Automatic Memory Management (AMM) Automatic Shared Memory Advisor Using Memory Advisors Dynamic Performance Statistics Troubleshooting and Tuning Views Invalid and Unusable Objects Backup and Recovery Concepts Statement Failure User Error Understanding Instance Recovery Phases of Instance Recovery Using the MTTR Advisor Media Failure Archive Log Files Performing Database Backups Backup Solutions: Overview Oracle Secure Backup User-Managed Backup Terminology Recovery Manager (RMAN) Configuring Backup Settings Backing Up the Control File to a Trace File Monitoring the Flash Recovery Area Performing Database Recovery Opening a Database Data Recovery Advisor Loss of a Control File Loss of a Redo Log File Data Recovery Advisor Data Failures Listing Data Failures Data Recovery Advisor Views Moving Data Moving Data: General Architecture Directory Object: Overview SQL*Loader: Overview Data Pump: Overview Performance Initialization Parameters Using Enterprise Manager to Monitor Data Pump Jobs Data Dictionary Enhancing Database Capabilities Using EM Support Workbench Create a Service Request Package and upload diagnostic data to Oracle Support Track the SR and Implement Repairs Incident Packaging Configuration Working with Oracle Support MetaLink Integration Managing Patches