MyPage is a personalized page based on your interests.The page is customized to help you to find content that matters you the most.


I'm not curious

Oracle PL-SQL, DBA Training

Course Summary

Our Oracle PL/SQL certification master training program lets you gain proficiency in Oracle database. We provide the best online training classes to help you learn SQL commands, DBA commands, schema management, database structure, integrity, security. Work on real world industry projects.


  • +

    Course Syllabus

    Oracle PL SQL Course Content

    Introduction to Oracle SQL
    What is RDBMS, Oracle versions, Architecture of Oracle Database Server, Installation of Oracle 12c

    Hands-on Exercise – Install Oracle 12c

    Using DDL Statements to Create and Manage Tables
    Categorize the main database objects, Review the table structure, List the data types that are available for columns, Create a simple table, Explain how constraints are created at the time of table creation, Describe how schema objects work

    Hands-on Exercise – Create a database table Person with two column (Name, Age) with constraint on age not greater than 100, Insert records using insert query

    Retrieving Data Using the SQL SELECT Statement

    List the capabilities of SQL SELECT statements, Execute a basic SELECT statement

    Hands-on Exercise – Use a basic select statement to retrieve all the records in the Person table

    Restricting and Sorting Data
    Limit the rows that are retrieved by a query, Sort the rows that are retrieved by a query, Ampersand substitution to restrict and sort output at runtime

    Hands-on Exercise – Write a select query to retrieve records where age is more than 60 yrs, Write a select query to sort the records by name, Write a select query to sort the records by age in descending order, Use ampersand substitution to restrict and sort output at runtime

    General functions
    The general functions in SQL, working with any data type and handling Null values, using COALESCE() and Null function, Constructing and executing SQL query that applies the NUL, NUL1, NUL2 and COALESCE()

    Hands-on Exercise – Use NULL function to deal with null values in data

    Using Single-Row Functions to Customize Output
    Describe various types of functions (character, number, date, string etc.) available in SQL

    Hands-on Exercise – Create a table with columns of type char, number and date, Use character, number, and date functions in SELECT statements

    Large Object Functions
    Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB, Aggregate or Group functions – COUNT, COUNT(*),MIN,MAX,SUM,AVG,etc…,Group BY Clause, HAVING Clause

    Hands-on Exercise – Count records based on a condition, Use Count(*) to know the count of all records, Find Max, Min, Sum, Avg

    OLAP Functions
    The various OLAP functions, cube, model clause, roll up and grouping functions

    Hands-on Exercise – Working with OLAP commands – Cube, Roll Up, etc.

    Using Conversion Functions and Conditional Expressions
    Describe various types of conversion functions that are available in SQL, Conditional expressions in a SELECT statement

    Hands-on Exercise – Group data by using the GROUP BY clause, Include or exclude grouped rows by using the HAVING clause

    Displaying Data from Multiple Tables
    Joins, Inner Join, Outer Join, Left Join, Right Join, Equijoins and Non-equijoins

    Hands-on Exercise – Write SELECT statements to access data from more than one table using equijoins and non equijoins, Join a table to itself by using a self-join, View data that generally does not meet a join condition by using outer joins, Generate a Cartesian product of all rows from two or more tables

    Using Subqueries to Solve Queries
    Define subqueries, Describe the types of problems that the subqueries can solve, List the types of subqueries

    Hands-on Exercise – Write single-row and multiple-row subqueries

    Using the Set Operators
    Describe set operators, UNION [ALL], INTERSECT, MINUS Operators

    Hands-on Exercise – Use a set operator to combine multiple queries into a single query Control the order of rows returned

    Manipulating Data using SQL
    Describe data manipulation language (DML) statement, Insert, Update, Delete Statements, Control transactions

    Hands-on Exercise – Insert rows into a table, Update rows in a table, Delete rows from a table

    Database Transactions
    What is a database transaction, Properties of a transaction (Atomic, Consistent, Isolated, Durable – ACID), Avoiding error/fault in manipulating database records using transaction

    Hands-on Exercise – Begin a transaction, Execute queries to update or insert or delete records, If no error, commit the transaction Else roll back the transaction and end it

    Creating Other Schema Objects
    Views – simple and complex, Sequences, Index, Synonym

    Hands-on Exercise – Create simple and complex views, Retrieve data from views, Create, maintain, and use sequences, Create and maintain indexes, Create private and public synonyms

    Writing Cursor and Conditional Statement
    SQL Cursor, SQL Cursor Attributes., Controlling PL/SQL flow of executions, IF Statement, Simple IF Statement, IF-THAN-ELSE Statement Execution Flow, IF-THAN-ELSE Statement, IF-THAN-ELSIF Statement, Logic Tables, Boolean Conditions., Iterative controlling loop statement, Nested Loops and Labels.

    Hands-on Exercise – Use Boolean condition on a select query, Use logic table, Create nested loops and labels

    Introduction to Explicit Cursor
    Writing Explicit cursors, About cursors, Explicit cursor functions, Controlling explicit cursor, Opening the cursor, Fetching Data cursor, Cursor and records, Cursor for loop using sub queries

    Hands-on Exercise – Write an explicit cursor, Use cursor function, Fetch data cursor

    Advance concepts of explicit Cursor
    Advance Explicit cursor, Cursor with parameters, For update Clause, Where current of Clause, Cursor with sub queries

    Hands-on Exercise – Use cursor with subquery, Use Where Current clause to retrieve data

    Exception Handling
    Handling Exception, Handling Exception with PL/SQLPredefined Exceptions, User Defined Exceptions, Non-Predefined Error, Function for trapping Exception, Trapping user-defined Exception, Raise Application Error Procedure

    Hands-on Exercise – Use Predefined exception, Write user defined exception, Generate and handle exception, Use a function for trapping an exception

    Writing Subprogram, Procedure and passing parameters
    Overview of subprograms, PL/SQL Subprograms, What is Procedure, Syntax for creating Procedure, Creating Procedure with parameter, Example of Passing parameters, Referencing a public variable from a standalone procedure, Declaring Subprogram

    Hands-on Exercise – Create a parameterized procedure, Pass parameters in a procedure call, Access a public variable from a standalone procedure

    Creating PL/SQL Package
    PL/SQL records, Using Pl/SQL Table method and example, Creating PL/SQL Table, Packages – Objective, overview, component, developing, removing, advantages, Creating the package specification/example, Declaring Public construct, Public and private construct, Invoking package construct, Guide lines for deploying packages

    Hands-on Exercise – Create a package, Deploy the created package

    Advance Package Concepts and functions
    Overloading, Using forward declaration, One time only procedure, Package functions, User define package function, Persistent state of package function, Persistent state of package variable, Controlling the persistent state of package cursor, Purity end, Using supplied package, Using native dynamic SQL, Execution flow, Using DBMS-SQL package, Using DBMS-DDL package, Submitting jobs, Interacting with operating system links

    Hands-on Exercise – Use supplied package, Use native dynamic SQL, Use DBMS-SQL package, Use DBMS-DDL package, Submit a job

    Introduction and writing Triggers
    Triggers – Definition, objective and its event type, Application & database triggers, Business application scenarios for implementing triggers, Define DML triggers, Define Non – DML triggers, Triggers event type & body, Creating DML triggers using the create triggers statement, Define statement level triggers v/s low level triggers, Triggers firing sequence: single row manipulation, Creating a DML statement triggers, Using old and new qualifiers, Old and new qualifiers, Instead of triggers, Managing triggers using the alter & drop SQL statement, Testing triggers

    Hands-on Exercise – Create a DML statement trigger, Use old and new qualifiers, Manage a trigger using the alter & drop SQL statement, Test the created triggers

    Compound Triggers
    Viewing trigger information, Describe user triggers, What is a compound trigger and working with it, Compound trigger structure for tables, Timing-point sections of a table compound trigger, Compound trigger structure for views, Trigger restrictions on mutating tables, Compound trigger restrictions, Using a compound trigger to resolve the mutating table error, Creating triggers on system events, LOGON and LOGOFF triggers example, Call statements in triggers, Benefits of database-event triggers, System privileges required to manage triggers

    Hands-on Exercise – View a trigger’s information, Use a compound trigger structure for views, Use a compound trigger to resolve the mutating table error, Create triggers on system events, Use LOGON and LOGOFF triggers, Call statements in triggers

    Working with Dynamic SQL
    Dynamic SQL-objectives, Describe execution flow of SQL statements, Dynamic SQL with a DDL statement-example, Working with dynamic SQL, Native Dynamic SQL(NDS), Using the executive immediate statement-example, Using native dynamic SQL to compile PL/SQL code, Using DBMS-SQL with a DML statement, Using the DBMS-SQL package subprograms, Parameterized DML statement

    Hands-on Exercise – Use the executive immediate statement, Use native dynamic SQL to compile PL/SQL code, Create DBMS-SQL with a DML statement, Create a DBMS-SQL package subprograms

    Advance level- Scripting
    Managing Dependencies, Objectives, overview of schema object dependencies, Direct local dependencies, Querying direct object dependencies, Displaying direct and indirect dependencies, Fine-Grained dependency management, Changes to synonym dependencies, Maintaining valid PL/SQL program units and views, Object re-validation, Concepts of remote dependencies, Setting the remote dependencies mode parameter, Recompiling PL/SQL program unit, Packages and Dependencies, Successful and unsuccessful recompilation, Recompiling procedures

    Hands-on Exercise – Query direct object dependencies, Display direct and indirect dependencies, Set the remote dependencies mode parameter, Recompile PL/SQL program unit, Edit a procedure and recompile it

    Oracle DBA Course Content

    Database Introduction

    What is the Oracle database? How it functions? Installation of Oracle database.

    Oracle Database Architecture

    The Architecture of Oracle server, the key components – Oracle Instance and Oracle Database, the various file types – data files, control files, redo log files.

    Oracle Storage Structures

    The basics of Oracle Storage structures, logical storage units like tablespaces, data blocks, extents and segments, database Schemas, Schema Objects, Operating System blocks, table statement and way to check “create table”.

    Memory & Process Architecture

    The Oracle Instance consists of background processes and memory structures, learn about the Oracle process and memory architecture, shared pool, redo log buffer, and buffer cache.

    Alert & Trace files

    The Oracle background process errors can be monitored using the Trace Files and Alert Logs, learn how this can be deployed for Oracle Administration and support.

    Database Startup & User Requests

    The methodology of starting a database instance, initializing parameter files, preparing for startup, serving user requests, understanding of the Server process and user process.

    Database Security

    Learn about the powerful Oracle database security features, data privacy, regulatory compliance, “create user” process, altering and dropping users, generating profiles and limiting resources, auditing, activity monitoring and blocking.

    Database Schema Objects

    Understanding of Database Schema objects, the various types available like views, tables, clusters, indexes, sequence, database links, packages and procedures, learning how data is stored in database tables, creating of temporary tables and external tables.

    Deep dive into Schema Objects

    Mastering the database Schema objects, learning Materialized View, deploying user-generated schema object for generating sequence, the balanced search tree index structure for placing and locating files, learn about data concurrency and data consistency in multi-user databases, the concepts of Locking and Deadlocks.

    Oracle Network Environment

    Study the client/server Network Environment, configuring the network, connecting to the database, the Oracle Network Environment and database link.

    Oracle Backup & Recovery

    Understand how Oracle database ecosystem backup and recovery is deployed, the database testing, database standby, Media recovery options, backup in offline mode.

    Oracle Recovery Manager (RMAN)

    Learn the significance of Oracle Recovery Manager, how it deploys performance, management and restoration of entire databases, learn about SQL Loader, Oracle Data Pump for import and export and support for External Table.

    Data Dictionary & Dynamic Performance Tables

    The Oracle Data Dictionary contains information related to database privileges, objects, users, and roles, learn Dynamic Performance Table for identifying instance-level problems, get complete knowledge to work with Oracle DBA.

    Database Tuning

    Introduction to database tuning, ensuring higher throughput, lowering response time of time-critical applications, optimizing the storage space, SQL scripts, deploying Index Selectivity, Index Statistics and Table Statistics for tuning the database, understanding Oracle Chained Rows and Locks.

    Database Tuning Continued

    Deep diving into Oracle Database Tuning, shared pool tuning, improving performance using Data Dictionary, tuning of Data Dictionary, deploying PL/SQL code, code reuse, database buffer.

    Oracle PL SQL Project

    Project – DML

    Topics – In this project you will learn about the Data Manipulation Language (DML), understand all about the DML triggers which are a special type of stored procedures that are automatically executed when DML operation is performed. You will learn about the various DML operations like INSERT, UPDATE, or DELETE which when fired on a Table or View can activate a DML Trigger. This project involves working with database and application Triggers, deploying the Triggers for various business applications and testing of the DML Triggers.

    Oracle DBA Project

    Project – Database Tuning

    – This project is involved with working on the Oracle Database for deploying the database performance tuning which includes optimizing and homogenizing the database performance. It is associated with query tuning but also includes DBMS selection, and configuration of the database environment. Some of the tasks that you will perform are–

    • Space usage monitoring
    • SQL scripts monitoring
    • Database & SQL tuning
    • Table & Index Statistics
    • Chained Rows & Locks


Course Fee:
USD 211

Course Type:

Self-Study

Course Status:

Active

Workload:

1 - 4 hours / week

Attended this course?

Back to Top

Awards & Accolades for MyTechLogy
Winner of
REDHERRING
Top 100 Asia
Finalist at SiTF Awards 2014 under the category Best Social & Community Product
Finalist at HR Vendor of the Year 2015 Awards under the category Best Learning Management System
Finalist at HR Vendor of the Year 2015 Awards under the category Best Talent Management Software
Hidden Image Url

Back to Top