Oracle Database 11G Administrator Certified Associate
  • La certificación Oracle Certified Associate representa una acreditación inicial dirigida a asegurar que los participantes cuenten con habilidades fundamentales para soportar los productos Oracle, esta certificación les permitirá alcanzar posteriormente a los estudiantes las certificaciones Oracle Certified Professional y Oracle Certified Master.

Habilidades obtenidas


Los estudiantes participantes aprenderán los conceptos de las Bases de Datos Relacionales. Este curso proporciona las bases esenciales de SQL que permite a los desarrolladores escribir consultas contra una o varias tablas, manipular tablas y crear objetos de la base de datos.
- Los alumnos aprenderán a controlar los privilegios a nivel de objeto y de sistema.
- Este curso cubre la creación de índices y restricciones, y modificaciones de objetos del esquema ya existentes en la base de datos.
- El estudiante aprenderá a utilizar las funciones avanzadas de SQL para consultar y manipular datos dentro de la base de datos, utilizar las vistas del diccionario para devolver y crear informes acerca de sus objetos del esquema.
- También se tratan funciones de fecha y ora disponibles en la base de datos Oracle.
- Este curso termina tratando la utilización de las expresiones regulares.
- DDL - Lenguaje de Definición de Datos
- Control de Transacciones
- Consulta
- DML- Lenguaje de Manipulación de Datos 
- Ejecutar sentencias de manipulación de datos (DML) para actualizar datos
- Controlar el acceso de usuario y gestionar objetos de esquema
- Gestionar objetos con las vistas del diccionario de datos
- Devolver datos de filas y columnas de las tablas
- Crear informes para ordenar y filtrar
 


Componentes:

 

- Curso Presencial Oracle Database 11g: Introduction to SQL (D49988GC10)
- Curso Presencial Oracle Database 11g Administration I (D50102GC10)
- Seminario de Certificación.
- 2 Vouchers de Certificación (1Z0_052).
 

INTRODUCTION TO SQL

Introduction (Database Architecture)

List the Oracle Database 10g Main Features
An Overview of: components, internet platform, apps server and developer suite
Describe Relational and Object Relational Database Designs
Review the System Development Life Cycle
Define the term Data Models
Describe different means of Sorting Data
Show how Multiple Tables can be related
Describe how SQL Communicates to the Database.
 

Writing SQL SELECT Statements

Define projection, selection, and join terminology
Review the basic SQL SELECT statement syntax
Select all columns using a wildcard notation from a table
State simple rules and guidelines for writing SQL statements
Write a query containing the arithmetic operators
Create a character expression with the concatenation operator
Using the iSQL*Plus Environment
SQL statements versus iSQL*Plus commands.
 

Restricting and Sorting Data

Limit rows using a selection
Using the WHERE clause to retrieve specific rows
Using the comparison conditions in the WHERE clause
Use the LIKE condition to compare literal values
List the logical conditions AND, OR, NOT
Describe the rules of precedence for the conditions
Sort rows with the ORDER BY clause
Use ampersand substitution in iSQL*Plus to restrict and sort output at run time.
 

Using Single-Row Functions to Customize Output

Show the differences between single row and multiple row SQL functions
Categorize the character functions into case manipulation and character manipulation types
Use the character manipulation functions in the SELECT and WHERE clauses
Explain and use the DATE and numeric functions
Use the SYSDATE function to retrieve the current date in the default format
Introduce the DUAL table as a means to view function results
List the rules for applying the arithmetic operators on dates
Use the arithmetic operators with dates in the SELECT clause.
 

Reporting Aggregated Data Using the Group Functions

Describe and categorize the group functions
Use the group functions
Utilize the DISTINCT keyword with the group functions
Describe how nulls are handled with the group functions
Create groups of data with the GROUP BY clause
Group data by more than one column
Avoid illegal queries with the group functions
Exclude groups of data with the HAVING clause.
 

Displaying Data from Multiple Tables

