I bet you are used to relational data structures that you query using SQL. And so do I. And there is nothing in terms of data that a good SQL query cannot answer. OK, the query can become quite long – with inline expressions and multiple joins – but it […]
Database
Oracle database
Live Handson Environment for Modern Oracle Database Programming
My fellow Oracle ACE Directors and former AMIS-colleagues Patrick Barel and Alex Nuijten have co-authored an impressive book: The book discusses many of the wonders of SQL and PL/SQL – the well and lesser known things you can (and should) do when programming in and against the Oracle Database. Part […]
Oracle Database 23c–Fine Grained Locking–Lock Free Reservations
This should surprise if not baffle you. Session 1 inserts a record and commits it. Session 1 performs an update on this record. And does not yet commit its transaction. Session 2 tries to update that same record …. and is able to! It does not run into a lock. […]
Get going with Oracle Database 23c Free
A new major Oracle Database release is a special occasion. I joined Oracle in May 1994 just after the Oracle7 release (December 1993) although I worked with Oracle6 for a long time after that. I remember the launch of Oracle 8 (with OO support!) in 1996 and the excitement of […]
Unified Auditing not possible for Oracle Database XE (Express Edition)
I recently tried to get Unified Auditing going for Oracle Database XE. I could not really find instructions – not even through ChatGPT – and finally reached out to Gerald Venzl, the community champion at Oracle who is one for the key people behind Oracle Database XE. I learned from […]
Oracle Database 23c–new feature highlights (release February 2023?)
Based on the fascinating Twitter thread by Philipp Salvisberg covering the presentation by Gerald Venzl at DOAG 2022 I would like to mention a few of the features that apparently will be part of Oracle Database 23c – the new Long Term Support release (succeeding 19c) that has a beta […]
Migrating Oracle Enterprise Edition database To Standard Edition using Transportable Tablespaces
Introduction Oracle database Standard Edition (SE) has the main features of a RDBMS database and cost significantly less than Oracle Enterprise Edition (EE). Migrating a database from Oracle Enterprise Edition to Standard Edition can be a consideration ifthe Enterprise Edition options or features are not used (or not required) in […]
Delay in establishing RAC Database connections after upgrading from Oracle 11g.
After upgrading to Oracle 12c, 19c, database connections take a long time (10 sec or more), before the connections are established. The connection time was less than 0.5 sec in 11g. This is caused by a setting in the used Oracle Clusterware/Grid Infrastructure service names for the database. The AQ […]
Pseudo Change Timestamp–Finding recently changed table rows using Flashback Version Query to provide a surrogate last_changed column
My challenge: replicate changes from database A to database B, once per day. The tables involved are pretty big (100M records). I really would not want to process them all into database B. However, the tables do not have a LAST_DATE_MODIFIED column that has a timestamp that is updated with […]
Quick start with free managed PostgreSQL database on ElephantSQL
This article shows you can quickly get started with a free, managed PostgreSQL database with a table full of country data (using ElephantSQL). ElephantSQL is a hosted, managed PostgreSQL service from the team that also provides CloudKarafka and several other nice services. It is a breeze to get going. The […]
Steampipe–analyze data from cloud, file, platform, IaC using SQL queries
In our daily work we are dealing with data from many sources. Data in CSV files, from Cloud APIs, in mail servers, configuration files, Terraform plans, in logging systems, source code repositories and many more. Different formats, access methods, tools. And retrieving data from one such source can be challenging […]
Brief: What is Oracle Wallet?
An Oracle Wallet is an encrypted file that contains database credentials – username and password – for connecting to an Oracle Database. This avoids writing hard code user passwords in a shell script or in an application database configuration file. A wallet can also contain certificates – for example for […]
Connecting Go application to Oracle Database–On Prem and Autonomous , with and without Oracle Client libraries
I have been struggling a bit with this one: how to connect from a Go application to an Oracle Database – both a locally running database as well as an Autonomous Database Instance running on Oracle Cloud. Ideally, my application does not require Oracle Client Libraries to be installed in […]
Oracle Database 21c XE, setting up a demo environment using Vagrant and Oracle VirtualBox (part 4)
I wanted to set up a demo environment with Apache Kafka (an open-source distributed event streaming platform) and an Oracle Database, all within containers. For my purpose I opted for Oracle Database XE. [https://kafka.apache.org/] As I did before, on my Windows laptop, I wanted to use a demo environment within […]
Oracle Database 21c XE, setting up a demo environment using Vagrant and Oracle VirtualBox (part 3)
I wanted to set up a demo environment with Apache Kafka (an open-source distributed event streaming platform) and an Oracle Database, all within containers. For my purpose I opted for Oracle Database XE. [https://kafka.apache.org/] As I did before, on my Windows laptop, I wanted to use a demo environment within […]
Oracle Database 21c XE, setting up a demo environment using Vagrant and Oracle VirtualBox (part 2)
I wanted to set up a demo environment with Apache Kafka (an open-source distributed event streaming platform) and an Oracle Database, all within containers. For my purpose I opted for Oracle Database XE. [https://kafka.apache.org/] As I did before, on my Windows laptop, I wanted to use a demo environment within […]
Simple queries against Oracle Cloud NoSQL Database
I have been struggling in my first simple steps with querying data in a NoSQL Database table on Oracle Cloud. Creating the table is easy enough – either in the console or through Terraform or the CLI. However, the data that my Node application creates in the table seemed very […]
Oracle Database 21c XE, setting up a demo environment using Vagrant and Oracle VirtualBox (part 1)
I wanted to set up a demo environment with Apache Kafka (an open-source distributed event streaming platform) and an Oracle Database, all within containers. For my purpose I opted for Oracle Database XE. [https://kafka.apache.org/] As I did before, on my Windows laptop, I wanted to use a demo environment within […]
“You have to give a little… scripting for 12c+”
or Publishing a cleanup script for Oracle 12c+ At the moment I’m just for fun collecting Linux scripts from my direct colleagues. And while I was busy to think up a wiki page for internal use, for a moment I felt like Uncle Scrooge, gathering riches from other minds. Which, […]
Preparation for migrating data to Oracle Virtual Private Database
preparation for migrating data to Oracle Virtual Private Database Introduction Recently I was part of a team involved in the preparation of migration data belonging to multiple business units into a single Oracle 19c database with Virtual Private Database (VPD). The VPD solution is used for the virtual separation […]
Executing JavaScript from PL/SQL in Oracle Database 21c with Multi Language Engine
Oracle Database Release 7 – end of 1993 – introduced PL/SQL as language for Stored Procedures, Functions and Triggers. This was just before I joined Oracle Corporation, in May 1994. I have made extensive use of PL/SQL. With Oracle 8i we saw the advent of the Embedded JVM that made […]
How to build [a CI/CD pipeline for] an Oracle Database application–kicking off a six part series
Build is an overloaded term in the world of software engineering. Building software used to be mainly a fancy way of saying “programming”. Now after the construction of the code, the actual build process is kicked off, that takes the work from the individual programmer and turns it into something […]
How to unit test a SQL query?
A simple challenge: I am working on a SQL query. Not a trivial query. I am trying various approaches. I believe I may have found a solution that works. That produces the correct solution. How can I be sure? How can I try to further refine the query while maintaining […]
How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (1)
What’s in a name? A title is important and I hope that it describes well what I do want to share with you in this series of articles. It is not so much about how to use the back-end part (Oracle Database) or the front-end (Oracle APEX, Java, Node, React […]
How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (6)
Last time in “How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (5)”, I told you about Git, Subversion, Maven and Flyway. In this final article, I will discuss the following tools & methods: Oracle SQL Developer, utPLSQL, SonarQube, Perl, Ant and DevOps. Oracle […]
How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (5)
Last time in “How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (4)”, I told you about the Oracle SQL Developer Data Modeler. This time I will discuss the following tools: Git, Subversion, Maven and Flyway. Flyway The first tool I would like to […]
How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (4)
Last time in “How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (3)”, I told you about the Oracle Database and Oracle APEX. This time I will discuss Oracle SQL Developer Data Modeler. Oracle SQL Developer Data Modeler A book I can recommend is Oracle […]
How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (3)
Last time in “How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (2)”, I did show you the database structure. This time I will elaborate on the base tools, the Oracle Database and Oracle APEX. Oracle Database How to use it? I can tell […]
How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (2)
Last time in “How to build an Oracle Database application (that supports APEX, Java, React or any other front-end) (1)”, I gave you an introduction. This time I will elaborate on the database structure. Project folder layout The following top level directories may exist for every database application project: Directory Description […]
First code and cli free steps with Oracle Database 21c in Oracle Cloud
Oracle Database 21c has been released – for now only in the cloud. Starting to explore – that is what we call playing with it – it easy and free of any charge in the OCI Always Free Tier that includes two Oracle Database instances that now can be 21c […]
How to dynamically Schedule EM Blackouts after PatchTuesday
In the organization I’m currently working for, the OS-patch schedule depends on PatchTuesday (PT) just as Microsoft, Adobe or Oracle are using. And PatchTuesday is, as you all know, the Second Tuesday of each month. But this is a schedule you can not set in the Oracle Enterprise Manager Scheduler. […]
AWS Shop example: SNS duplicate messages
Introduction Our shop example [1] is now in production, wohoo!!! When you are using our example program in production, you might see that some sales are updated multiple times in the database. This will not happen very often, but you want your sales to be processed once, not twice. In […]
A Quick how-to RMAN backup to OCI Object Storage
This is a quick how-to article on setting up RMAN to backup to Object Storage on OCI. This is applicable when you build your on Oracle database on an OCI instance, or when you have a database on-premise you wish to backup to the (OCI) cloud.
Migrating databases using Dataguard, some pitfalls and observations
My customer needed to migrate to a new set of datacenters, and while other services were able to “lift and shift”, the Oracle RAC database servers had to be rebuilt in the new datacenter because the underlying platform wasn’t able to migrate the shared disks. Fortunately the environment was already […]
AWS Shop: DynamoDB, the AWS NoSQL database
Introduction The ultimate goal of our shop application [1] is to update the AMIS-shop table in the DynamoDB service. In this blog, I will tell a little bit more about DynamoDB. DynamoDB is the NoSQL solution of AWS. The way we use this table in our example is straightforward: the […]
Migrating an old (10.2.0.4) database to Oracle Cloud with minimal downtime
Unlike most of our posts this post will contain almost no code or examples. But it hopefully will help somebody who ends up to be in the same situation we landed on: migrating data from very old versions to a new environment. Recently we were tasked with the migration of […]
Performance of relational database drivers. R2DBC vs JDBC
R2DBC provides non-blocking reactive APIs to relational database programmers in Java. It is an open specification, similar to JDBC. JDBC however uses a thread per connection while R2DBC can handle more connections using less threads (and thus potentially use less memory). This could also mean threads are available to do other […]
Getting started with NoSQL Database Service on Oracle Cloud Infrastructure
This morning I discovered a new entry in the menu on my Oracle Cloud Infrastructure Tenancy (on Ashburn, USA): the NoSQL Database service had been added overnight. The service offers a key-(schema-less) document store. It also supports ACID transactions, SQL on JSON documents, joins across tables, structured tables and if […]
Oracle Database – more value for money and more value for free
Oracle is not known for low prices or free products. However, when it comes to the Oracle Database there are some developments that might be of interest to you. Either when your organization has existing Oracle Database licenses – or when you do not. In short: three powerful and until […]
pgAdmin in Docker: Provisioning connections and passwords
pgAdmin is a popular open source and feature rich administration and development platform for PostgreSQL. When provisioning Postgres database environments using containers, it is not unusual to also provision a pgAdmin container. The pgAdmin image provided on Docker Hub does not contain any server connection details. When your pgAdmin container […]