POUG 2021

Changing "colors" happens in IT frequently and is driven by various reasons. In this session we will talk about using production database running on IBM POWER/AIX on-premises platform as a source for building multiple testing environments running in Microsoft Azure (x86/Linux). All fully automated using Azure DevOps Pipelines and in accordance with security standards imposed on banking sector.

Unit tests are useful. Unit tests are essential. All of us are doing the UT, aren’t we? We try to do our best but in real life, UTs are expensive and consume a lot of time. When we finally find a bug - it turns out that the bug is in the test itself. And the deadline is coming… Can we do the UT easier, less expensive, and more effective? YES, WE CAN! I would like to show you how to design your application, write your PL/SQL code and prepare test cases in a way that will help to build Unit Tests and perform them in a fast, efficient, and exciting way!

Oracle database 21c came with 211 new features and 3 major ones are directly related to tables and indexes in the database: Blockchain Tables, Automatic Index Optimization and Automatic Zone Maps. The presentation covers all these 3 in detail with several examples including also Data Pump new features and how to upgrade to 21c.

Oracle Fleet Patching and Provisioning is a robust, reliable, automated and fleet capable solution that orchestrates the install, patching and upgrade of Oracle Grid Infrastructure and Oracle Databases. How does it work? What are the building blocks? How does the communication between client and server happen? How are the software homes transferred and stored? How are databases patched? In this session I will answer all these questions and give you a clear understanding of Fleet Patching and Provisioning architecture.

Performance problems come in many varieties. Most can be solved by the Wait Interface, but sometimes that’s not enough. Enter session counters to the rescue. And in rare occasions even those don’t help you have to use O/S tracing and profiling tools. The talk introduces all of these in depth.

Oracle database 18c introduces Polymorphic Table Functions. This session will tell you about Polymorphism. How can you apply this in pre-18c databases? I will show you what problem and the solution when your data or structure changes. I will also show you an example of a Polymorphic Table Function and how this can help protect you from changing table structures and how you can apply the same code to different table structures without changing or even recompiling the code. After this session you will understand when to use which technique.

Organizations using modern approaches to code development are using CI/CD pipeline to speed up and automate all processes. There are a lot of solutions to help developers to manage code, changes or building an artifact. It is not that easy with managing databases and more specifically data. Using containers, it’s pretty easy to create a new empty database but it’s not a solution for data especially if development or bug fixing requires real production or masked data. This presentation is trying to address this issue and show what can be done to resolve this problem. The following techniques will be presented: - Cloning / snapshot-ing using Oracle Containers Databases - Building dockers with some data - Snapshots on file system / storage / 3rd party products

Moving from Oracle to PostgreSQL creates some wonderful opportunities and there are countless features you will find useful and elegang on the PostgreSQL side. This talk covers the most exciting and powerful things PostgreSQL has to offer from an Oracle perspective

With the release 19.2 of Oracle REST Data Services Oracle introduced functionalities for DBAs to operate a database via a REST API. The main focus of this presentation is on the architecture and what a DBA needs to know to run this API safely. It enables automation processes to directly communicate with the database without interaction with the server but there are some questions to be answered before we implement ORDS. Who can use this API? How do they authenticate? Where should the Webserver be placed? Is it safe?

Over the last few years we’ve been automating parts of the regular security patching of a bunch of Fusion Middleware installations. The code covers ~80% of the regular tasks. I’ll cover the past experiences, some history, and issues faced during development, and actual use of the code for patching.

Microservices are a useful concept, and the supposed advantages are often cited. But there are a few things to consider when microservices need to interact with a “traditional” database or RDBMS.

In some cases, you can even have your RDBMS (Oracle, PostgreSQL or mySQL) take up the role of a microservice, and achieve remarkable efficiency.

We will provide a few use-cases and examples where it may be interesting to use the DB as the main component of the microservice.

After the presentation, the audience will have a better idea of the Advantages, and hopefully some ideas to to avoid the Disadvantages of Microservices. And in particular use cases, may choose to use the Database as the base for a microservice.

How do you take your database schema and your data to the next release? Do you herd a collection of delta scripts? Do you know exactly which version of which object was deployed in your different environments at a certain point in time?
Find out how the open-source tool Flyway can help you manage your database schema changes in an agile environment and how easy it is to get started.
After several years of using Flyway Community Edition in combination with an Oracle database I would like to share my insights about
- How to write scripts for Oracle database in Java, DDL and PL/SQL
- How to organise your scripts
- Potential pitfalls and workarounds
- Good and not-so-good practices
- A short comparison with Liquibase
Flyway integrates with various build frameworks like Ant, Maven or Gradle but for demo purposes we will stick with the command line.
This session will give you the basic knowledge and a collection of sample scripts to start right away.
I promise – once you’ve started using a database deployment tool you'll never want to go back!

