Course Content

A. Oracle Database:  Introduction to SQL

1. Introduction

• Course Objectives, Course Agenda and Appendixes Used in this Course
• Overview of Oracle Database 12c and Related Products
• Overview of relational database management concepts and terminologies
• Introduction to SQL and its development environments
• What is the Oracle SQL Developer?
• Starting SQL*Plus from Oracle SQL Developer
• The Human Resource (HR) Schema
• Tables used in the Course

2. Retrieving Data using the SQL SELECT Statement

• Capabilities of the SELECT statement
• Arithmetic expressions and NULL values in the SELECT statement
• Column aliases
• Use of concatenation operator, literal character strings, alternative quote operator, and the DISTINCT keyword
• Use of the DESCRIBE command

3. Restricting and Sorting Data

• Limiting the Rows
• Rules of precedence for the operators in an expression
• Substitution Variables
• Using the DEFINE and VERIFY command

4. Using Single-Row Functions to Customize Output

• Describe the differences between single row and multiple row functions
• Manipulate strings with character function in the SELECT and WHERE clauses
• Manipulate numbers with the ROUND, TRUNC and MOD functions
• Perform arithmetic with date data
• Manipulate dates with the date functions

5. Using Conversion Functions and Conditional Expressions

• Describe implicit and explicit data type conversion
• Use the TO_CHAR, TO_NUMBER, and TO_DATE conversion functions
• Nest multiple functions
• Apply the NVL, NULLIF, and COALESCE functions to data
• Use conditional IF THEN ELSE logic in a SELECT statement

6. Reporting Aggregated Data Using the Group Functions

• Group Functions
• Creating Groups of Data
• Restricting Group Results

7. Displaying Data from Multiple Tables Using Joins

• Introduction to JOINS
• Types of Joins
• Natural join
• Self-join
• Non equijoins
• OUTER join

8. Using Subqueries to Solve Queries

• Introduction to Subqueries
• Single Row Subqueries
• Multiple Row Subqueries

9. Using the SET Operators

• Set Operators
• UNION and UNION ALL operator
• INTERSECT operator
• MINUS operator
• Matching the SELECT statements
• Using ORDER BY clause in set operations

10. Managing Tables using DML statements

• Data Manipulation Language
• Database Transactions
• Introduction to Data Definition Language
• Data Definition Language
• Introduction to Data Dictionary Views
• Introduction to Data Dictionary
• Describe the Data Dictionary Structure
• Using the Data Dictionary views
• Querying the Data Dictionary Views

11. Creating Sequences, Synonyms, Indexes

• Overview of sequences
• Overview of synonyms
• Overview of indexes
• Creating Views
• Overview of views

12. Managing Schema Objects

• Managing constraints
• Creating and using temporary tables
• Creating and using external tables
• Retrieving Data by Using Subqueries
• Retrieving Data by Using a Subquery as Source
• Working with Multiple-Column subqueries
• Using Scalar subqueries in SQL
• Correlated Subqueries
• Working with the WITH clause
• Manipulating Data by Using Subqueries
• Using Subqueries to Manipulate Data
• Inserting by Using a Subquery as a Target
• Using the WITH CHECK OPTION Keyword on DML Statements
• Using Correlated Subqueries to Update and Delete rows
• Controlling User Access
• System privileges
• Creating a role
• Object privileges
• Revoking object privileges
• Manipulating Data
• Overview of the Explicit Default Feature
• Using multitable INSERTs
• Using the MERGE statement
• Performing flashback operations
• Tracking Changes in Data
• Managing Data in Different Time Zones
• Working with CURRENT_DATE, CURRENT_TIMESTAMP, and LOCALTIMESTAMP
• Working with INTERVAL data types

B. Admin, Install and Upgrade Accelerated

1. Introduction

• Course Objectives
• Course Schedule
• Overview of Oracle Database 
• Overview of Oracle Cloud
• Overview of the HR Schema
• Oracle Database  Introduction

2. Exploring the Oracle Database Architecture

• Oracle Database Architecture: Overview
• Oracle Database Instance Configurations
• Connecting to the Oracle Database Instance
• Oracle Database memory Structures (overview)
• Process Architecture
• Process Structures
• Process Startup Sequence
• Database Storage Architecture

