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 sales@jbinternational.co.uk
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
