An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management. In general, a server reliably manages a large amount of data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.

Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost effective way to manage information and applications. Enterprise grid computing creates large pools of industry-standard, modular storage and servers. With this architecture, each new system can be rapidly provisioned from the pool of components. There is no need for peak workloads, because capacity can be easily added or reallocated from the resource pools as needed.

SQL Syllabus

• Oracle Basics
• Database models
• ER Model Overview
• Data types
• Understanding Test Database
• Basics Queries
• Arithmetic and String functions
• DML Operations – Insert, Update and Delete
• Data Filters Using Operators
  • DISTINCT
  • BETWEEN
  • IN
  • LIKE
  • NOT
  • NULL
  • AND, OR, NOT etc
  • Using Complex Data Filtering Techniques
• Data Sorting
  • Ascending Order
  • Desending Order
  • Complex Sorting
• String Functions
  • Lowercase
  • Uppercase
  • In string
  • Sub String
  • length

• Number Functions
  • number formatting
  • Round
  • Trunc
  • Mode etc.
  • currency formatting
  • convert from string to number and vice versa etc.
• Date Functions
  • Date Formatting
  • Date difference
  • last day etc
  • Conversion from String to Date and vice versa etc.
• Nested Functions
• Handle Null Values :NVL, NVL2, NULLIF, COALESCE
• Conditional Expressions – Case and Decode

  Groups

• Using Grouping functions – AVG, MIN, MAX, COUNT, DISTINCT COUNT, HAVING etc
• Group Filters – HAVING

  Joins

• Cartesian Product
• Equi Joins
• Non Equi Joins
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Left Outer Join
• Self Join

• Sub Query & Co-related Sub Query
  • Single Row Sub Query
  • Multi Row Sub Query
  • Co related Sub Query
• Views
• Inline Views
• Complex Queries
  • Joins with Groups
  • Inline views, Joins, Sub Query Integration

  SET Operators

• Union
• Union All
• Intersect
• Minus

• DDL – Create, Alter and Drop
  • Tables
  • Indexes
  • Sequence
  • Synonyms
• DML – Data Manipulation Language
  • Insert
  • Update
  • Delete
  • Truncate
• Advanced DML Operations
• Transaction Mgt (T SQL)
  • Commit
  • Rollback
  • Read Consistency
• Constraints
  • Primary Key
  • Unique
  • Foreign Key
  • Not Null
  • Check
• Foreign Key Effect
  • Effect of Foreign Keys on DML & DDL Operations
• Creating Tables Using Queries
• Rolups & Cubes
• DCL – Data Control Language
  • Managing Security
  • Creating Users & Roles
  • Manage User & Role Privileges
• Interview Prepare Session
• Project

PL SQL Syllabus

• PL SQL Basics
• Anonymous Blocks
• Data types, Variable, Composite Variables etc
• Control Structures
  • if-else-elsif
  • while
  • loop…endloop
  • for loop
  • nested loop
• Basic Programs
• Logic Building Sessions
• Using SQL Queries in PLSQL
• Transactions in PLSQL
• Exception Handling
  • Working with Exception Block
  • Handling Oracle Defined Exceptions
  • Creating User Defined Exceptions

• Cursors
  • Implicit Cursors
  • Explicit Cursors
  • Cursor For Loops
  • Cursor with parameters
  • Reference Cursors
• Procedures
  • Creating Procedures
  • Overloading Procedures
  • Exception handling and propagation
• Nested Procedures
• Functions
  • Creating Functions
  • Nested Functions
  • Overloading Functions
  • Nesting Functions and Procedures
  • Functions in Select and DML Queries
• Nested Functions
• Integrating Prodedures & Functions

• Packages
  • Package Basics
  • Package Specification
  • Package Body
  • Global constants
  • Writing Procedures and Functions in Pacakges
  • Procedure Overloading
  • Function Overloading
  • Exception Handling
• Oracle Supplied Packages
  • Execute Immediate

• Triggers
  • Create, Replace, Drop
  • Trigger Types:
  • Statement Level Triggers
  • Row Level Triggers
  • INSTEAD OF Triggers Overview
• Bulk Collect Overview
• Global Temorary Table
  • Creation and use in Procedures
• Interview Prepare Sessions
• Project

DBA Syllabus

2 months
On Oracle 19c

• Introduction, Basics & Overview of Oracle and Oracle DBA
• Linux Setup on (Oracle VM) Virtual Machine
  • Setting up a VM (Virtual Machine)
  • Importing Linux Image into VM
  • Linux Commands – Example (ps, ls, top, df and many more) as required for DBA
• Installing Oracle Database Server on Linux VM
  • Installing Oracle 18c XE DB via rpm
  • Setting bash profiles
  • Working with Listeners, Tnsnames.ora & Listener.ora files
• Understanding Oracle Database Architecture
  • Architectural Components
  • Background Processes
  • Memory structures like SGA, PGA, shared pool, large pool and many more
• Understanding Oracle Database Instance
  • PFILE, SPFILE and parameter changes
  • DB startup and shutdown modes
  • Alert.log, dictionary views and dynamic performance views

• Understanding Database Storage Structures
  • Blocks
  • Extents
  • Segments
• Tablespace Management
  • Introduction to Tablespace
  • Tablespace Creation
  • Managing Datafiles
  • Undo Tablespace
  • Temp Tablespace
• Creating Database
  • Updating server files
  • Mapping Database Instance
  • Mapping Tablespaces
  • Mapping users
  • Setting up prvileges
• Using Database
  • Table Creation
  • CRUD Operations
  • Table Management
• Administering User Security and Resources
  • Creating Users, Roles etc
  • Assigning Roles
  • Managing Profiles

• Managing Data Concurrency
  • Understanding Object and Row locking
  • Simulating locking Scenario, Detecting and Resolving it via SQL scripts
• Backup and Recovery
  • Types of backups: Hot and Cold
  • RMAN Backups
  • Putting DB in Archivelog modes
  • Restoring DB from RMAN backup
• Data Export / Import
  • SQL Loader
  • Export (and taking backups with export)
  • Export Datapump
• Misc. Topics
  • db links
  • dbms_scheduler
• SQL Tuning (Performance Enhancements)
  • Explain Plan
  • DBMS_STATS – Gathering statistics via Oracle supplied package DBMS_STATS
  • Creating Appropriate Indexes
  • Table Re-Organization

• Partitioning
  • Types of Partitioning schemes
  • Table Partitioning, Tablespace Partitioning etc.
  • Understanding Effect of Partitioning
  • Listing Partitions
• Instance Tuning & AWR Reports
  • Understanding AWR reports
  • AWR vs ASH reports
  • Generating AWR reports
  • Reading AWR reports
• Tuning Advisors
  • SQL query tuning advisor
  • Memory advisors: SGA, PGA, Database Buffer cache advisors
  • Automatic Database Diagnostics Monitor (ADDM) reports
• Oracle 18c Multitenant Architecture
  • Basics
  • Creating PDBs
  • Plugging and unplugging PDBs
  • Cloning a PDB within CDB
• Certification Preparation
• Interview Prepare Sessions