Oracle DBA Training Institute

ORACLE DATABASE ADMINISTRATOR (DBA

Oracle Database Administrator (DBA) is a multi-model Relational Database management system Developed by Oracle Corporation. Orade DBA is responsible for the organization, integrity, & management of a company's data with great knowledge of Database Management Systems (DBMS) to define, create, maintain, backup, and secureanorganization's databases.

Orade database administrator has to perform Responsibilities indudes: 

Installing new versions of the Oracle database and its tools, Installing tools required to access the Oracle database, Create new database users as required, Upgrading the database, Shutting down and Starting the database, Backing up and Recovering Data, Exporting and Importing Data, Managing Users, Managing security of the database, Monitoring the database, Creating database objects like databases, tables and views,, Plans and implements backup and recovery of the Oracle database, Provides technical support to application development teams, Enforces and maintains database constraints to ensure integrity of the database, Administers all database objects, including tables, clusters, indexes, views, sequences, packages and procedures, Troubleshooting problems regarding the databases-applications and developmenttools.

ORACLE Database Administrator (DBA) include the following tasks:

• Installing and upgrading the Oracle server and applicationtools.
• Allocating system storage and planning future storage requirements for the database system.
• Creating primary database storage structures (table-spaces) after application
• developers have designed an application. Creating primary objects (tables, views, indexes) once application developers have designed an application.
• Modifying the database structure, as necessary, from information given by application developers.
• Enrolling users and maintaining system security.
• Controlling and monitoring user access to the database. Monitoring and optimizing the performance of the database.
• Planning forbackup and recovery of database information.
• Maintaining archived data on tape.
• Backing up and restoring the database.
• Provide Technical support for Other Application Development Platforms

            ORACLE DBA NEW COURSE CURRICULUM

Introduction to DBMS

• Approach to Data Management
• Introduction to prerequisites File Settings and File-system
• Review of Database Management Terminology
• Database Models
• Hierarchal Model
• Network Model
• Relational Model

Introduction to RDBMS

• Features & Advantages of RDBMS and DBMS
• The 12 rules (E.F Codd's Rules -RDBMS)
• Need for Database Design
• Support of Normalization Process for Data Management
• Client-Server Technology
• Oracle Versions
• About SQL&SQL*PLUS

Introduction To ORACLE

• Difference between Oracle Old and latest versions
• Installation of Oracle Software
• About SQL*Plus and use of developer tool
• Data types in Oracle
• Operators in Oracle
• Understanding Schema design and objects

ORACLE Sub Language Commands

• Data Definition Language (DDL)
• Data Retrieval Language (DRL)
• Data Manipulation Language (DML)
• Transaction Control Language (TCL)
• Database Security and Privileges (DCL

Working With DDL Commands in ORACLE

• Table creation using CREATE statement
• Creating table from another table
• Dropping a table using DROP command
• Altering the column of a table
• Renaming an entire table
• Using truncate command
• Difference between Delete and Truncate command

Working With DML Commands in ORACLE

• How to copy data from one table to another table? How to copy the structure alone from a table?
• Different types of inserting row to an existing table
• Updating any value of with in a record using UPDATE command
• Deleting a particular record from a table
• Using merge & insert all command

Working with DCL, TCL Commands in ORACLE

• Grant, Revoke
• Commit, Rollback, Savepoint
• Page No.
• SQL Editor Commands
• SQL Environment settings

Managing Users and Securing Database

• Establishing Security Policy on a Database
• Privileges and Roles
• Managing oracle database users
• How to create a new database user
• Auditing Database activities
• Predefined oracle database accounts

Introduction to SQL Database Object in ORACLE

• Oracle Pre Defined Datatypes
• DQL-SELECT Statements using WHERE Clause
• Comparison and Conditional Operators Arithmetic and Logical Operators
• Special Operators - IN (NOT IN), BETWEEN (NOT BETWEEN), LIKE (NOT LIKE), IS NULL (IS NOT NULL)
• Working with DML, DRL Commands

Working with Integrity Constraints in ORACLE

• Importance of Data Integrity
• How to add constraints to an existing table?
• NOT NULL constraint
• UNIQUE constraint + PRIMARY KEY constraint
• FOREIGN KEY constraint
• CHECK constraint
• Applying DEFAULT option to columns
• How to enable and disable constraints?
• How to get information about constraints?

Data Retrieval Techniques in ORACLE

• How to use select statement in different ways to retrieve records?
• Working with Column and table alias
• Data filtering and sorting with in single table
• Clauses and its types in oracle
• Where clause & Having clause & From clause
• Group by clause, Order by clause
• Constraint clause

Built-in Functions in ORACLE

• How to use single row functions using dummy table?
• Character Functions and String functions
• Date functions
• Mathematical functions
• Conversion functions
• Aggregate Functions
• Special functions
• Analytical functions

Working with DCL, TCL Commands in ORACLE

• Grant, Revoke
• Commit, Rollback, Savepoint
• Page No.
• SQL Editor Commands
• SQL Environment settings

Managing Users and Securing Database

• Establishing Security Policy on a Database
• Privileges and Roles
• Managing oracle database users
• How to create a new database user
• Auditing Database activities
• Predefined oracle database accounts

Introduction to SQL Database Object in ORACLE

• Oracle Pre Defined Datatypes
• DQL-SELECT Statements using WHERE Clause
• Comparison and Conditional Operators Arithmetic and Logical Operators
• Special Operators - IN (NOT IN), BETWEEN (NOT BETWEEN), LIKE (NOT LIKE), IS NULL (IS NOT NULL)
• Working with DML, DRL Commands

Working with Integrity Constraints in ORACLE

• Importance of Data Integrity
• How to add constraints to an existing table?
• NOT NULL constraint
• UNIQUE constraint + PRIMARY KEY constraint
• FOREIGN KEY constraint
• CHECK constraint
• Applying DEFAULT option to columns
• How to enable and disable constraints?
• How to get information about constraints?

Data Retrieval Techniques in ORACLE

• How to use select statement in different ways to retrieve records?
• Working with Column and table alias
• Data filtering and sorting with in single table
• Clauses and its types in oracle
• Where clause & Having clause & From clause
• Group by clause, Order by clause
• Constraint clause

Built-in Functions in ORACLE

• How to use single row functions using dummy table?
• Character Functions and String functions
• Date functions
• Mathematical functions
• Conversion functions
• Aggregate Functions
• Special functions
• Analytical functions

Set Operators in ORACLE

• Working with set operator types
• Page No.
• UNION and UNION ALL
• INTERSECT
• MINUS

Pseudo Columns in Oracle

• Understanding Pseudo Columns in Oracle
• Types of Pseudo Columns in Oracle
• CURRVAL and NEXTVAL
• LEVEL
• ROWID
• ROWNUM

Functions in PL/SQL in ORACLE

• Difference between Procedures and Functions
• How to create functions?
• How to use inline functions?
• Nested Functions
• Using stored function in SQL statements

Querying Multiple Tables (Joins) in ORACLE

• Understanding joins and its uses
• Types of joins
• Simple Join / Equi-join/Inner Join / Outer join
• Left & Right outer join and Full outer join
• Cross join

Sub Queries in ORACLE

• Importance of sub queries
• Using different types of sub queries
• Single row sub queries and Multi row sub queries
• Nested queries
• Multi column sub queries
• IN, ANY/SOME, ALL Operators in Sub Queries
• Handling Data Retrieval with EXISTS and NOT EXISTS Operators

VIEWS in Oracle

• Types of VIEWS
• Relational Views and Object Views
• Practical approach of SIMPLE VIEWS and COMPLEX VIEWS
• Column definitions in VIEWS
• Using VIEWS for DML Operations
• Putting CHECK Constraint upon VIEWS
• Creation of READ ONLY VIEWS
• Understanding the IN LINE VIEWS
• About Materialized Views
• View Triggers

Triggers in PL/SQL in ORACLE

• How to create triggers?
• Benefits of trigger
• Using DML trigger & DDL trigger
• How to audit database using triggers?
• What are database level trigger?

Creation Of Stored Procedures in ORACLE

• Creating Procedures in PL/SQL
• IN parameter / OUT parameter/INOUT parameter
• Procedure with Cursors
• Dropping a Procedure

Creating and Using Cursors in ORACLE

• How to create cursors in PL/SQL
• How to create explicit cursor?
• What are cursor parameters?
• How to use for update clause?
• How to use implicit cursors?

Introduction to PL/SQL (Procedural Language Extension to SQL)

• Introduction& advantages of PL/SQL
• Datatypes, Variable and Constants in PL/SQL
• Conditional and Unconditional Statements
• Simple if, if... else, nested if..else, if..else Ladder Selection Case, Simple Case, GOTO Label and EXIT
• Simple LOOP, WHILE LOOP, FOR LOOP and NESTED LOOPS
• SQL within PL/SQL.

Implementing OOPS Technology in ORACLE

• What is Object Technology?
• Creation of classes and objects
• Inserting rows in a table using Objects
• Retrieving data from Object-based Tables
• Calling a Method

Design Of Schema Objects in ORACLE

• Creating and working with Views
• Working with Synonyms
• Creating Index and clusters
• Working with in materialized view
• Understanding sequences and its types

File Input/Output in ORACLE

• PL/SQL file I/O (Input/Output)
• SQL* Loader Architecture
• Log file
• .txt to base table
• .csv to base table

Creating & Using Packages in ORACLE

• What is a Package?
• Page No.:
• Reasons to use packages
• What is package specification?
• What is package body?
• How to initialize instantiated package?
• Creating PACKAGE Specification and PACKAGE Body

Collections In PL/SQL in ORACLE

• What is collection?
• How to use arrays?
• Using nested tables
• How to use index by value?
• Listing types of collection methods

Exception Handling in ORACLE

• What is an Exception and Types of exceptions?
• Handling system defined exceptions Handling user defined exceptions?
• RAISE_APPLICATION_ERROR
• SQL Error Code Values

Advanced Features in ORACLE LATEST VERSIONS

• Introduction to Oracle Database Architecture & its Type
• Merge statement
• Temporary Tables/Global Tables
• New Function EXTRACT()
• About Flash Back Queries
• New data types, Flash back Command
• Regular expressions, DML Error Logging
• Types of Table spaces

Security and Locks in ORACLE LATESET VERSIONS.

• Row level Locks
• Table Level Locks
• Shared Lock
• Exclusive Lock
• Dead Lock

Database Transaction and Advance Security in ORACLE

• Working with data query language using TCL Working with data control language commands
• Use of commit and rollback
• Use of save-point and set transaction
• How to give system privileges to an user?
• How to invoke and revoke object privileges?
• How to create users and roles?

PROJECTS