Low-code platforms such as Oracle APEX were invented to deliver value to business as fast as possible.
However, delivering something fast often causes a lack of control in database changes and lower code quality.
We will show You, how to automate deployment process (DevOps) saving more of Your time for development and tests instead of wasting it for preparing release scripts.

One of the frequent complaints developers receive from DBAs is about misconfigured connection pools and insane number of connections applications tend to allocate. This session explains basics about connection pooling, why they are used, and how to configure them for availability and performance.

Learn In-Database machine learning embedded in the Oracle Database . Through an example, to learn the process to develop the Machine Learning with Oracle machine learning for SQL and Oracle machine learning notebook environment. Learn new In-Database Machine Learning features in Oracle 21c.

The talk will give an introduction into basics of queueing theory, and how it affects latency and throughput. A straight overview from the impossibility of linear scalability to Amdahls law until the Universal Sacability Law is provided. By these tools and with reasonable measurements specific characteristics of any system can be described in a comparative way.

This presentation will focus on writing secure PL/SQL code. We will define and identify sql injection bugs along with other common coding bugs that can lead to a data breach. Once we have defined these bugs, we will go through a design and coding architecture that drastically improves your infosec posture.

The Internet of Things (IoT) has deep use cases - energy grids, communications, policing, security, and manufacturing. I’ll show how to use Oracle 19c’s Fast Ingest and Fast Lookup features to load IoT data from “edge” sources to take immediate advantage of that information in nearly real time.

We have missed human contact so much, I believe it’s more important to sort that out than talk about some silly little computers.

I will therefore use my cuddly frame to hug any and all audience members either individually or using a more advanced option of clusters of 3, 4 or even 5 people.

A look inside an Oracle block, the fundamental unit of the Oracle Database. We’ll see how a block is constructed, and how the components inside the block are used by the database to provide performance and concurrency. Unlike a typical internals deep-dive, this session has a twist - this deep dive is aimed at the novice! Whereas most deep dives are aimed at the very experienced professional in order to resolve the most complicated problems, we’ll be more focussed on how these internals related back to the fundamental operations of the database, such as how queries work , storage optimization, and application locking.

Have you every heard from Oracle Support “It will be fixed in 21c or 23c”? And wondered if you should upgrade now, and how you should upgrade when the release is not there yet?

In this talk we will give rare insights into how Oracle Database Development works.

We will explain why it takes more than a few minutes to complete a regression test suite, why you don’t get one-off patches on Windows - and why you need a backport to have “this” issue fixed on 19.11.0.

We are fully aware that you will need some beer to relax - and that everybody has a story to tell. Our focus will be to shed some light on the black box “Oracle Database Development” to get you a better understanding why things work as they work - and why miracles happen not every day but sometimes take longer.

Tired of the spaghetti architecture created by hundreds applications and ETL flows dependencies making every database table change a matter of 100 people meeting? Understand how you can decouple and integrate better your systems with Apache Kafka and Kafka Connect!

PLSQL is a very easy to learn programming language. Deceivingly easy. Recently I saw what happens when you learn the basics and think “this is so simple, I know enough”. I was working for a client (no, I will not reveal their name) where the databases that I had to work with were built by people with obviously just a very basic understanding of the Oracle database and especially PLSQL. Many times a day I was shaking my head over the solutions that had been invented. I would like to take you along on a journey through some of the things that I saw there. Mainly for fun, because I hope you will already know that these things are not the way to do it. If you see something and think “that’s how I would have done it”, don’t be ashamed. None of us knows everything. We all make mistakes. We all do stupid things. But, if you think “I could have done that” with all of the examples …… I know a client for you :D

It’s really obvious what the database writer does; it writes changed blocks from the buffer cache. However the mechanics are not, hardly and/or incompletely documented, such as checkpointing. This talk is an investigation into these mechanics.

At the end of the talk, it should be clear how easy it is nowadays to analyze geospatial data, whether it be addresses, coordinates, or other location information, either using plain SQL or using Spatial Studio as a user interface for the non-expert.

Lockdown Profile, PDB_OS_CREDENTIALS and other measures to enhance security and isolation of multitenant databases are available since Oracle 12c. Unfortunately only a part of the desired measures can be technically implemented. With the latest release of Oracle 21c a new features called DB Nest has been introduced. DB Nest introduced an other approach to security in PDBs. In this presentation we will discuss the new approach and its possibilities to increase database security of PDBs. The presentation will be completed by corresponding examples and live demos.

