Oracle Training

Attend Oracle Training Courses to Build Your Expertise

COURSE DESCRIPTION:

This is a 2-day class (6-hours per day) that includes lecture, lab and testing for up to 12 students.

Attendees will learn how to access and manipulate Oracle data using SQL (Structured Query Language).

The attendees will construct SQL statements and scripts to solve simple and complex business questions by writing SQL in a variety of tools and applications.

Lectures and topics are enhanced with live illustrations and hands-on exercises.

COURSE OBJECTIVES:

  1. Introduction to SQL commands for data extraction and manipulation.
  2. Introduction to SQL commands to create Oracle objects including tables, indexes and views.
  3. Exposure to Oracle tools SQL*Plus and SQL Developer.
  4. Exposure to Quest Software's Toad application.
  5. Introduction to advanced SQL topics including group functions, joins, analytical SQL, tuning and much more.
  6. Creating reports

AUDIENCE:​

The focus of this course is to give a working knowledge of SQL to non-IT people or to people who are new to the relational database world.

PREREQUISITES:

None.

ATTENDEES RECEIVE:

  • Study Guide with presentations and relevant white papers
  • SQL Reference pocket guide
  • Example files with SQL and SQL*Plus scripts
7

COURSE OUTLINE:​

Day 1: Introduction to Oracle SQL

  • Oracle RDBMS Architecture overview
  • Introduction to Toad, Toad Data Point, SQL*Plus and SQL Developer
  • Introduction to SQL DML (Data Manipulation Language)
    1. Database queries (SELECT)
    2. Data Manipulation (INSERT, UPDATE, DELETE)
  • Introduction to SQL DDL (Data Dictionary Language)
    1. Create Tables
    2. Create Indexes
    3. Create Views
  • Introduction to SQL Scripts

Day 2: Advanced SQL Topics

  • Functions
  • Advanced SQL
    1. Joins
    2. ANSI SQL Syntax
    3. Sub-Queries
  • Group Functions
  • Analytical SQL
    1. Windowing
    2. Partitioning
    3. Ranking
    4. Percentiles
  • Intro to SQL Tuning
  • Referential Integrity
  • Using ODBC Drivers and MS Products
  • Building HTML ReportsType your paragraph here.

COURSE DESCRIPTION:

This is a 2-day class (6-hours per day) that includes lecture, lab and testing for up to 12 students.

Attendees will learn how to develop Oracle procedures, functions and packages using PL/SQL (Programming Language extension of Structured Query Language). They will also learn about error handling and debugging.

The attendees will construct PL/SQL statements and scripts to solve simple and complex business questions by writing PL/SQL in a variety of tools and applications.

Lectures and topics are enhanced with live illustrations and hands-on exercises.

COURSE OBJECTIVES:

  1. Introduction to PL/SQL syntax and language structure.
  2. Introduction to PL/SQL commands to develop Oracle procedures, functions and database triggers.
  3. Exposure to Oracle tools SQL*Plus and SQL Developer.
  4. Exposure to Quest Software's application named Toad.
  5. Introduction to advanced PL/SQL topics including cursor management, exception handling, PL/SQL Profiler, PL/SQL Debugger and much more.

AUDIENCE:

The focus of this course is to give a working knowledge of PL/SQL to students who already have a working knowledge of SQL.

PREREQUISITES:

Working knowledge of SQL.

ATTENDEES RECEIVE:

  • Study Guide with presentations and relevant white papers
  • PL/SQL Reference pocket guide
  • Example files with SQL and SQL*Plus scripts
  • Tips and Techniques for both Toad and SQL Developer
8

COURSE OUTLINE:

Day 1: Introduction to Oracle PL/SQL

  • Oracle RDBMS Architecture overview
  • Introduction to SQL*Plus, SQL Developer and Toad
  • Introduction to PL/SQL
    1. Syntax
    2. Structure
    3. Variables
    4. Control Structures
      1. IF-Then-Else
      2. Case
      3. Looping
    5. Cursors
      1. Implicit
      2. Explicit
      3. Cursor Loops

Day 2: Advanced PL/SQL Topics

  • PL/SQL Exception Handling
  • Procedures, Functions and Packages
  • Passing Parameters
  • PL/SQL Profiling
  • PL/SQL Debugging
  • PL/SQL New Features

COURSE DESCRIPTION:

This is a 5-day class (6-hours per day) that includes lecture, lab and testing for up to 12 students.

Attendees will learn tips and techniques on how to advance their expertise when programming in SQL and PL/SQL. They will learn tuning tips and debugging techniques.

The attendees will work with a variety of SQL statements and tune them by making coding changes based on their review of the explain plans, modifying indexes and using hints and coding style to control the explain plans. They will also learn how to use tools such as index monitoring, SQL Trace and the PL/SQL profiler. They will be instructed on how Oracle selects indexes, why they are sometimes not used and how to tell which indexes are being used or not.

The attendees will find poorly-performing components of PL/SQL routines, use Oracle's DEBUG routines to find problems in PL/SQL logic and will learn how to use PL/SQL's new optimizing compiler and collections. Students will learn how to use Toad when performing the hands-on exercises.

