ADDITIONAL TRAININGS: ORACLE 9i - Database Administration

ORACLE: Certificate of Attendance
(Oracle9i Database Administration Fundamentals I)

COURSE TOPICS:

Oracle Architectural Components
* Defining an Oracle Database, the Oracle server, and the Oracle Instance
* Establishing a connection and creating a session
* Understanding the physical structure
* Understanding the memory structure
* Understanding the process structure
* Understanding the logical structure
* Outlining the stages of an SQL statement

Getting Started with the Oracle Server
* Identifying database administrative tools

Identifying the features of the Universal Installer

Defining the Optimal Flexible Architecture

Creating a database during installation

Creating a new database

Identifying database administrative users
* Understanding operating system and password file authentication
* SQL*Plus
* Enterprise Manager

Managing an Oracle Instance
* Creating, managing, and using initialization files: PFILE and SPFILE
* Identifying the various states of starting an instance
* Identifying the various options available to shutdown an instance
* Monitoring Alert and Trace files

Creating a Database
* Using the Database Configuration Assistant to create a database
* Creating a database manually

Using the Data Dictionary
* Identifying the uses and contents of the data dictionary
* Using the data dictionary to retrieve information about the database

Maintaining the Control File
* Explaining the uses of the control file
* Listing the contents of the control file
* Multiplexing the control file
* Managing control file with Oracle Managed Files (OMF)
* Obtaining control file information

Maintaining Redo Log Files
* Explaining the purpose of and how online redo log files work
* Control log switches and checkpoints
* Maintaining online redo log files
* Managing online redo log files using OMF
* Multiplexing and maintaining online redo log files
* Archiving online redo log files
* Obtaining online and archived redo log file information

Managing Tablespaces and Datafiles

Describing storage hierarchy

Establishing the difference between SYSTEM and Non-SYSTEM tablespaces
* Creating and managing tablespaces
* Creating a tablespace when using OMF
* Obtaining tablespace and datafile information

Storage Structures and Relationships
* Describing the logical structure of the database (segments, extents, blocks)
* Listing the segment types and their uses
* Understanding the use of extents and their allocation and deallocation
* Understanding the structure of a database block
* Maintaining storage structures with automatic segment - space management
* Maintaining storage structures manually
* Obtaining storage structure information

Managing Undo Data
* Understanding the difference between automatic undo management and manual undo management

Creating and maintaining UNDO tablespaces
* Obtaining undo information

Managing Tables
* Creating and maintaining tables using appropriate storage settings
* Obtaining table information

Managing Indexes
* Listing the different types of indexes and their uses
* Creating b-tree and bitmap indexes
* Maintaining Indexes

Identifying unused indexes
* Obtaining index Information


COURSE OBJECTIVES:
* Manage tablespaces, segments, extents, and blocks
* Manage users, privileges, and resources
* Create an operational database
* Start up and shut down an Oracle instance and database
* Manage Oracle database files
* Use Globalization Support features

ADDITIONAL TRAININGS: ORACLE 9i - Introduction to SQL

ORACLE: Certificate of Attendance

COURSE TOPICS:

Introduction

* Describing the Life Cycle Development Phases
* Defining a Relational Database
* Discussing the Theoretical, Conceptual, and Physical Aspects of a Relational Database
* Describing How a Relational Database Management System (RDBMS) Is Used to Manage a Relational Database
* Describing the Oracle Implementation of Both the RDBMS and the Object Relational Database Management System (ORDBMS)
* Describing How SQL Is Used in the Oracle Product Set

Writing a Basic SQL Statement
* Describing the SQL Select Capabilities
* Executing a Basic Select Statement with the Mandatory Clauses
* Differentiating Between SQL and iSQL*Plus Commands

Restricting and Sorting Data
* Limiting the Rows Retrieved by a Query
* Sorting the Rows Retrieved by a Query

Single Row Functions
* Describing Various Types of Functions Available in SQL
* Using a Variety of Character, Number, and Date Functions in SELECT Statements
* Explaining What the Conversion Functions Are and How They Are Used
* Using Control Statements

Displaying Data from Multiple Tables
* Writing SELECT Statements to Access Data from More Than One Table
* Describing the Cartesian Product
* Describing and Using the Different Types of Joins
* Writing Joins Using the Tips Provided