Identify Types of Joins
Retrieve Records with Natural Joins
Use Table Aliases to write shorter code and explicitly identify columns from multiple tables
Create a Join with the USING clause to identify specific columns between tables
Use the ON clause to specify arbitrary conditions or specify columns to Join
Create a Three-way join with the ON clause to retrieve information from 3 tables
List the Types of Outer Joins LEFT, RIGHT, and FULL
Generating a Cartesian Product.
 

Using sub queries to Solve Queries

List the syntax for sub queries in a SELECT statements WHERE clause
List the guidelines for using sub queries
Describe the types of sub queries
Execute single row sub queries and use the group functions in a sub query
Identify illegal statements with sub queries
Execute multiple row sub queries
Analyze how the ANY and ALL operators work in multiple row sub queries.
 

Using the SET Operators

Use the UNION operator to return all rows from multiple tables and eliminate any duplicate rows
Use the UNION ALL operator to return all rows from multiple tables
Describe the INTERSECT operator
Use the INTERSECT operator
Explain the MINUS operator
Use the MINUS operator
List the SET operator guidelines
Order results when using the UNION operator.
 

Manipulating Data

Write INSERT statements to add rows to a table
Copy rows from another table
Create UPDATE statements to change data in a table
Generate DELETE statements to remove rows from a table
Use a script to manipulate data
Save and discard changes to a table through transaction processing
Show how read consistency works
Describe the TRUNCATE statement.
 

Using DDL Statements to Create and Manage Tables

List the main database objects and describe the naming rules for database objects
Introduce the schema concept
Display the basic syntax for creating a table and show the DEFAULT option
Explain the different types of constraints
Show resulting exceptions when constraints are violated with DML statements
Create a table with a sub query
Describe the ALTER TABLE functionality
Remove a table with the DROP statement and Rename a table.
 

Creating Other Schema Objects

Categorize simple and complex views and compare them
Create a view
Retrieve data from a view
Explain a read-only view
List the rules for performing DML on complex views
Create a sequence
List the basic rules for when to create and not create an index
Create a synonym.
 

Managing Objects with Data Dictionary Views

Describe the structure of each of the dictionary views
List the purpose of each of the dictionary views
Write queries that retrieve information from the dictionary views on the schema objects
Use the COMMENT command to document objects.
 

Controlling User Access

Controlling User Access
System versus Objects Privileges
Using Roles to define user groups
Changing Your Password
Granting Object Privileges
Confirming Privileges Granted
Revoking Object Privileges
Using Database Links.
 

Manage Schema Objects

Using the ALTER TABLE statement
Adding a Column
Modifying a Column
Dropping a Column, Set Column UNUSED
Adding, Enabling and Disabling Constraints
Creating Function-Based Indexes
Performing FLASHBACK operations
External Tables.
 

Manipulating Large Data Sets

Using the MERGE Statement
Performing DML with Sub queries
Performing DML with a RETURNING Clause
Overview of Multi-table INSERT Statements
Tracking Changes in DML.
 

Generating Reports by Grouping Related Data

Overview of GROUP BY Clause
Overview of Having Clause
Aggregating data with ROLLUP and CUBE Operators
Determine subtotal groups using GROUPING Functions
Compute multiple groupings with GROUPING SETS
Define levels of aggregation with Composite Columns
Create combinations with Concatenated Groupings.
 

Managing Data in Different Time Zones

Time Zones
Using date and time functions
Identifying TIMESTAMP Data Types
Differentiating between DATE and TIMESTAMP
Performing Conversion Operations.
 

Searching Data Using Advanced Sub queries

Sub query Overview
Using a Sub query
Comparing several columns using Multiple-Column Sub queries
Defining a Data source Using a Sub query in the FROM Clause
Returning one Value using Scalar Sub query Expressions
Performing ROW by-row processing with Correlated Sub queries
Reusing query blocks using the WITH Clause.
 

Hierarchical Retrieval

Sample Data from the EMPLOYEES Table
The Tree Structure of Employee data
Hierarchical Queries
Ranking Rows with LEVEL
Formatting Hierarchical Reports Using LEVEL and LPAD
Pruning Branches with the WHERE and CONNECT BY clauses
 

