request@systematix.co.uk
supermarketBasket
Call Us On:
0118 324 3000

Oracle SQL Part 2: Creating and Maintaining objects in SQL and SQL*Plus

2 Day Instructor-led
course icon

Course Information

Overview

To enable the creation maintenance of objects in an Oracle database it is essential to have a through understanding of the SQL Language. The course takes you through oracle terminology, object types available tables, indexes, views, synonyms, sequences, etc and how to create and maintain them. Opportunities are provided to use ORACLE’s isqlplus as well as sqlplus client and sqlplus from the command prompt. The course covers all the essentials for developers and administrators to create and maintain data objects and continues on to using various techniques in creation and population. The course is organised into chapters covering the course objectives in a lecture format interspersed by sets of exercises designed to illustrate the points covered and are completed by the delegate. The course provides essential knowledge of SQL as a stand alone course but is also intended as part of our provided courses list SQL1 and SQL3 for versions 9i, 10g and 11g of the ORACLE database.

Target Audience

The course is suitable for any personnel involved in the maintenance or creation of Oracle applications and databases who have a basic knowledge of SQL.and who wish to construct their own tables and data structures. The course is a pre-requisite for the Database Administration and PL/SQL range of courses.

Course Requirements

Some degree of computer literacy is required and at least a basic knowledge of SQL, or attendance on our SQL part1 courses.

Course Objectives

At the end of the course the delegate should be able to :

  • Understand Oracle terminology.
  • Understand Oracle data objects and data types.
  • Understand and use relational integrity to produce structured data sets.
  • Understand and create index structures.
  • Control data access through the use of different types of views.
  • Control data access through Oracle security structures.

Course Outline

Download PDF Outline
1. Introduction to Oracle data objects
2. Data Dictionary
  • Using the Data Dictionary
  • Identifying data Structures and object usage
3. Oracle Tables
  • Column data types and their uses.
  • Storage and storage parameters.
  • Creating a table.
  • Copying existing definitions
  • Types – nested tables, varrays, methods.
  • Table Partitioning.
  • External Tables - Input only (ORACLE_LOADER)
  • External Tables - Input/Output (DATAPUMP versions 10g and 11g only)
4. Relational Integrity
  • Relational theory
  • Primary and foreign key definition and usage.
  • Unique, Check and Not Null constraints.
5. Indexes
  • Types of index and their uses
  • Storage and storage Parameters.
  • Creating B*tree and Bitmap indexes
  • Function based indexes
  • Local vs Global partitioning.
  • Maintaining indexes and de-fragmentation.
6. Views and Materialized Views
  • Understanding views.
  • Simple and Complex views
  • Creating a standard view - With check option
  • Creating a standard view - Read only
  • Creating a standard view - Restricting column and row data access
  • Creating a Materialized View - Refresh options
  • Creating a Materialized View - Storage considerations
  • Creating a Materialized View - Creating materialized views
7. Other Oracle Objects
  • Creating and using Sequences.
  • Creating synonyms.
8. Schema Security
  • Overview of schema security
  • Granting and revoking data access
  • Roles and defining security hierarchies
  • User Profiles and their impact on security
  • User passwords and authentication methods
9. Data Utilities
  • Overview of ORACLE_LOADER - Loading data from external sources.
  • Overview of EXP (and DATAPUMP *version 10g and 11g only) - Data transfer between databases and backup/restore uses.

Dates & Availability

We currently don’t have any dates scheduled for this training course but offer it as a corporate, private course for your own team.

Please use the enquire button below.

Private Courses

We can arrange your own private Oracle course.

Tailored

Have us build a custom private course tailored to your needs.

Cost Effective

If you are looking to training a group of people private courses can be very cost effective.

Post Course Support

Unlimited post course email support on the course topics.

Delivery Options:
On-Site At Your Offices
Virtual Online Virtual Online
Training Centres Training Centres

What Our Clients Think

Really good course. lots of hands on practice and clear explanations.

fiona dawe - Covetrus Global Software Services

This was a really good training, nicely paced, very useful and well presented. Thank you, Stuart!

Joris Latinne - Oracle

Course was well adjusted to consider the business perspective - considering commonly needed calculations, time based data analysis etc.

Suvi Salminen - Crane CPE

Cannot fault the content & delivery of the course at all. A brilliant service from start to finish. Thank you Systematix.

Claire Mitchell - Denbighshire County Council

The trainer was an amazing lecturer and the vba course was very usefull and well structured. Thank you.

Veronika Panton - Haven Power

Really enjoyable and relevant, Stuart was brilliant!

Vicky Fox - Charity Link

Related Courses

Oracle SQL Part 1: Introduction

Format:
Virtual or Classroom
Length:
3 Days
Learn the essentials of writing Structured Query Language (SQL) commands to access, create and modify data within an Oracle database.

Oracle SQL Part 2: Creating and Maintaining objects in SQL and SQL*Plus

Format:
Virtual or Classroom
Length:
2 Days
 Learn to create and maintain data objects together with various techniques in data creation and population.