Tag: oracle

Duration

14 hours (usually 2 days including breaks)

Requirements

• Computer skills
• Knowledge of any operating system

Overview

The course answers questions

How to build a query?

What possibilities have SQL?

What is a relational database?

What is the structure and SQL commands

Course Outline

Relational database models

• The structure of a relational database
• Connection types of tables
• The normalization and denormalization database
• Database Management System (RDBMS)

Relational Operators

Characteristics of declarative SQL language SQL Syntax Division language DQL, DML, DDL, DCL Language DQL (Data Query Language)

• SELECT queries
• Aliases columns, tables
• Service date (DATE types, display functions, formatting)
• Group Features
• Combining internal and external tables
• UNION operator
• Nested Subqueries (the WHERE clause, the table name, column name)
• Correlated subqueries

Data Modification Language

• Inserting rows (INSERT clause)
• Inserting via query
• Updating rows (UPDATE)
• Removing rows (DELETE)

Data Definition Language

• Create, change of ownership, remove tables (CREATE, ALTER, DROP) Creating tables by asking (CREATE TABLE …. AS SELECT …)

CONSTRAINTS

• Options NULL and NOT NULL
• CONSTRAINT clause
• Condition PRIMARY KEY
• Condition UNIQUE
• Condition FOREIGN KEY
• DEFAULT clause

Transactions

• The command COMMIT, ROLLBACK, SAVEPOINT

Language DCL

• Granting and revoking permissions (GRANT, REVOKE)
• Roles
• Creating Users
• sequences
• Synonyms
• The views (perspective)

Duration

21 hours (usually 3 days including breaks)

Requirements

• Experience with application development
• Familiarity with Oracle SQL Database

Audience

• Developers

Overview

Oracle APEX or Oracle Application Express is an application development service in Oracle Database. It provides a fully-integrated, secure, and low-code platform for building and deploying applications.

This instructor-led, live training (online or onsite) is aimed at developers who wish to use Oracle APEX to develop, scale, and deploy applications faster with less code.

By the end of this training, participants will be able to:

• Set up the necessary development environment to start developing database apps with Oracle APEX.
• Understand and explore the Oracle APEX tools to build a fully-featured application.
• Learn how to create applications and pages using Oracle APEX and the Page Designer UI.
• Extend the functionality of complex enterprise apps easily with Oracle APEX.
• Optimize and scale application performance and deployment.
• Control and secure access to application data and components.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Course Outline

Introduction

• Overview of Oracle APEX features and architecture
• Understanding database applications

Getting Started

• Installing Oracle APEX
• Accessing the development environment
• Exploring the UI and tools

Creating an Application

• Setting up the workspace
• Using the Create Application wizard
• Adding and removing application features
• Creating a database app from a file
• Using an application blueprint
• Copying an existing database application
• Creating an application for mobile devices
• Configuring application attributes
• Managing application groups
• Using application and workspace utilities

Managing Pages

• Adding pages to an application
• Creating dialog pages
• Branching with dialog pages
• Copying database application pages
• Deleting database application pages
• Locking and unlocking pages

Using Page Designer

• Navigating the Page Designer UI
• Managing page attributes
• Running a page from Page Designer
• Using the runtime developer toolbar
• Copying and deleting an application page

Building Reports and Forms

• Creating a report using the wizard
• Managing cards in a page
• Creating and editing faceted search pages
• Customizing interactive grids and reports
• Managing classic reports
• Viewing and printing reports
• Understanding form types
• Creating a form using the wizard
• Creating master details forms
• Validating user inputs
• Working with BLOBs

Customizing Application Controls and Navigation

• Managing page-level items
• Adding dynamic actions, buttons, and trees
• Creating navigation menus, breadcrumbs, and tabs
• Using computations, validations, and processes
• Working with shared components
• Extending application functions (e-mail, plug-ins, globalization, etc.)

Managing Application Security and Performance

