Advanced Topics in Oracle

Course Number:  578-JK
Recommended Duration:  3 days

Intended Audience
Experienced users of Oracle and SQL needing to further develop knowledge and skills in the use of Oracle and its tools.  This class is intended for designers and developers of systems using Oracle.  The technical emphasis of this class lends itself to those involved in improving existing systems in addition to those designing and creating new systems.

Course Overview
Students create and tune complex SQL statements using AUTOTRACE, EXPLAIN and TKPROF, understand Oracle data structures, use data dictionary views, backup up and recover data, and use SQL*Loader.  There are eight machine exercises.

Course appropriate for Oracle 10g. Course covers Oracle9i and Oracle8i.  It is also useful for shops using Oracle8 or Oracle 7. (earlier course versions available)

Objectives
Participants will improve SQL and other Oracle-related skills so that they may more efficiently and easily satisfy user needs.  The latest releases of Oracle products will be discussed.  Hands-on exercises will allow participants to: practice complex SQL statements, learn how to use catalog tables, properly use indexes, be able to detect and improve many common performance problems, and use PL/SQL.

Prerequisites
Previous Oracle experience/education is essential to understanding the advanced topics presented in this class.  Participants should be familiar with SQL, SQL*Plus, and PL/SQL.

Topics

• Oracle concepts and data storage

• Advanced SQL usage

• EXPLAIN

• TKPROF

• AUTOTRACE

• Complex SELECT statements (subqueries, joins, and outer joins)

• Oracle object creation

• Granting/revoking authorizations

• Dynamic SQL

• Indexing, proper use of indexes

• SQL use of indexes

• Optimizing SQL

• Hints

• PL/SQL

• Stored Procedures, Functions, Packages, and Triggers

• Backup and Recovery

• SQL*Loader