3. Oracle Software Installation Basics

• Planning Your Installation
• Configuring Oracle Linux with Oracle RDBMS Pre-Install RPM
• Operating System Groups and Users
• Environment Variables
• Configuring the Oracle Software Owner Environment
• Using Oracle Universal Installer (OUI)
• Installation Option: Silent Mode

4. Installing Oracle Database Software

• System Requirements for Oracle Database
• Creating Operating System Groups and Users
• Types of Installations

5. Creating an Oracle Database by Using DBCA

• Planning the Database Storage Structure
• Types of Databases (based on workload)
• Choosing the Appropriate Character Set
• Understanding How Character Sets are Used
• Setting the NLS_LANG Initialization Parameter
• Using the Database Configuration Assistant (DBCA)

6. Oracle Database Management Tools

• Introducing Oracle Database Management Tools
• Using SQL*Plus
• Using SQL Developer
• Logging in to Oracle Enterprise Manager Database Express
• Using the Enterprise Manager Database Express Home Page
• Understanding the Enterprise Manager Cloud Control Framework
• Using Enterprise Manager Cloud Control

7. Managing the Database Instance

• Initialization Parameter Files
• Starting an Oracle Database Instance
• Shutting Down an Oracle Database Instance
• Viewing Log Files
• Using Trace Files
• Using the Dynamic Performance Views
• Data Dictionary

8. Configuring the Oracle Network Environment

• Oracle Net Services Overview
• Oracle Net Listener Overview
• Establishing Oracle Network Connections
• Connecting to a Server
• Tools for Configuring and Managing the Oracle Network
• Using the Listener Control Utility
• Using Oracle Net Configuration Assistant
• Using Oracle Net Manager

9. Administering User Security

• Database User Accounts (overview)
• Predefined Administrative Accounts
• Creating a User
• Authentication
• Unlocking a User Account and Resetting the Password
• Privileges
• Roles
• Profiles

10. Managing Database Storage Structures

• Understanding Storage of Data
• Database Block contents
• Exploring the Storage Structure
• Creating a New Tablespace
• Overview of Tablespaces Created by Default
• Managing Tablespaces
• Viewing Tablespace Information
• Using Oracle Managed Files

11. Managing Space

• Space Management Overview
• Block Space Management
• Row Chaining and Migration
• Free Space Management Within Segments
• Types of Segments
• Allocating Extents
• Allocating Space
• Creating Tables Without Segments

12. Managing Undo Data

• Undo Data Overview
• Transactions and Undo Data
• Storing Undo Information
• Comparing Undo Data and Redo Data
• Managing Undo
• Configuring Undo Retention
• Guaranteeing Undo Retention
• Changing an Undo tablespace to a Fixed Size
• Managing Data Concurrency
• Overview of Locks
• Locking Mechanism
• Data Concurrency
• DML Locks
• Enqueue Mechanism
• Lock Conflicts
• Implementing Oracle Database Auditing
• Separation of Responsibilities
• Database Security
• Monitoring for Compliance
• Standard Database Auditing
• Unified Audit Data Trail
• Separation for Duties for Audit Administration (AUDIT_ADMIN and AUDIT_VIEWER roles)
• Configuring the Audit Trail
• Specifying Audit Options
• Backup and Recovery Concepts
• Categories of Failures
• Flashback Technology
• Understanding Instance Recovery
• Phases of Instance Recovery
• Tuning Instance Recovery
• Using the MTTR Advisor
• Comparing Complete and Incomplete Recovery
• Oracle Data Protection Solutions
• Backup and Recovery Configuration
• Configuring for Recoverability
• Configuring the Fast Recovery Area
• Multiplexing the Control File
• Online Redo Log File
• Multiplexing the Online Redo Log File
• Archived Redo Log Files
• Archiver Process
• Archived Redo Log File Naming and Destinations
• Performing Database Backups
• Backup Solutions Overview
• Oracle Secure Backup (overview)
• User Managed Backup (overview)
• Backup Terminology and Types of Backups
• Using Recovery Manager (RMAN)
• Configuring Backup Settings
• Oracle-Suggested Backup
• Backing Up the Control File to a Trace File
• Performing Database Recovery
• Data Recovery Advisor
• Loss of a Control File
• Loss of a Redo Log File
• Loss of a Datafile in NOARCHIVELOG Mode
• Loss of a Noncritical Datafile in ARCHIVELOG Mode
• Loss of a System-Critical Datafile in ARCHIVELOG Mode
• Moving Data
• Moving Data: General Architecture
• Oracle Data Pump
• SQL*Loader
• External Tables
• Performing Database Maintenance
• Database Maintenance (overview)
• Viewing the Alert History
• Terminology
• Automatic Workload Repository (AWR)
• Statistic Levels
• Automatic Database Diagnostic Monitor (ADDM)
• Advisory Framework
• Enterprise Manager and Advisors
• Managing Performance
• Performance Monitoring
• Tuning Activities
• Performance Planning
• Instance Tuning
• Performance Tuning Methodology
• Performance Tuning Data
• Monitoring Performance
• Managing Memory
• Oracle Database Cloud Service: Overview
• Database as a Service Architecture, Features and Tooling
• Software Editions: Included Database Options and Management Packs
• Automated Database Provisioning
• Managing the Compute Node Associated With a Database Deployment
• Managing Network Access to Database as a Service
• Enabling Access to a Compute Node Port
• Scaling a Database Deployment & Patching Database as a Service
• Using the Oracle Database Cloud Service Console to Manage Patches