Lectures and topics are enhanced with live illustrations and hands-on exercises.

COURSE OBJECTIVES:

  1. Introduction to Explain Plans and Hints.
  2. Introduction to Cost-Based Optimizer.
  3. Understanding Indexes.
  4. Introduction to Tuning Tools: SQL Tracing & TKProf
  5. Learning SQL Coding Tips.
  6. Learning PL/SQL Coding Tips.
  7. Review of PL/SQL syntax and language structure.
  8. Review of cursor management.
  9. Review of PL/SQL Profiling.
  10. Review of Package Usage (User Defined and Oracle Defined)
  11. Exposure to Quest Software's application named Toad.
  12. Introduction to advanced PL/SQL topics including compiler options, code encryption, conditional compilation, collections, triggers, dynamic SQL and much more.

AUDIENCE:

Students who wish to learn advanced tips and techniques of SQL and PL/SQL programming who already have a working knowledge of SQL and PL/SQL.

PREREQUISITES:

Working knowledge of SQL and PL/SQL.

ATTENDEES RECEIVE:

  • Study Guide with presentations
  • PL/SQL Reference pocket guide
  • Example files with SQL and PL/SQL scripts
  • Java and PL/SQL white paper
  • Executing external procedures through PL/SQL white paper
9

COURSE OUTLINE:

Day 1: SQL Statement Tuning

  • Oracle RDBMS Architecture overview
  • Explain Plans explained
    1. Understanding
    2. Reading
    3. Interpreting
  • Understanding the Cost-Based Optimizer (CBO)
  • Understanding Parallel Explain Plans
  • Working with Hints

Day 2: SQL Troubleshooting Tips and Techniques

  •  Indexes:
    1. Review
    2. Tips and Techniques
  • Sub-Query Coding Techniques
  • Tuning Tools
    1. Statspack
    2. Events
    3. SQL Tracing
    4. TKProf
  • Profiling and tuning PL/SQL

 

 

Day 3: Advanced SQL Troubleshooting Tips and Techniques

  • Review SGA Memory Structures
    1. Buffer Cache
    2. Result Cache
  • Library Cache Trace File Analysis
    1. Using SQL TXPlan (new free Oracle SQL analysis tool)
    2. Using Toad
  • Other Useful Oracle Traces
    1. 10053 CBO Trace
    2. 10030 & 10031 Sort Traces
    3. 10104 Hash Join Traces
    4. Bulk Collect/Forall Bulk Binding
    5. Using Collections for Reference Table
  • Oracle Internals
    1. Various Space Management Issues discussed
    2. Monitoring Sorting
  • Finding Problem SQL using v$ information
  • Using Automated Workload Repository
  • Review Database Replay and External Tables
  • Review the SQL Tuning Advisor

Day 4: Advanced Oracle PL/SQL

  • PL/SQL Compiler Options
  • Code Encryption
  • Conditional Compilation
  • Definer/Invoker Rights
  • Executing and Exception handling
  • Autonomous Transactions
  • Package Options
  • Overloading
  • Database Triggers
    1. DML (Data Manipulation Language)
    2. Instead of
    3. ServerError
    4. Startup
    5. Shutdown
    6. Logon
  • Cursors
    1. User Defined Datatypes
    2. Cursor Sharing
    3. ServerError
    4. Strong vs. Weak Cursors

Day 5: Advanced Oracle PL/SQL

  • Collections
    1. Associate Arrays
    2. Nested Tables
    3. Varrays
    4. Bulk Collect/Forall Bulk Binding
    5. Using Collections for Reference Table
  • Oracle Provided Packages
    1. DBMS_ALERT
    2. DBMS_JOB
    3. DBMS_OUTPUT
    4. DBMS_SCHEDULER
    5. UTL_FILE
    6. UTL_MAIL
    7. Other Provided Packages
  • DBMS_SQL and Dynamic SQL
  • Working with Large Objects
  • Debugging PL/SQL Routines
  • PL/SQL Profile
  • PL/SQL Tuning Tips

COURSE DESCRIPTION:

This is a 3-day class (6-hours per day) that includes lecture, lab and testing for up to 12 students.

Attendees will learn tips and techniques on how to advance their expertise when programming in PL/SQL. This course covers all of the latest features of PL/SQL using databases Oracle 10g, Oracle 11g and Oracle 12c. This course utilizes a variety of tools, one of which is Quest Software's Toad.

This class could easily be named "New Features for Developers."

Lectures and topics are enhanced with live illustrations and hands-on exercises.