• Security best practices
• Controlling access to applications, pages, and components
• Configuring user identity (authentication and authorization)
• Identifying performance issues
• Logging and reporting
• Creating custom reports
• Testing and debugging applications

Deploying Applications

• Understanding the deployment process
• Creating, managing, and upgrading custom applications
• Building the applications and components
• Publishing the database application URL
• Exporting applications and components
• Importing and installing exported files
• Deploying an application remotely

Troubleshooting

Summary and Next Steps

Duration

14 hours (usually 2 days including breaks)

Requirements

• Experience with Oracle applications
• Familiarity with Cloud Computing

Audience

• IT administrators
• Developers

Overview

Oracle PaaS is a combination of Oracle services and open-source technologies delivered as a service to seamlessly build, deploy, and manage applications in the cloud. It provides ready-to-use infrastructure, framework, and programming components that enable developers to extend or customize applications.

This instructor-led, live training (online or onsite) is aimed at IT administrators and developers who wish to set up, deploy and manage cloud applications using the services and technologies provided by Oracle PaaS.

By the end of this training, participants will be able to:

• Build, deploy, and manage cloud applications using Oracle PaaS.
• Configure and use data management tools for easy storage and backup.
• Visualize and evaluate application performance and logs.
• Generate business analytics reports.
• Understand and implement security standards.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Course Outline

Introduction

Overview of Oracle PaaS Features and Architecture

Understanding Oracle PaaS Services and Technology Stack

Building, Deploying, and Migrating Cloud Applications

Managing Data With Oracle PaaS

Monitoring Application Performance, Logs, and Orchestration

Implementing Security Compliance Standards

Using Business Analytics Tools

Optimizing Content and Processes

Troubleshooting

Summary and Conclusion

Duration

14 hours (usually 2 days including breaks)

Requirements

• Experience with Oracle Forms.

Audience

• Database engineers
• Developers

Overview

This instructor-led, live training (online or onsite) is aimed at engineers who wish to migrate from Oracle Forms to Oracle Web Forms.

By the end of this training, participants will be able to:

• Understand the major feature and architectural changes in Oracle Web Forms.
• Migrate to Oracle Web Forms from a previous Forms version.
• Deploy an Oracle Web Application.
• Leverage Oracle Web Forms to enhance user services and experience.

Format of the course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Course Outline

Introduction

Overview of Oracle Web Forms Features and Architecture

• From Forms 6i to Web Forms
• Understanding web standards (XHTML, CSS, Javascript)

Oracle Web Forms Deprecated Functionality

Migrating to Web Forms

• Upgrading an application

Building Web Forms with Oracle Application Express (Apex)

• Conversion tools
• Creating an application

Building Web Forms with Oracle BPM

• Creating an application with Oracle Business Process Composer

Migrating to a Non-Oracle Web Stack (optional)

• Ruby on Rails, NodeJS, PHP, etc.

Deploying a Web Application

Troubleshooting

Summary and Conclusion

Duration

14 hours (usually 2 days including breaks)

Requirements

• Experience with Oracle Database.

Audience

• Database engineers
• Developers
• System administrators

Overview

Oracle Database 18c is the next iteration of Oracle Database 12c Release 2, or “the first patch”. 

This instructor-led, live training (online or onsite) is aimed at engineers who wish to set up, deploy, migrate to and manage an Oracle 18c Database.

By the end of this training, participants will be able to:

• Understand the major feature and architectural changes from previous Oracle versions.
• Migrate to Oracle 18c from a previous version.
• Deploy Oracle 18c in cloud or on premise.
• Understand Oracle 18c’s new yearly release cycle and plan maintenance around it.

Format of the course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Course Outline

Introduction

• Oracle 18c and Oracle’s Autonomous Database Cloud

Overview of Oracle 18c Features and Architecture

• On-premises and in cloud

Oracle 18c Incremental Enhancements over Oracle Database 12c Release 2.

• Performance, High Availability, Multitenant Architecture, Security, Data Warehousing and Application Development

Oracle 18c Major Enhancements over Older Versions