When I recently had the opportunity to review a PL/SQL procedure, I didn’t know whether to laugh or cry. Scrolling through the source code, I noticed numerous stylistic blunders, from bad programming style to elementary errors in the structure of the procedure. The good thing about it: So many typical errors on relatively few lines of code are wonderfully suitable to show by means of a concrete example what has to be considered when programming with PL/SQL. In this session we will review the procedure together and simplify it to make it more readable, faster and more elegant. Various tips on good programming style and efficient code will be given, which can also be used in other projects.

Knowledge Discovery looks to uncover hidden patterns in your data, and can be a difficult task. Lots of tools have been built to help us discover hidden patterns. Typically, statistically analysis and visualizations, combined with our domain knowledge, is the most common. Building upon these there are various machines learning and pattern discovery algorithms. They delve deeper into the data, identifying links and associations between the data elements.

This presentation will examine some of these options and will demonstrate how, combining the insights from Machine Learning with the pattern discovery of Graph Analysis, you can attain a greater understanding of your data and the hidden patterns. All Roads or Paths do lead to Knowledge Discovery.

Six Degrees of Kevin Bacon is about linking actors by movies where they have worked together. The Bacon Number of an actor is the number of links in the shortest path from the actor to Kevin Bacon.

Calculation of the Bacon number is an interesting exercise (like given as a lab at Oberlin College) and of course we can do it in Oracle. But simple implementations might easily become excessively resource consuming for the database - we might need to do things like early pruning to get acceptable performance on larger datasets.

This talk investigates different Bacon calculation methods with SQL and PL/SQL as well as graph methods (remember that Graph option is now no-cost in supported database versions.) These methods can be useful for other real sets of data besides movie data.

Some of the attack methods I plan to show in this session: - Public Synonym escalation - Escaping to OS - Using Java Source / External Tables / UTL_FILE - Break the DB Link password - Connect on another remote server - Brute Force USER$ password - Rootkit on Oracle Listener - Ransomware using own DB Wallet - Exploring sudo

Everything runs on Linux, including databases and cloud services. And the performance metrics usually show %CPU, or load average, I/O wait, free memory,… which are easy to show but very difficult to interpret. We will run a workload, look at “top” together and see what it tells us… or not.

People go to cloud, look at CloudWatch or other performance metric charts, and scale up and scale out their instances (and their bill) without understanding if they are really in CPU, IO or memory pressure. In this full demo session we will look together at a workload running and see how the commonly used metrics are often not understood and sometimes meaningless. A good occasion to go back to the basics: process states, virtual memory and CPU threads

AJAX plays an important role when developing APEX Applications. You might not be aware of the possibilities and pitfalls - even when you use just the standard approach. And if “standard” isn’t good enough for you : you can always code it by hand! Attend this session to know all about AJAX!

Oracle RAC is one of the key differentiators in the Database world. It is critical to understand how this works below the covers so DBAs can help troubleshoot problems in their environments. In the new world with SSDs and NVMe’s, Cache fusion is adapting to these changes. We will cover these changes and provide internals on measuring these new capabilities. This will help DBAs to architect their solutions to benefit from Oracle RAC

On-site only The V function has been around APEX as long as … let’s say very long and that’s a long time. The V function allows you to reference item values from session state in your SQL statements. When calling V there would always be a price to pay. With recent releases of the database there is a new way to do something similar; SQL Macro. In this session I will give you an overview of this new functionality and how you can use it in your APEX applications.

You might think your conscious self is doing all the heavy lifting when it comes to writing code (go neocortex!). But the reality is that your unconscious is very busily sorting out issues and solving problems, which it then delivers via fingertips to your editor. In this session, Steven shows how listening to your inner voices (some of them, anyway) can help you write better PL/SQL code - and be more productive.

Transformations - the things Oracle does to rewrite your query in a form it like, and how you can recognise the choices it has made when you review the execution plans. In this presentations we will review examples of the transformations available to deal with such things as existence, aggregates, non-mergeable views, union views, partitioned tables and partial indexes.

Running production-level systems in Oracle Cloud requires performing some standard maintenance tasks on the databases. As most DBAs know, patching is one of the job’s necessary evils. While many customers have built their own scripts or automation around database patching, moving to OCI creates a new set of patching procedures. The first time a quarterly patch is issued, there are a million questions:

Should DBAs use the console or the API?
How does data guard fit in the mix?
What about oneoff patches?
How do we diagnose failures?
This session will introduce DBAs to what they need to know in order to understand patching and upgrading in the OCI universe.