COURSE OBJECTIVES:

  1. Review of PL/SQL syntax and language structure.
  2. Review of cursor management.
  3. Review of PL/SQL Profiling.
  4. Review of Package Usage (User Defined and Oracle Defined.
  5. Introduction to advanced PL/SQL topics including compiler options, code encryption, conditional compilation, collections, triggers, dynamic SQL and much more.

AUDIENCE:

Students who wish to learn advanced tips and techniques of PL/SQL programming who already have a working knowledge of SQL and PL/SQL.

PREREQUISITES:

Working knowledge of SQL and PL/SQL.

ATTENDEES RECEIVE:

  • Study Guide with presentations
  • PL/SQL Reference pocket guide
  • Example files with SQL and SQL*Plus scripts
  • Information on Toad, SQL Developer, using UTL_MAIL and executing external procedures
10

COURSE OUTLINE:

Day 1: Review of PL/SQL Options and Compiling

  • Overview
  • Review
  • PL/SQL Compiler Options
  • Code Encryption
  • Conditional Compilation
  • Definer/Invoker Rights
  • Executing and Exception handling
  • Autonomous Transactions
  • Package Options
  • Overloading

Day 2: Triggers, Cursors and Collections

  • Database Triggers
    1. Instead of
    2. ServerError
    3. Startup
    4. Shutdown
    5. Logon
  • Cursors
    1. User Defined Datatypes
    2. Cursor Sharing
    3. Cursor Variables
    4. Strong vs. Weak Cursors
  • Collections
    1. Associate Arrays
    2. Nested Tables
    3. VArrays
    4. Bulk Collect/Forall Bulk Binding
    5. Using Collections for Reference Table

Day 3: Oracle Provided Packages and Miscellaneous Topics

  • ​ DBMS_ALERT
  •  DBMS_JOB
  •  DBMS_OUTPUT
  •  DBMS_SCHEDULER
  •  UTL_FILE
  •  UTL_MAIL
  •  Other Provided Packages
  •  DBMS_SQL and Dynamic SQL
  •  Working with Large Objects
  •  Debugging PL/SQL Routines
  •  PL/SQL Profile

COURSE DESCRIPTION:

This is a 3-day class (6-hours per day) that includes lecture, lab and testing for up to 12 students.

Attendees will learn coding techniques that insure a consistent response time between instances and releases of the Oracle database. This course focuses on performance tuning of actual SQL statements via coding style. The instructor finds that tuning in this fashion maintains the performance of the SQL when upgrading Oracle.

The attendees will work with a variety of SQL statements and tune them by making coding changes based on their review of the explain plans, modifying indexes and using hints and coding style to control the explain plans. They will also learn how to use tools such as index monitoring, SQL Trace and the PL/SQL profiler. They will be instructed on how Oracle selects indexes, why they are sometimes not used and how to tell which indexes are being used or not.

Lectures and topics are enhanced with live illustrations and hands-on exercises.

TOPICS COVERED:

  1. Oracle Architecture from a SQL Performance point-of-view
  2. Understanding SQL Tuning Statement Topics
  3. Reading Explain Plans/Understanding Explain Plans
  4. Controlling both the Cost-based and Rule-based Optimizers
  5. A close look at Indexes – how they work and how they are selected
  6. SQL Tuning via coding style
  7. A review of how Oracle computes row cardinality
  8. Important CBO Statistics review
  9. Oracle Trace Facility – collecting SQL and interpreting using TKProf and TOAD’s Trace Analyzer
  10. Quest SQL Optimizer
  11. Profiling PL/SQL, PL/SQL Coding Tips

COURSE OBJECTIVES:

  1. Introduction to Explain Plans and Hints.
  2. Introduction to Cost-Based Optimizer.
  3. Understanding Indexes.
  4. Introduction to Tuning Tools: SQL Tracing & TKProf
  5. Learning SQL Coding Tips.
  6. Learning PL/SQL Coding Tips.

AUDIENCE:

The focus of this course is to give SQL and PL/SQL developers the knowledge and experience so that they can tune their statements and scripts for optimal performance.

PREREQUISITES:

Working knowledge of SQL.

 ATTENDEES RECEIVE:

  •  Study Guide with presentations and relevant white papers
  • Example files of tuning and problem discovery scripts
11

COURSE OUTLINE:​

Day 1: SQL Statement Tuning

  • Oracle RDBMS Architecture overview
  • Explain Plans explained
    1. Understanding
    2. Reading
    3. Interpreting
  • Understanding the Cost-Based Optimizer (CBO)
  • Understanding Parallel Explain Plans
  • Working with Hints
  • Indexes
    1. Review
    2. Tips and Techniques

Day 2: Sub-Queries, Coding Tips, Profiling, Tuning

  • Sub-Query Coding Techniques
  • SQL Coding Tips
  • WHERE Clause and controlling Cardinality
  • CBO Statistics

Day 3: Tuning

  • Hints Tips and Techniques
  • Finding Problem SQL
  • Tuning Tools
    1. SQL Tracing
    2. TKProf
  • Profiling and tuning PL/SQL
  • PL/SQL Coding Tips
    1. Tips and Techniques

ASK ABOUT OUR NEW FEATURES CLASS!

ABOUT THE TRAINER

Our Oracle training solutions are built to empower DBAs and SQL Developers to be successful.

Training Specialist Dan Hotka is an Oracle ACE Director Alumni (a recognized outside expert/evangelist on Oracle Products) and has over three decades of experience with Oracle Products. Dan is well published with 14 Oracle books and over 200 published articles. He is frequently published in Oracle trade journals, regularly blogs and speaks at Oracle conferences and user groups around the world.