• Multitenant, Database In-Memory, Database Sharding, Connection Manager, etc.

Oracle 18c Deprecated Features from Older Versions

• Non-CDB architecture, Oracle Streams, Oracle Multimedia, Oracle Administration Assistant for Windows, etc.

Upgrading from Previous Versions of Oracle

• Multi-step Upgrade (10g => 12c => 18c)
• Datadump “Upgrade” (Import/Export)

Understanding Oracle’s Autonomous Database Cloud

• Cost-based Optimization, Performance Tuning and Diagnostics, Data Automization, Cloud Operations

Automating Routine Administrative Tasks

• Self-driving, Self-securing, Self-repairing

Deploying Oracle to a Public, Private and Hybrid Cloud

• Oracle Cloud and Oracle Exadata

Troubleshooting

Summary and Conclusion

Duration

28 hours (usually 4 days including breaks)

Requirements

SQL basic knowledge

Overview

The training is intended not only for Oracle database administrators  both who are just starting to work with Oracle servers and who are already working on such one and want to learn about the new futures in 12c version. This training can be also useful for programmers who – thanks to the understanding of server architecture and its mechanisms rules – will be able to create effective applications working with the Oracle 12c database.

Course Outline

Oracle architecture overview
   Instance and database
   New instance features in Oracle 12c
   Multitenant architecture
   Container databases
   Pluggable databases

System security, instance and database configuration
    Server access management and database access for SYSDBA role users
    Initialization parameters – binary and text parameter file
    Starting and shutting down the instance
    Opening and closing the database
    Opening and closing of pluggable databases (PDBs) in Oracle 12c
    Status of the instance and databases (container and pluggable ones)
    Permissions
    Roles
    Profile management

Oracle Net
    Call descriptors
    Network services
    Listener server process
    Oracle Net Manager
    Serve and client configuration for remote connection
    
Logical storage structure
    Tablespaces
    Segments
    Extents
    Blocks

Database users
    Database architecture
    Schemas and their objects
    Global and local schema in Oracle 12c
    User management in Oracle 12c

Logical database structure
    Tabels
    limitations
    Indexes
    Views
    Profiles
    Sequences
    Synonyms
    Directories

Managing the physical database structure
    Initialization parameters and passwords
    Alert.log file and trace files
    Controlfiles management
    Adding and moving control files
    Redo.log files management
    Mirrorig, adding and moving redo files
    Switching the database mode and the difference between the ARCHIVELOG and NOARCHIVELOG modes
    Archivelog file management
    Mirroring, adding and moving archivelog files
    Data file management and free space on disks
    Moving data files
    Database files in Oracle 12c containers
    Automatic Storage Management architecture

Disk space management
    Problems with free space in tablespaces
    Free disk space management
    Providing space for temporary segments
    Fragmented tablespaces and segments
    Multiple files tablespaces
    Segment advisor
    Undo Advisor
    Use of indexes

Memory structures within instances
    SGA area
    PGA area
    Area of ​​executable code
    Secondary processes
    Dynamic memory management
    ASMM since 10g and AMM since 11g

Creating a database
Differences in the creation of non-PDB and PDB databases
Selecting and updating data (how SQL commands are processed)
Ensuring consistency and concurrency of transactions
Database structure management
Tablespaces
    Types of tablespaces
    OFA (Optimal Flexible Architecture) architecture
    System tablesspace
    SYSAUX tablespace
    TEMP tablespace
    UNDO tablespace
    USERS table space
    EXAMPLE table space
    Tablespace management
    Resizing table space
    Segments in tablespaces
    Moving tables to different tablespaces or to the same tablespace vs HighWaterMark vs Indexes

Archiving and database recovery:
    Hot and cold backup
    Backup of datafiles and control files
    Restoring and recovering the database
    RMAN – Recovery Manager
    Backup using the RMAN tool
    Restoring and recovering database using the RMAN tool

