ORACLE Essentials training course

Course Outline

The course is suitable for any personnel involved in the maintenance of Oracle applications and databases or who use reporting tools that use ANSI compliant SQL

Course Content

This course has been developed for real-world, commercial scenarios by our expert instructors. See below for detailed syllabus. If you have a technical question, please email [email protected]

What you will learn

1. Understand Oracle terminology.
2. Insert, update, delete data from Oracle tables.
3. Produce query output from oracle data.
4. Understand and use the Data Dictionary.

Oracle Essentials Training Course Outline

Part 1 - SQLPLUS Basics

Introduction to SQL.

What is SQL and What is it Used For?

The Data Dictionary.

Using the Data Dictionary
Identifying table Structures

The SELECT Statement

Mandatory Clauses 
- SELECT and FROM Clauses
The WHERE Clause 
- Filtering data – including and excluding records 
The ORDER BY Clause
- Sorting operations

Creating Simple Scripts and Saving Output.

Saving your Queries
Spooling Output

Substitution Variables.

Lexical and Bind variables

Using Functions to convert, format and summarize data.

Numeric, Character, and Miscellaneous Functions
Group Functions SUM,AVG,COUNT,STDDEV 
GROUP BY and HAVING Clauses

Basic Sub-queries.

Queries within Queries 
Basic Sub Queries correlated and uncorrelated
EXISTS and NOT EXISTS Clauses

Joining Tables.

Cartesian Products
Standard Table Joins 
Self Referencing Table Joins 
Inner and Outer Joins

Data Manipulation

Inserting Data Into Tables
Updating Data 
Deleting Data

Table Locking.

Commit and Rollback 
Table and Row Level Locking

Part 2 PL/SQL basics

Introduction to PL/SQL

PLSQL Blocks 
Introduction to Procedures, Functions and Packages 
Executing & Using Procedures, Functions & Packages 
Private & Public Execution

Declarations and variable assignment.

Data Types 
Bind variables 
Assigning Default Values and Constants 
Table Arrays 
Defining Records and Tables of Records

Cursors

Defining Cursors 
Manipulating Multiple Records 
Parameterising Cursors

Data Manipulation

For Update and Where Current of Clauses Embedded Insert, Update, Delete commands.

Loops

Basic, FOR, While and Cursor For loops 
Nested Loops and Variable Scope

Exception handling.

Handling Runtime Errors 
System Handled Exceptions 
User Defined Exceptions 
How to continue processing after an Exception

Part 3 Database Administration essentials

The basics of an Oracle Database
What Is an Oracle Instance and Oracle Database 
The components Of an Oracle Installation
Enterprise Manager Overview

The Oracle instance

The System Global Area

The Oracle Database

The Oracle Disk Areas
Control Files 
REDO Logs 
Data Files 
Parameter & Password Files

Oracle Database Administration Tasks

Starting and shutting down the database.
Using Server Manager and SQLPLUS Line mode 
Nomount, Mount, Open Database Modes 
Sizing and creating Tablespaces.
Types of Tablespace 
The System Tablespace 
Creating a Tablespaces 
Sizing and Positioning of Tablespace Datafiles 
Locally Managed Tablespaces
Rollback/Undo segments. 
Rollback Segments and Their Use 
Required number and Size of Rollback Segments 
Creating Rollback Segments 
Private and Public Rollback Segments
Automatic Undo Management
User Access
Users and User Schemas 
Managing User Sessions
Creating Users
User Profiles 
Defining User Storage Areas 
Restricting User Resources
Tables & Indexes
Creating Tables 
Creating Indexes