oracle dba course content for professionals

7/31/2019 Oracle DBA Course Content for Professionals

http://slidepdf.com/reader/full/oracle-dba-course-content-for-professionals 1/14

1 | XPERT INFOTECH | ConfidentialCOURSE OUTLINE FOR

ORACLE DBA and D2K

Effective from: Dec 2011

D2K




ORACLE DBA and D2K

Relational Database Concepts

What is an Oracle Database

Relational Database Structures

Tables, Rows and Columns

Indexes, Primary Keys and Foreign Keys

Supported Datatypes

The Data Dictionary

Using SQL*Plus

What is SQL*Plus

Getting Started

Entering and Executing SQL Statements

Editing SQL Statements

Creating, Editing and Executing SQL Files

Using SQL Developer

What is Oracle SQL Developer

Starting SQL Developer

Configure a Connection

Navigation Tabs

SQL Worksheet

Retrieving Data with the SELECT Statement

The SELECT Statement

The SELECT and FROM Clauses

Conditions and the WHERE Clause

Other Conditional Operators

Logical Operators

The ORDER BY Clause

Column Aliases

Arithmetic Expressions

Precedence of Operators

Aggregate Functions

Overview of Built In Aggregate Functions

The GROUP BY Clause The HAVING Clause

Joining Tables

Overview of Table Joins

Inner Joins

Table Aliases

Outer Joins




ORACLE DBA and D2K

Self Joins

ANSI Standard Joins

Set Operators

Numeric, Character and Date Functions

Function Types

Using the Table dual to try out Functions

Numeric Functions

Character Functions

String Concatenation

Date Arithmetic and Date Functions

Conversion and Miscellaneous Functions

Conversion Functions

The NVL and NVL2 Functions

The DECODE Function

CASE Expressions

The COALESCE and NULLIF Functions

SQL*Plus Parameters

Command Line Substitution Parameters

The Accept Command

The Define and Undefine Commands

Using Subqueries

Overview of Subqueries

In-Line Views

Top-N Queries

Complex Subqueries

Multi Column Subqueries Correlated Subqueries

Subquery Rules

Using the ANY, ALL and SOME Operators

Managing Data

Inserting Rows

Updating Rows

Deleting Rows

Verifying Updates

Transaction Control

Commit and Rollback

Savepoints Commits and Constraints

Amending Data in SQL Developer

Managing Tables

Creating Tables

Specifying Constraints

Altering Tables, Columns and Constraints




ORACLE DBA and D2K

Dropping Tables, Columns and Constraints

Copying Tables

Managing Indexes and Views

Creating Indexes

Dropping Indexes

Listing Indexes

Creating and Using Views

Dropping Views

Listing Views

Managing Sequences and Synonyms

Create a Sequence

View Sequence Details

Create a Synonym

List Synonyms

Installing and Configuring Oracle

Creating and Managing an Oracle database

Administering an Oracle database

Configuring the Oracle Network Environment

Configuring Shared Servers

Creating and Managing Database Storage Structures

Managing Redo Log Files and Control Files Managing Schema Objects

Managing Users, Profiles, Privileges and Roles

Managing Undo Data and Temporary Segments

Monitoring and Resolving Lock Conflicts

Maintaining Database Security

Monitoring the Performance of the Database

Loading and Unloading Data

Backing up and Recovering the Database




ORACLE DBA and D2K

Plan a Backup Strategy

Plan a Disaster Recovery Strategy Alter a Database to Archivelog and NoArchivelog Modes

Online and Offline Backups

Complete and Incomplete Database Recovery

Recover a Database from Non-Critical Losses

Perform Logical Backups with Export and Import Utilities

Use RMAN to Perform Backups And Restore

Use Flashback Technology to Recover a Database

Use Flashback to Recover From User Errors

Database Corruption

Load Data into a Database

Diagnostic Sources

Use Automatic Database Management

Monitor and Manage Storage

Use Automatic Storage Management

Monitor and Manage Memory

Control Database Resources Using Resource Manager

Automate Management Tasks with the Scheduler

Secure the Oracle Listener

Use Globalization Support