ADMINISTRATION WORKSHOP I, RELEASE 2

Introduction (Database Architecture)

Describe course objectives
Explore the Oracle 10g database architecture
 

Installing the Oracle Database Software

Explain core DBA tasks and tools
Plan an Oracle installation
Use optimal flexible architecture
Install software with the Oracle Universal Installer (OUI).
 

Creating an Oracle Database

Create a database with the Database Configuration Assistant (DBCA)
Create a database design template with the DBCA
Generate database creation scripts with the DBCA.
 

Managing the Oracle Instance

Start and stop the Oracle database and components
Use Enterprise Manager (EM)
Access a database with SQL*Plus and iSQL*Plus
Modify database initialization parameters
Understand the stages of database startup
View the Alert log
Use the Data Dictionary.
 

Managing Database Storage Structures

Describe table data storage (in blocks)
Define the purpose of tablespaces and data files
Understand and utilize Oracle Managed Files (OMF)
Create and manage tablespaces
Obtain tablespace information
Describe the main concepts and functionality of Automatic Storage Management (ASM).
 

Administering User Security

Create and manage database user accounts
Authenticate users
Assign default storage areas (tablespaces)
Grant and revoke privileges
Create and manage roles
Create and manage profiles
Implement standard password security features
Control resource usage by users.
 

Managing Schema Objects

Define schema objects and data types
Create and modify tables
Define constraints
View the columns and contents of a table
Create indexes, views and sequences
Explain the use of temporary tables
Use the Data Dictionary.
 

Managing Data and Concurrency

Manage data through SQL
Identify and administer PL/SQL Objects
Describe triggers and triggering events
Monitor and resolve locking conflicts.
 

Managing Undo Data

Explain DML and undo data generation
Monitor and administer undo
Describe the difference between undo and redo data
Configure undo retention
Guarantee undo retention
Use the undo advisor.
 

Implementing Oracle Database Security

Describe DBA responsibilities for security
Apply the principal of least privilege
Enable standard database auditing
Specify audit options
Review audit information
Maintain the audit trail.
 

Configuring the Oracle Network Environment

Use Enterprise Manager for configuring the Oracle network environment
Create additional listeners
Create Net Service aliases
Configure connect-time failover
Control the Oracle Net Listener
Test Oracle Net connectivity
Identify when to use shared versus dedicated servers.
 

Proactive Maintenance

Use statistics
Manage the Automatic Workload Repository (AWR)
Use the Automatic Database Diagnostic Monitor (ADDM)
Describe advisory framework
Set alert thresholds
Use server-generated alerts
Use automated tasks.
 

Performance Management

Use Enterprise Manager pages to monitor performance
Use the SQL Tuning Advisor
Use the SQL Access Advisor
Use Automatic Shared Memory Management
Use the Memory Advisor to size memory buffers
Use performance related dynamic views
Troubleshoot invalid or unusable objects.
 

Backup and Recovery Concepts

Identify the types of failure that may occur in an Oracle Database
Describe ways to tune instance recovery
Identify the importance of checkpoints, redo log files, and archived log files
Configure ARCHIVELOG mode.
 

Performing Database Backups

Create consistent database backups
Back your database up without shutting it down
Create incremental backups
Automate database backups
Monitor the flash recovery area.
 

Performing Database Recovery

Recover from loss of a control file
Recover from loss of a redo log file
Perform complete recovery following the loss of a data file.
 

Performing Flashback

Describe Flashback database
Restore the table content to a specific point in the past with Flashback Table
Recover from a dropped table
View the contents of the database as of any single point in time with Flashback.
 

Query

See versions of a row over time with Flashback Versions Query
View the transaction history of a row with Flashback Transaction Query.
 

Moving Data

Describe available ways for moving data
Create and use directory objects
Use SQL*Loader to load data from a non-Oracle database (or user files)
Explain the general architecture of Data Pump
Use Data Pump Export and Import to move data between Oracle databases
Use external tables to move data