How to do Unit Tests and Not go Crazy (and Bankrupt)rozwiń opis
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.
Cybertec: 9 PostgreSQL Features Even Oracle Fans Would Likerozwiń opis
Profiling and debugging problems on modern Linux - on premises and in the cloud rozwiń opis
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.
A Gentle Introduction to Polymorphic Table Functions rozwiń opis
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
Oracle Fleet Patching and Provisioning: Architecture and Internalsrozwiń opis
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.
All Road Lead to Knowledge Discovery, or Do They?rozwiń opis
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.
Patching with Ansible: What worked, What didn't work, and Whyrozwiń opis
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 and Databases, a Smart combinationrozwiń opis
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.
Version control for your database - How to Migrate your DB Schemas with Flyway rozwiń opis
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!
The long requested Data Guard Feature: Data Guard per PDBrozwiń opis
Can we use Data Guard on PDB level? How do you do PDB switchover? Before Oracle 21c it was not possible, or at least not very easy. During this deep dive session this new feature will be explored in detail: What is it? How does it work? An answer on these questions will be provided in a step-by-step live demo of the feature.
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.
In-Database Machine Learning with Enterprise Data and Enterprise Applicationsrozwiń opis
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.
I don’t want to miss a Thing 🎶 - Track Database Changes with Apache Kafkarozwiń opis
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!
The sky isn't the limit! Calculation & description of performance parameters for any given systemrozwiń opis
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.
Fast and Furious: Handling Edge Computing Data With Oracle 19c Fast Ingest and Fast Lookuprozwiń opis
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.
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.
Huh? It is fixed in Oracle 23c?? Seriously? - Or how Database Development at Oracle worksrozwiń opis
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.
Partitioning - Eat your Data Bit By Bitrozwiń opis
Oracle Partitioning is hardly talked about anymore, which is a great shame as it is such an incredibly useful feature - handling large data volumes without partitioning is like drinking a barrel of beer with no glasses. It can be done, but it’s messy.
However, as with so many other things, if you use partitioning wrong you will make things worse. Instead of making your database faster, easier to manage, and vastly quicker to load & delete data, you can end up making it a complete mess.
In this talk I explain how partitioning works, how to get the best out of it, and the most common mistakes people make.
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.
Back to basics: Using a spatial index to find the nearest pubrozwiń opis
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.
DB Nest 21c - PDB Security and Isolationrozwiń opis
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.
To Laugh or To Cry? Code Review of a PL/SQL Procedurerozwiń opis
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.
What a DBA should know about the Oracle REST APIrozwiń opis
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?
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.
Hacking Oracle Database - Live and Uncensored rozwiń opis
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
Linux load average and other silly metrics rozwiń opis
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
To V or not to V? or.. using SQL Macros in APEX?rozwiń opis
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.
Inserting a row, updating it repeatedly, shrinking the segment, deleting the row, all sound so simple: just stick it in the block and it’s done! But do you want to know what’s going on under the covers, how we find space for rows, and how we lay them down in the block? What is so important about having more than 255 columns in a table? Each of these involve finding space but how does Oracle look for space? How does it optimize space? How are rows migrated? How is free space used and why is it critical? How do you know when space or rows are fragmented? Why does it matter? And, what can you do about it? What about stats - are they useful here? This talk will answer those questions.
Transformations - things the optimizer does to your SQLrozwiń opis
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.
Upgrading and Patching Databases in Oracle Cloudrozwiń opis
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.
POUG is not only the official Oracle community – first of all it is the base of very active members, who are engaged in the group development both during the meetings and the preparations to them. Our message gets to the 400 people connected with databases – from the developers to the administrators and from the begginers to the experts with years of experience.
We would like to invite you to be a part of our event – it is a chance to show your brand during and before the meeting.