Oracle PL-SQL, DBA Training

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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”.

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.

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.

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

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.

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.

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.

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

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

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.

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.

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.

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.