ORACLE DBA TRAINING

ORACLE 11g DBA 3-Months

Working with SQL

Retrieving Data Using the SQL SELECT Statement

• List the capabilities of SQL SELECT statements
• Execute a basic SELECT statement

Restricting and Sorting Data

• Limit the rows that are retrieved by a query
• Sort the rows that are retrieved by a query
• Use ampersand substitution to restrict and sort output at runtime

Using Single-Row Functions to Customize Output

• Describe various types of functions available in SQL
• Use character, number, and date functions in SELECT statements

Using Conversion Functions and Conditional Expressions

• Describe various types of conversion functions that are available in SQL
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Apply conditional expressions in a SELECT statement

Reporting Aggregated Data Using the Group Functions

• Identify the available group functions
• Describe the use of group functions
• Group data by using the GROUP BY clause
• Include or exclude grouped rows by using the HAVING clause

Displaying Data from Multiple Tables

• Write SELECT statements to access data from more than one table using equijoins and nonequijoins
• Join a table to itself by using a self-join
• View data that generally does not meet a join condition by using outer joins
• Generate a Cartesian product of all rows from two or more tables

Using Subqueries to Solve Queries

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

Using the Set Operators

• Describe set operators
• Use a set operator to combine multiple queries into a single query
• Control the order of rows returned

Manipulating Data

• Describe each data manipulation language (DML) statement
• Insert rows into a table
• Update rows in a table
• Delete rows from a table
• Control transactions

Using DDL Statements to Create and Manage Tables

• Categorize the main database objects
• Review the table structure
• List the data types that are available for columns
• Create a simple table
• Explain how constraints are created at the time of table creation
• Describe how schema objects work

Creating Other Schema Objects

• Create simple and complex views
• Retrieve data from views
• Create, maintain, and use sequences
• Create and maintain indexes
• Create private and public synonyms

Performing Database Administration

Exploring the Oracle Database Architecture

• Explain the Memory Structures
• Describe the Process Structures
• Overview of Storage Structures

Preparing the Database Environment

• Identify the tools for Administering an Oracle Database
• Plan an Oracle Database installation
• Install the Oracle software by using Oracle Universal Installer (OUI)

Creating an Oracle Database

• Create a database by using the Database Configuration Assistant (DBCA)

Managing the Oracle Instance

• Setting database initialization parameters
• Describe the stages of database startup and shutdown
• Using alert log and trace files
• Using data dictionary and dynamic performance views

Configuring the Oracle Network Environment

• Configure and Manage the Oracle Network
• Using the Oracle Shared Server architecture

Managing Database Storage Structures

• Overview of tablespace and datafiles
• Create and manage tablespaces
• Space management in tablespaces

Administering User Security

• Create and manage database user accounts
• Grant and revoke privileges
• Create and manage roles
• Create and manage profiles

Managing Data and Concurrency

• Monitor and resolve locking conflicts

Managing Undo Data

• Overview of Undo
• Transactions and undo data
• Managing undo

Implementing Oracle Database Security

• Database Security and Principle of Least Privilege
• Work with Standard Database Auditing

Database Maintenance

• Use and manage optimizer statistics
• Use and manage Automatic Workload Repository (AWR)
• Use advisory framework
• Manage Alerts and Thresholds

Performance Management

• Use Automatic Memory Management
• Use Memory Advisors
• Troubleshoot invalid and unusable objects

Intelligent Infrastructure Enhancements

• Use the Enterprise Manager Support Workbench
• Managing Patches

Backup and Recovery Concepts

• Identify the types of failure that can occur in an Oracle database
• Describe ways to tune instance recovery
• Identify the importance of checkpoints, redo log files, and archived log files
• Overview of flash recovery area
• Configure ARCHIVELOG mode

Performing Database Backups

• Create consistent database backups
• Back up your database without shutting it down
• Create incremental backups
• Automate database backups
• Manage backups, view backup reports and monitor the flash recovery area

Performing Database Recovery

• Overview of Data Recovery Advisor
• Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Moving Data

• Describe and use methods to move data(Directory objects, SQL*Loader, External Tables)
• Explain the general architecture of Oracle Data Pump
• Use Data Pump Export and Import to move data between Oracle databases

Working With PL / SQL

Introduction to PL/SQL

• Explain the need for PL/SQL
• Explain the benefits of PL/SQL

PL/SQL Cursor

• Using Explicit Cursors
• Distinguish between usage of implicit and explicit cursors
• Declare and control explicit cursors, use simple loops and cursor FOR loops to fetch data

Creating Procedures and Functions

• Work with procedures
• Differentiate between a procedure and a function
• Describe the uses of functions
• Work with functions (create, invoke and remove functions)

Creating Database triggers

• Creating Triggers
• Describe different types of triggers and their uses
• Create database triggers
• Manage triggers