What is SQL Tuning and Why to Do That?

*       How an SQL Statement is Processed

*       What is "cost" in detail

*       Plan Generator

*       Execution Plan and Explain Plan in Details

*       V$SQL_PLAN View

Indexes

* Table Access Full

* Table Access by ROWID

* Index Unique Scan

Oracle Database Architecture

* PGA

* SGA

* Shared Pool

* Buffer Cache

* Redo Log Buffer

Tuning :

* Collection

* Introduction to BULK COLLECT feature

* Implementation of BULK COLLECT in PLSQL program

* FOR ALL loop with BULK COLLECT

* Dynamic SQL

* Partitioning

* Ref Cursors

* Understanding of PLSQL Tables

* Arrays

* Nested Tables

* Associative arrays

* Some part of tuning

* Bind variable

* Function based index

* Some part of Hints

Trainer Profile

Interview Questions & Answer

1) What is PL/SQL?

It is a procedural language extension over SQL provided by Oracle. It facilitates declaration of variables, functions, and conditional operators in SQL syntax thereby giving the developer more freedom and ease to design complex query.

2) What Is Schema?

A schema is collection of database objects of a user.

3) What Is System Tablespace and When Is It Created?

Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

4) What are savepoints?

Savepoints are like markers. While executing a long transaction, it is a good programming practice to set up periodic savepoints so that we can roll back to the save point if in case the transaction fails.

5) What is BLOB?

BLOB is a binary large object datatype. BLOG is used to stored unstructured data such as video, audio or image. Maximum capacity of a BLOB is 4GB-1 in Oracle.

6) What is a database trigger? How to create it?

A database trigger is a stored PL/SQL block. Oracle database executes it automatically when certain conditions are satisfied. The stored PL/SQL block is connected to a table, a schema or a database .A trigger can be created using the CREATE TRIGGER clause. We can choose to enable or disable it using the ENABLE and DISABLE clauses of the ALTER TABLE or ALTER TRIGGER statement. This is the frequently asked Oracle Interview Questions in an interview.

7) What is dynamic SQL? When to use dynamic SQL?

Dynamic SQL is an enhancement over static SQL. It enables writing SQL queries at run-time. Dynamic SQL comes into picture when we need to customize SQL queries during execution.
Few common cases where Dynamic SQL can be utilized:
•If database objects do not exist at compile time, we can use dynamic SQL to reference them.
•Certain DDL (Data Definition Language) statements and SCL (Session Control Language) Statements are not supported by Static SQL in PL/SQL. These statements can be executed by via dynamic SQL programming.
•Dynamic SQL can be used for executing Dynamic PL/SQL block. By using EXECUTE IMMEDIATE clause, PL/SQL calls can be determined at runtime.

 8) What is Control File used for?

