SQL Oracle – data administration on SQL server, maintenance and optimisation of SQL Oracle databases

Training process

Training needs analysis

If you have specific requirements regarding the training programme, we will carry out a training needs analysis for you. This will guide us on which aspects of the programme should receive greater emphasis, so that the training programme meets your specific needs.

What will you gain?

Confident Oracle administration - You will learn how to manage Oracle database structures in practice, including schemas, tablespaces, segments, and files, so you can support production environments with more confidence.

Better memory and process control - You will understand SGA, PGA, and background processes, making it easier to spot performance pressure points and choose settings that improve Oracle instance stability.

Efficient space management - You will learn how to control disk usage and handle tablespaces, undo, and temporary segments, helping you reduce downtime and avoid issues caused by running out of space.

Safer recovery of changes - You will work with Oracle Flashback and rollback-related mechanisms, so you can recover data faster after incorrect operations and reduce the risk of costly admin mistakes.

Query plan analysis - You will learn to read EXPLAIN PLAN, DBMS_XPLAN, and Autotrace output, allowing you to assess SQL execution paths more accurately and find inefficient operations faster.

Stronger performance tuning - You will understand how statistics, histograms, indexes, and join methods affect execution time, so you can remove bottlenecks more effectively and speed up database workloads.

Smarter storage design - You will explore ASM, OFA, and the roles of SYSTEM, SYSAUX, TEMP, and UNDO tablespaces, enabling you to organize data storage architecture using solid Oracle practices.

Stronger data protection - You will master roles, privileges, profiles, access control, and features such as VPD and Label Security, so you can protect data better and monitor user and schema activity.

Training programme

1. Discussion of basic issues related to Oracle SQL database administration

2. Data storage structures

table areas,
blocks, segments and tablespaces,
schemas and their relationship with databases,
logical database structures (views, profiles, tables, sequences),
physical data structures (data files and logs and copies).

3. Memory architecture during installation

PGA and SGA areas,
executable code and background processes,
creation and parameters of databases,
SGA memory management.

4. Discussion of the management of tablespaces and tables in Oracle SQL

Optimal Flexible Architecture,
SYSTEM, SYSAUX, TEMP, UNDOTBS1, USERS, EXAMPLE table spaces,
management and Segments,
management of the physical structure of databases. Automatic Storage Management architecture.

5. Presentation of the principles of effective disk space management in Oracle SQL databases

administration of free space,
manipulation of temporary segments and undo space,
local administration of tablespaces,
multifile tablespaces,
Segment Advisor, Undo Advisor, Automatic Workload Repository,
rolling back changes.

6. Discussion of Oracle Flashback tools

7. Detailed explanation of how Oracle SQL generates and executes query plans

methods of obtaining a hypothetical and actual query plan (EXPLAIN PLAN, DBMS_XPLAN, SQLPlus Autotrace,
indication of the order of steps in the algorithm,
analysis of information from the plan tree,
adaptive solutions.

8. Overview of session values and Oracle SQL installation configurations

hints, query plan baselines, query plan management.

9. Discussion of the importance of statistics in Oracle SQL

performance versus statistics and histograms,
statistics acquisition strategy,
estimation and calculation of statistics,
identification of bottlenecks,
monitoring changes and statistics management tools,
system and adaptive statistics.

10. Presentation of performance optimization techniques in Oracle SQL

optimization through physical separation of hot tables,
optimization through proper space management,
data compression,
read optimization through indexes (ROWID, BTREE structure and FULL SCAN, DLM operations, types of reads UNIQUE, RANGE, SKIP, FULL, FAST FULL, MIN/MAX, NULL value, reverse key indexes, IOT, bitmap indexes),
management of the sorting process,
administration of joins and subqueries - join methods: MERGE, HASH, NESTED LOOP,
performance of PL/SQL code.

11. Discussion of various methods of securing data in Oracle SQL

user privileges and roles,
access to databases and their control,
virtual private databases,
profile monitoring,
the Label Security tool,
monitoring statements, privileges, and schema objects.

What are the prerequisites for participating in the training?

SQL basics - You should be comfortable writing simple SQL queries, using SELECT, WHERE, JOIN, and ORDER BY, and understand what tables, views, and basic data relationships are.

Oracle SQL familiarity - You should know the basic parts of the Oracle SQL environment and understand the difference between a database, schema, table, and user to follow admin topics more easily.

Console and tool usage - You should have experience working with a database client such as SQL Developer or SQL*Plus, so you can run commands efficiently and review results during hands-on exercises.

Database fundamentals - You should understand how a relational database works, including data storage and query execution, so you can move more easily into administration and performance topics.