Oracle Flashback
    Flashback Query – retrospective queries
    DBMS_FLASHBACK package – moving the session back and forward in time
    FlashbackVersions Query – querying data versions over time
    FlashbackTransaction Query – monitoring of all DML commands in database
    FlashbackTable – moving tables back and forward in time
    Flashback Drop – retrieving dropped tables back
    Flashback Database – moving the database back in time

National language support
Oracle Scheduler and the DBMS_SCHEDULER package
Control system resources

Duration

7 hours (usually 1 day including breaks)

Requirements

• Experience with database management systems
• Familiarity with cloud computing

Audience

• Database administrators
• Cloud architects

Overview

On-premise, Oracle database management systems can be costly and difficult to maintain for mid and large-scale organizations. Migrating Oracle databases to Azure SQL (also known as Microsoft Azure SQL Database, Microsoft Azure SQL or AzureSQL) or PostgreSQL (also known as Postgres or Postgre SQL) is a practical solution for growing businesses to address database management, maintenance, and scalability at an optimum cost.

This instructor-led, live training (online or onsite) is aimed at database administrators and cloud architects who wish to migrate Oracle databases to Azure SQL or PostgreSQL.

By the end of this training, participants will be able to:

• Compare Azure SQL with PostgreSQL to choose the database that best meets business objectives and needs.
• Prepare databases to move definitions, data, and procedures from Oracle to AzureSQL or PostgreSQL database system.
• Perform post-migration steps to test, optimize, update, and maintain a migrated database.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Course Outline

Introduction

Overview of Oracle Migration to Azure SQL or PostgreSQL

Designing a Proof of Concept Solution

Comparing Azure SQL Database With PostgreSQL Database

Configuring SQL Server Instances

Setting up Oracle Express Edition

Preparing the Database for Migration

Migrating SQL Server to Azure SQL

Deploying Post Upgrade Enhancements

Migrating Oracle Database to SQL Server 2017

Migrating the Application to Azure SQL

Migrating SQL Server to PostgreSQL

Preparing Oracle Database Migration to PostgreSQL

Migrating the Database and Application to PostgreSQL

Performing Post-Migration Tests and Optimization

Troubleshooting

Summary and Conclusion

Duration

14 hours (usually 2 days including breaks)

Requirements

The course is suitable for those who have some knowledge of SQL as well as those who are using ORACLE for the first time.

Previous experience with an interactive computer system is desirable but not essential.

Overview

This 2 day course gives an introduction to SQL Developer, SQL*Plus and to SQL, the Structured Query Language used to access a Relational Database and includes the new features of the latest version of ORACLE. The principles learnt may also be applied to databases as diverse as Microsoft SQL Server, MySQL, Access, Informix and DB2.

The course takes the format of a workshop, with a mix of lecture, working examples and practical exercises. Although the content may be customised, at least 2 days are needed to cover the core elements.

Full course notes are provided along with sample database files, example SQL files and free software tools for use in accessing an ORACLE database.

Course Outline

Introduction

• Overview
• Aims and Objectives
• Sample Data
• Schedule
• Introductions
• Pre-requisites
• Responsibilities

Relational Databases

• The Database
• The Relational Database
• Tables
• Rows and Columns
• Sample Database
• Selecting Rows
• Supplier Table
• Saleord Table
• Primary Key Index
• Secondary Indexes
• Relationships
• Analogy
• Foreign Key
• Foreign Key
• Joining Tables
• Referential Integrity
• Types of Relationship
• Many to Many Relationship
• Resolving a Many-to-Many Relationship
• One to One Relationship
• Completing the Design
• Resolving Relationships
• Microsoft Access – Relationships
• Entity Relationship Diagram
• Data Modelling
• CASE Tools
• Sample Diagram
• The RDBMS
• Advantages of an RDBMS
• Structured Query Language
• DDL – Data Definition Language
• DML – Data Manipulation Language
• DCL – Data Control Language
• Why Use SQL?
• Course Tables Handout

Data Retrieval