Control File is used for:
- it is used for Database recovery.
- Whenever an instance of an ORACLE database begins, its control file is used to identify the database and redo log files that must be opened for database operation to go ahead.

9) What is a sub query? What are its various types?

- Sub Query also termed as Nested Query or Inner Query is used to get data from multiple tables.
- A sub query is added in the where clause of the main query.

 Two types of subqueries are:
a) Correlated sub query
- It can reference column in a table listed in the from list of the outer query but is not as independent as a query. .

b) Non-Correlated sub query
- Results of this sub query are submitted to the main query or parent query.
- It is independent like a query

10) What is a synonym?

A synonym is also called as alias for a table, view, sequence or program unit.

11) What are the different types of synonyms?

There are two types of synonyms or alias:

Private: It can accessed only by the owner.

Public: It can be accessed by any database user.

12) What are Views and why they are used?

 A view is referred as the user-defined object in Oracle database. It is used to retrieve the records from the different tables for the specific columns which are required to populate or selected based on the certain conditions. In other words, a View logically represents subsets of data from one or more table.  A View contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called Base Tables. The View is stored as a SELECT statement in the data dictionary. View definitions can be retrieved from the data dictionary table: USER_VIEWS.

Views are used:

• To restrict data access
• To make complex queries easy
• To provide data Independence
• Views provide groups of user to access data according to their requirement

13) Explain Role in Oracle?

Roles are used to providing access to objects and users in the group which has common privileges assigned in the database. The role can be provided with help of the grant and revoke command to provide the access to and revocation from the users.

14) How to convert a date to char in Oracle? Give one example.

The to_char() function is used to convert date to character. You can also specify the format in which you want output.

1. SELECT to_char ( to_date ('12-12-2012', 'DD-MM-YYYY') , 'YYYY-MM-DD') FROM dual;  

Or,

1. SELECT to_char ( to_date ('12-12-2012', 'DD-MM-YYYY') , 'DD-MM-YYYY') FROM dual; 

Blog

Six strong reasons why Oracle Digital Assistant is a must-have

Critical advantages of Digital Assistance:

1. Integration with multiple applications.

Oracle DA lets you enhance any finance and operations process to allow users to work seamlessly across multiple applications. Pre-built skills (individual bots focused on specific tasks) mean Oracle Cloud ERP and SCM users are pre-provisioned and get started with these skills right away. The Assistant helps drive self-service, making it easy for users to access data, pull reports, and perform submissions and also approvals.

2. Leverage AI-supported voice commands.

 Oracle DA uses natural language processing and AI, so that it will learn, understand, and even interpret what employees are requesting. It supports industry-specific terminology, including acronyms that finance and operations teams use, such as EBITDA. And as the AI-powered Digital Assistant listens and learns, it can offer insights and recommendations to the team.

3. Improve team collaboration. 

Oracle DA runs on your device of choice and across messaging platforms such as Facebook Messenger, Slack, and Microsoft Teams. Using Oracle Digital Assistant within these collaboration tools, finance teams can get conversational access to contextual data and insights or even drive relevant actions.

4. Automate manual tasks. 

As Oracle Digital Assistant is AI-driven, finance teams no longer need to comb through data, and this prevents human errors. It quickly breezes through workflows and enforces policies, and you can upload receipts on the fly. The assistant helps finance teams use their limited time and resources for more value-added strategic initiatives.

5. Streamlined collaboration with real-time intelligence. 

With Oracle DA, SCM teams can send status updates to employees and customers in real time while empowering collaboration. The Assistant helps the supply chain’s stakeholders easily share resources, data, responsibilities, and performance metrics to satisfy demanding customers. Teams also have access to self-service functionality, so it’s easier to monitor dynamic supply chain activities such as orders and shipments. These capabilities help SCM teams to democratize access to information, which in turn empowers organizations to streamline the collaboration.

6. Multi-language voice and text support. 

SCM teams can enhance their productivity by taking advantage of Oracle Digital Assistant’s multi language support. For example, if someone uses the assistant in English, their colleague in another country could use the same assistant in a different language, thus improving the supply chain’s efficiency. Lost in translation no more!