Aggregating Data by Using Group Functions
* Identifying the Different Group Functions Available
* Explaining the Use of Group Functions
* Grouping Data by Using the GROUP BY Clause

Writing Subqueries
* Describing the Types of Problems That Subqueries Can Solve
* Describing Subqueries
* Listing the Types of Subqueries
* Writing Single-Row and Multi-Row Subqueries
* Describing and Explaining the Behavior of Subqueries When NULL Values Are Retrieved

Producing Readable Output with iSQL*Plus
* Producing Queries That Require an Input Variable
* Customizing the iSQL*Plus Environment
* Producing More Readable Output
* Creating and Executing Script Files

Manipulating Data
* Describing Each Data Manipulation Language (DML) Command
* Inserting Rows into a Table
* Updating Rows in a Table
* Deleting Rows from a Table
* Merging Rows into a Table
* Controlling Transactions
* Describing Transaction Processing
* Describing Read Consistency and Implicit and Explicit Locking

Creating and Managing Tables
* Describing the Main Database Objects
* Creating Tables
* Describing the Oracle Data Types
* Altering Table Definitions
* Dropping, Renaming, and Truncating Tables

Including Constraints
* Describing Constraints
* Creating and Maintaining Constraints

Creating Views
* Describing Views and Their Uses
* Creating a View
* Retrieving Data by Means of a View
* Inserting, Updating, and Deleting Data Through Views
* Dropping Views
* Altering the Definition of a View
* Inline Views
*Top 'N' Analysis

Other Database Objects
* Creating, Maintaining, and Using Sequences
* Creating and Maintaining Indexes
* Creating Private and Public Synonyms

Controlling User Access
* Understanding the Concepts of Users, Roles, and Privileges
* Granting and Revoking Object Privileges
* Creating Roles and Granting Privileges to Roles
* Creating Synonyms for Ease of Table Access

SQL Workshop
* Applying Techniques Learned in This Course
* Preparing for Future Oracle Courses

Using Set Operators
* Describing the Set Operators
* Obeying the Set Operators Rules and Guidelines
* Using a Set Operator to Combine Multiple Queries into a Single Subquery
* Controlling the Order of Rows Returned

Oracle 9i Datetime Functions
* Using DATETIME Functions
* Using the NVL2 Function to Handle NULL Values

Enhancements to the GROUP BY Clause
* Using ROLLUP as an Extension to the GROUP BY Clause to Produce Subtotal Values
* Using CUBE as an Extension to the GROUP BY Clause to Produce Cross-Tabulation Values
* Using the GROUPING Function to Identify the Row Values Created by ROLLUP or CUBE Operators
* Using GROUPING SETS to Produce a Single Result Set That Is Equivalent to a UNION ALL Approach
* Using the WITH Clause

Advanced Subqueries
* Multiple-Column Subqueries
* Writing a Subquery in the FROM Clause
* Writing and describing Correlated Subquery
* Using EXISTS and NOT EXISTS Operators
* Updating and Deleting Rows Using Correlated Subqueries
* Using Scalar Subqueries in SQL

Hierarchical Retrieval
* Discussing the Benefits of the Hierarchical Query
* Ordering the Rows Retrieved by a Query in a Hierarchical Manner
* Formatting Hierarchical Data so That It Is Easy to Read
* Excluding Branches from the Tree Structure

Oracle9i Extensions to DML and DDL Statements
* Discussing Multitable Inserts
* Creating and Using External Tables
* Naming the Index and Using the CREATE INDEX Command at the Time of Creating Primary Key Constraint

COURSE OBJECTIVES:
* Describe relational and object relational database concepts
* Retrieve, insert, update, and delete data
* Use the Single row and Group functions
* Retrieve data using additional advanced techniques like using ROLLUP, CUBE, set operators, correlated subqueries and hierarchical queries
* Solve problems by using multicolumn subqueries, subqueries in the from clause of a SELECT statement, correlated subqueries, and scalar subqueries
* Use the iSQL*Plus environment
* Write scripts to generate script files
* Generate reports using iSQL*Plus
* Create and maintain database objects
* Control transactions
* Control data/user access
* Use the Oracle9i Single row functions
* Use GROUPING SETS and the WITH clause
* Use the Oracle9i extensions to DML and DDL statements
* Apply techniques in real life simulation