• SQL Developer
• SQL Developer – Connection
• Viewing Table Information
• Using SQL, Where Clause
• Using Comments
• Character Data
• Users and Schemas
• AND and OR Clause
• Using Brackets
• Date Fields
• Using Dates
• Formatting Dates
• Date Formats
• TO_DATE
• TRUNC
• Date Display
• Order By Clause
• DUAL Table
• Concatenation
• Selecting Text
• IN Operator
• BETWEEN Operator
• LIKE Operator
• Common Errors
• UPPER Function
• Single Quotes
• Finding Metacharacters
• Regular Expressions
• REGEXP_LIKE Operator
• Null Values
• IS NULL Operator
• NVL
• Accepting User Input

Using Functions

• TO_CHAR
• TO_NUMBER
• LPAD
• RPAD
• NVL
• NVL2 Function
• DISTINCT Option
• SUBSTR
• INSTR
• Date Functions
• Aggregate Functions
• COUNT
• Group By Clause
• Rollup and Cube Modifiers
• Having Clause
• Grouping By Functions
• DECODE
• CASE
• Workshop

Sub-Query & Union

• Single Row Sub-queries
• Union
• Union – All
• Intersect and Minus
• Multiple Row Sub-queries
• Union – Checking Data
• Outer Join

More On Joins

• Joins
• Cross Join or Cartesian Product
• Inner Join
• Implicit Join Notation
• Explicit Join Notation
• Natural Join
• Equi-Join
• Cross Join
• Outer Joins
• Left Outer Join
• Right Outer Join
• Full Outer Join
• Using UNION
• Join Algorithms
• Nested Loop
• Merge Join
• Hash Join
• Reflexive or Self Join
• Single Table Join
• Workshop

Advanced Queries

• ROWNUM and ROWID
• Top N Analysis
• Inline View
• Exists and Not Exists
• Correlated Sub-queries
• Correlated Sub-queries with Functions
• Correlated Update
• Snapshot Recovery
• Flashback Recovery
• All
• Any and Some Operators
• Insert ALL
• Merge

Sample Data

• ORDER Tables
• FILM Tables
• EMPLOYEE Tables
• The ORDER Tables
• The FILM Tables

Utilities

• What is a Utility?
• Export Utility
• Using Parameters
• Using a Parameter file
• Import Utility
• Using Parameters
• Using a Parameter file
• Unloading Data
• Batch Runs
• SQL*Loader Utility
• Running the Utility
• Appending Data

Duration

28 hours (usually 4 days including breaks)

Requirements

• Basic Linux and SQL knowledge

Audience

• Database administrators and developers
• Cloud database architects
• Application developers

Overview

Oracle Database 21c is a converged database that supports a large range of workloads and data models. It’s a data management platform that provides high performance, security, availability, and scalability for cloud environments.

This instructor-led, live training (online or onsite) is aimed at database administrators who wish to learn and use the features of Oracle Database 21c in performing administrative tasks.

By the end of this training, participants will be able to:

• Understand the core concepts of Oracle Database 21c administration.
• Understand the Oracle 21c architecture.
• Learn how to perform administration tasks on Oracle 21c.
• Understand the Oracle 21c backup and recovery process.

Format of the Course

• Interactive lecture and discussion.
• Lots of exercises and practice.
• Hands-on implementation in a live-lab environment.

Course Customization Options

• To request a customized training for this course, please contact us to arrange.

Course Outline

Introduction

• Introduction to Oracle Cloud
• Overview of Oracle Database 21c

Oracle Database Architecture

• Overview of the Oracle Database architecture
• Configuring and connecting to the Oracle Database Instance
• Understanding the Oracle Database memory structures
• Process architecture and structures
• Process startup sequence
• Storage architecture

Database Instance

• Overview of the Oracle Database management tools
• Enterprise manager management framework

Setting Up the Oracle Network Environment

• Overview of the Oracle Net Services and Oracle Net Listener
• Oracle Network Connections
• Configuring the Oracle Network tools
• Listener Control Utility
• Oracle Net configuration assistant
• Oracle Net Manager
• Enterprise Manager Cloud Control