PERFORMANCE AND TUNING:

Self monitoring architecture

Disk tuning - Temporary storage, undo segments

Memory tuning – Buffer cache, shared pool, redo log buffer; locks

Statistics management

Oracle Advisors

USERS, ROLES AND PROFILES

User creation and management

Profiles

System privileges

Object privileges

Roles

Security and Auditing




ORACLE DBA and D2K

BACKUP AND RECOVERY

Cause of data loss

Structures used Offline backups

Archiving redo logs

Online backups

Complete recovery principles

Incomplete recovery principles

Oracle Flashback overview

ORACLE NET SERVICES

Client/Server architecture – Connection and Naming methods (Easy, Local,

Directory, External)

Local Networking and Internet-Working

Files - TNSNAMES.ORA / LISTENER.ORA / SQLNET.ORA / CMAN.ORA /

OID (LDAP)

Advanced Features – Load Balancing and Failover

ORACLE UTILITIES

Data Pump – Export and Import

SQL*Loader

Log Miner

ADVANCED TOPICS OVERVIEW

Performance - Table compression, indexing choices

OLAP processing–

Materialized views, ETL solutions Programming options – PL/SQL v Java

Content Management – XML, LOBs, Ultra Search

Globalization – time zone, language, date and number formats




ORACLE DBA and D2K

Relational Database Concepts

What is an Oracle Database

Relational Database Structures

Tables, Rows and Columns

Indexes, Primary Keys and Foreign Keys Supported Datatypes

The Data Dictionary

Using SQL*Plus

What is SQL*Plus

Getting Started

Entering and Executing SQL Statements

Editing SQL Statements

Creating, Editing and Executing SQL Files

Using SQL Developer

What is Oracle SQL Developer

Starting SQL Developer

Configure a Connection

Navigation Tabs

SQL Worksheet

Retrieving Data with the SELECT Statement

The SELECT Statement

The SELECT and FROM Clauses

Conditions and the WHERE Clause

Other Conditional Operators

Logical Operators

The ORDER BY Clause

Column Aliases

Arithmetic Expressions

Precedence of Operators

Aggregate Functions

Overview of Built In Aggregate Functions

The GROUP BY Clause




ORACLE DBA and D2K

The HAVING Clause

Joining Tables

Overview of Table Joins

Inner Joins

Table Aliases

Outer Joins

Self Joins

ANSI Standard Joins

Set Operators

Numeric, Character and Date Functions

Function Types

Using the Table dual to try out Functions

Numeric Functions

Character Functions

String Concatenation

Date Arithmetic and Date Functions

Conversion and Miscellaneous Functions

Conversion Functions

The NVL and NVL2 Functions

The DECODE Function

CASE Expressions

The COALESCE and NULLIF Functions

SQL*Plus Parameters

Command Line Substitution Parameters

The Accept Command

The Define and Undefine CommandsUsing Subqueries

Overview of Subqueries

In-Line Views

Top-N Queries

Complex Subqueries

Multi Column Subqueries

Correlated Subqueries

Subquery Rules

Using the ANY, ALL and SOME Operators

Managing Data

Inserting Rows Updating Rows

Deleting Rows

Verifying Updates

Transaction Control

Commit and Rollback

Savepoints




ORACLE DBA and D2K

Commits and Constraints

Amending Data in SQL Developer

Managing Tables

Creating Tables

Specifying Constraints

Altering Tables, Columns and Constraints

Dropping Tables, Columns and Constraints

Copying Tables

Managing Indexes and Views

Creating Indexes

Dropping Indexes

Listing Indexes

Creating and Using Views

Dropping Views

Listing Views

Managing Sequences and Synonyms

Create a Sequence

View Sequence Details

Create a Synonym

List Synonyms

PL/SQL Fundamental

What is PL/SQL?

Basic Elements

Variables and Constants

Data Types

Initialising Variables and Assigning Values

Using SQL Statements in Code

Generating Output to SQL or SQL Developer

Program Logic

IF THEN ELSIF ELSE Statements

CASE Statements

The Basic Loop Construct

WHILE and FOR Loops

Nested and Labelled Loops

The GOTO Statement




ORACLE DBA and D2K

The CONTINUE Statement

Using Cursors

What is a Cursor?

Implicit and Explicit Cursors

Cursor Operations

Declaring, Opening and Closing Cursors

Fetching Rows

Status Checking

Using Cursors FOR UPDATE

The Cursor FOR Loop

Parameterised Cursors

Exceptions and Nested Blocks

The EXCEPTION Section

Types of Exception

Handling Named System-Raised Exceptions

Handling Un-named System-Raised Exceptions

User-Declared Exceptions and Application Errors

WHEN OTHERS THEN NULL

Nested and Labelled Blocks

Propagation of Exceptions

Scope of Variables and Cursors

Scope of Goto Statements

PL/SQL Records and Index-By Tables

Declaring Record Types

Handling PL/SQL Records

Nested Records

Declaring PL/SQL Index-By Tables or Associative Arrays

PL/SQL Table Built-in Functions

Manipulating PL/SQL Tables or Associative Arrays

Introduction

Course objectives

The Oracle complete solution

Course agenda

Tables and data used for this course




ORACLE DBA and D2K

PL/SQL Programming Concepts Review

Identify PL/SQL block structure

Create procedures

Create functions

Create packages

Use cursors

Handle exceptions

Understand dependencies

Identify the Oracle supplied packages

Design Considerations

List the different guidelines for cursor design

Describe cursor variables

Pass cursor variables as program parameters

Compare cursor variables to static cursors

Describe the predefined data types

Create subtypes based on existing types for an application

Collections

Describe and use nested tables

Describe and use varrays

Describe and use associative arrays

Describe and use string indexed collections

Describe and use nested collections

Write PL/SQL programs that use collections

Describe the common collection exceptions and how to code for them Compare associative arrays to collections

Advanced Interface Method

Execute external C routines from PL/SQL

Understand the benefits of external routines

Publish the external C routine in the PL/SQL code

Execute a PL/SQL routine that calls the external C routine

Execute Java routines from PL/SQL

Publish the Java class method by creating the PL/SQL subprogram unit

specification that references the Java class method Execute the PL/SQL subprogram that invokes the Java class method

PL/SQL Server Pages

Define embedding PL/SQL code in Web pages(PL/SQL Server Pages)

Describe the format of a PL/SQL Server Page

Write the code and content for the PL/SQL Server Page




ORACLE DBA and D2K

Load the PL/SQL Server Page into the database as a stored procedure

Run a PL/SQL Server Page via a URL

Debug PL/SQL Server Page problems

Fine Grained Access Control

Understand how fine-grained access control works overall

Describe the features of fine-grained access control

Describe an application context

Set up a logon trigger

View the results

Query the dictionary views holding information on fine-grained access

Notes:

Recommend Practical hours for student except class hours is 70 Hrs.

We also provide weekend classes for all courses.

After completion of 75% of course, student will go through Live Project

Training, Interview Preparation and Recruitment process in Software

Industry.

Student will go through the training of DBMS and RDBMS Concepts

before starting of SQL. This is complimentary.




ORACLE DBA and D2K

Our Technology Specialization and Certification Courses:




ORACLE DBA and D2K

K K K E E E Y Y Y F F F E E E A A A T T T U U U R R R E E E S S S O O O F F F X X X P P P E E E R R R T T T I I I N N N F F F O O O T T T E E E C C C H H H : : :

Training by Certified and Experienced Trainers.

Industrial and Corporate Tie-ups for Live projects for student.

Well equipped Computer Lab, Internet and Book Bank facility.

Live project based Summer Trainings, Summer Internships and

summer programs.

100% placement assistance on every course.

Interview Preparation and Technical Events.

On Campus Interviews in every month for the placement of students.

WI-FI enabled classrooms. 100% Placement

Assistance

On Campus

Interviews

58-61 (Basement), Vashist Park, Pankha Road,

New Delhi-46

Ph.: 011-28526572, 65833100, 9990345891

www.xpert-infotech.comEmail: [email protected]

oracle dba course content for professionals

Documents