Database Storage Structures

• Overview of Database Block Content
• Storage structure
• Creating and managing Tablespaces
• Navigating and viewing Tablespace information
• Using Oracle managed files

User Security

• Managing database user accounts
• Predefined administrative accounts
• Creating a user account
• Authentication and password reset
• Understanding the concept of privileges, roles, and profiles

Data Concurrency

• Understanding the locking mechanism
• Understanding data concurrency
• DML locks
• Enqueue mechanism
• Lock conflicts

Undo Data

• Overview of Undo data
• Managing transactions and storing Undo information
• Undo data vs. Redo data
• Configuring and guaranteeing Undo retention
• Changing the size of an Undo Tablespace

Oracle Database Auditing

• Overview of database auditing
• Database security and compliance monitoring
• Standard database auditing
• Audit administration
• Configuring audit train
• Audit options

Database Maintenance

• Overview of database maintenance
• Alert history
• Automatic Database Diagnostic Monitoring (ADDM)
• Understanding the advisory framework
• Enterprise manager and advisor

Managing Performance and Resources

• Monitoring performance
• Tuning activities and performance planning
• Instance tuning
• Performance tuning methodology and data
• Managing memory
• Overview of Database Resource Manager
• Using the Database Resource Manager
• Maintenance Resource Manager plan
• Resource Manager workflow
• Resource allocation methods

Space Management

• Block space management
• Chaining and migration
• Understanding the types of segments
• Allocating extents and space
• Creating tables without segments

Backup and Recovery

• Overview of Instance Recovery
• Understanding and configuring Instance Recovery
• Instance Recovery Phases
• Using MTTR Advisor
• Understanding Media Failure

Summary and Next Steps

Duration

14 hours (usually 2 days including breaks)

Requirements

Recommended attendance on the “Oracle SQL for Beginners” training course

Overview

The objective of the course is to enable participants to gain a mastery of how to work with the SQL language in Oracle database for data extraction at intermediate level.

Course Outline

Limiting results

• The WHERE clause
• Comparison operators
• LIKE Condition
• Prerequisite BETWEEN … AND
• IS NULL condition
• Condition IN
• Boolean operators AND, OR and NOT
• Many of the conditions in the WHERE clause
• The order of the operators.
• DISTINCT clause

SQL functions

• The differences between the functions of one and multilines
• Features text, numeric, date,
• Explicit and implicit conversion
• Conversion functions
• Nesting functions
• Viewing the performance of the functions – dual table
• Getting the current date function SYSDATE
• Handling of NULL values

Aggregating data using the grouping function

• Grouping functions
• How grouping functions treat NULL values
• Create groups of data – the GROUP BY clause
• Grouping multiple columns
• Limiting the function result grouping – the HAVING clause

Subqueries

• Place subqueries in the SELECT command
• Subqueries single and multi-lineage
• Operators Subqueries single-line
• Features grouping in subquery
• Operators Subqueries multi-IN, ALL, ANY
• How NULL values ​​are treated in subqueries

Operators collective

• UNION operator
• UNION ALL operator
• INTERSECT operator
• MINUS operator

Further Usage Of Joins

• Revisit Joins
• Combining Inner and Outer Joins
• Partitioned Outer Joins
• Hierarchical Queries

Further Usage Of Sub-Queries

• Revisit sub-queries
• Use of sub-queries as virtual tables/inline views and columns
• Use of the WITH construction
• Combining sub-queries and joins

Analytics functions

• OVER clause
• Partition Clause
• Windowing Clause
• Rank, Lead, Lag, First, Last functions

Retrieving data from multiple tables (if time at end)

• Types of connectors
• The use NATURAL JOIN
• Aliases tables
• Joins in the WHERE clause
• INNER JOIN
• External Merge LEFT, RIGHT, FULL OUTER JOIN
• Cartesian product

Aggregate Functions (if time at end)

• Revisit Group By function and Having clause
• Group and Rollup
• Group and Cube