EBR – Part 6: Editionable and Non-Editionable, Editioned and Non-Editioned

This is part 6 of a post series about EBR.
In part 1 we created the baseline model and code – a table (PEOPLE) and two packages (PEOPLE_DL and APP_MGR).
In part 2 we saw that even a simple change – a package body compilation – can be dangerous in a busy system.
In part 3 we learned about editions and how they can be used for solving the problems described in part 2, so (more...)

Fedora User Manager

As one of my students pointed out, Fedora doesn’t install a GUI user management tool installed by default. That’s true. It’s easy to install. You can install it as the root user, like:

yum install -y system-config-users

It should generate a screen console output like this when successfully installed:

Loaded plugins: langpacks, refresh-packagekit
http://yum.postgresql.org/9.3/fedora/fedora-20-x86_64/repodata/repomd.xml: [Errno 14] HTTP Error 404 - Not Found
Trying other mirror.
Resolving Dependencies
--> Running transaction check

Logger Snippets for VS Code

Snippets are a common feature in text editors, allowing you to essentially paste in a snippet of code from some library somewhere.

Snippets appear for selection as you start typing, and the editor will paste in the entire content of the snippet.

Snippets in VS Code

VS code snippets also have the ability to focus the cursor, ready for typing.

It's very closely related to code completion concepts, applied in VS Code with Intellisense. Vito (more...)

On Switching Code Editors

Ever since I joined the industry, my preferred text editor was TextPad.
It's a solid tool, similar to Notepad++. I probably underutilise it, but I started to see some people to interesting things with more modern editors.

Day-to-day, I work with two monitors, so I've been trialling software literally side-by-side for a while.

With some help from my nearby colleague, I tried a little Sublime (freemium), and that peaked my interest when contemplating the jump. (more...)

COLLECT INTO inside a Cursor Definition?

I have been programming in PL/SQL since 1993, more or less continuously (I’ve just realized that it’s exactly half of my life until now), so I know the PL/SQL syntax quite well.
So when I saw the following piece of code a few days ago, I was willing to bet it would not compile:

    v_number_array sys.odcinumberlist;
    for v_rec in (select rownum as num
                  bulk   collect
                  into   v_number_array
                  from   dual
                  connect by level < = 5)
    end loop;


Fedora Install unixODBC

Encountered a problem while running the RODBC library from the R prompt as the root user, as follows:

> install.packages('RODBC')

It failed with the following library dependency:

checking for unistd.h... yes
checking sql.h usability... no
checking sql.h presence... no
checking for sql.h... no
checking sqlext.h usability... no
checking sqlext.h presence... no
checking for sqlext.h... no
configure: error: "ODBC headers sql.h and sqlext.h not found"
ERROR:  (more...)

Excessive Locking when Dropping a Table in 11g

I tried to drop a table today and failed due to “ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired”.
That was weird because I knew that nobody had been using this table for months, and that the table had no enabled foreign keys.
A quick investigation revealed the cause – the DROP TABLE operation tried to lock another table (in the quite aggressive “Share” mode) that was referenced by a (more...)

Keine Empfehlung mehr für System Statistics

Die Einführung der "System Statistics" liegt schon einige Jahre zurück, aber die ihnen zugrunde liegende Idee einer Kalibrierung der verfügbaren CPU- und I/O-Ressourcen fand ich damals durchaus einleuchtend. Tatsächlich eingesetzt habe ich sie selten - und das scheint inzwischen kaum noch jemand zu tun. Und zukünftig wird es wohl noch seltener vorkommen, nachdem Maria Colgan und Nigel Bayliss davon abraten. Bei Frau Colgan liest man:
Don’t gather system statistics unless you are in a pure (more...)

A Single Query with Many Filter Combinations – Part 3

In a recent post I suggested a way to write a single SQL query that filters a table by one or more of several columns.
Here is the query from that post:

select * from employees where rowid in (
  select rid from (
    select rowid rid from employees where department_id = :department_id
    union all
    select rowid rid from employees where job_id = :job_id
    union all
    select rowid rid from employees where manager_id = :manager_id

Data Warrior #HappyDance! Guess who joined @Snowflakedb

It going to be a big day at Snowflake. Two of my good friends are joining my team.

UKOUG Northern Technology Summit 2018

The UKOUG has run something called the Northern Server Day for several years. Northern because they were held in a northern part of England (but south of Scotland) and Server because the focus was the database server. Over the last couple of years the day has had several streams, covering Database, High Availability and Engineered Systems. So primarily a day for DBAs and their ilk.

This year the event has expanded to let in the (more...)

A Single Query with Many Filter Combinations – Part 2

In the previous post I suggested a way to write a single SQL query that filters the EMPLOYEES table (as an example) by one or more of the following columns: DEPARTMENT_ID, JOB_ID, MANAGER_ID and LAST_NAME.

Here is the query from the previous post:

select * from employees where rowid in (
  select rid from (
    select rowid rid from employees where department_id = :department_id
    union all
    select rowid rid from employees where job_id  (more...)

Fedora R Install

I’ve started building the new image for the database courses. This one needs to include Oracle, MySQL, Cassandra, Hive, and MongoDB databases; and include examples for C, C++, Java, Perl, PHP, Python, R programming languages.

Installing R was a surprise when I saw how many packages there are for it. It’s a standard yum command from the repository, but it will install 256 packages. The command is:

yum install -y R

Once you install it, (more...)

A Single Query with Many Filter Combinations

Let’s assume the EMPLOYEES table (from the HR schema) contains many records, and we want to write an (efficient) SQL query that filters it by one or more of the following columns: DEPARTMENT_ID, JOB_ID, MANAGER_ID and LAST_NAME.
For example:

  • in one execution we may want to get all the employees with DEPARTMENT_ID = 80
  • in a second execution all the employees that their LAST_NAME is King
  • in a third execution all the employees (more...)

Histogramm-Probleme mit EXP/IMP in Oracle 12

Jonathan Lewis weist darauf hin, dass die historischen Utilities exp und imp mit 12c neue Probleme bekommen haben: der Code der Tools wurde nicht dahingehend angepasst, dass sie mit den neuen Histogrammtypen "Hybrid" und "Top-N" zurecht kämen. Wenn man aus einem Quellsystem exportiert, in dem bereits ein "Hyrid Histogram" exisitiert, so wird dieses nach dem Import im Zielsystem zum historischen "Frequency Histogram" - das noch nie viel getaugt hat. Grundsätzlich sollte natürlich in diesen Tagen (more...)

Potentielle Performance-Probleme bei match_recognize

Jonathan Lewis hat vor einiger Zeit zwei Artikel zum match_recognize geschrieben, die auf potentielle Probleme hinweisen, die das Feature mit sich bringt, wenn man die Match-Bedingung nicht mit ausreichender Sorgfalt definiert. Leider gehört match_recognize noch immer nicht zu meinem SQL-Repertoire (jedenfalls nicht ohne dass ich mir irgendwo ein funktionierendes Beispiel ausborge und darin dann anpasse, bis es meinen Anforderungen entspricht), aber die Hinweise finde ich hier möglicherweise besser wieder als beim Herrn Lewis:

Optimizing the Performance & Scalability of Java Applications that use an RDBMS


There is an abundant literature on Java performance (books, articles, blogs, websites, and so on); a Google search returns more than 5 millions hits. To name a few, the Effective Java programming language guide, Java Performance the definitive guide, Java performance tuning newsletter and associated http://www.javaperformancetuning.com website. This is not the purpose of this post.

The goal of this post is to revisit the known best practices for speeding up (more...)

Deploying a Kubernetes cluster with Vagrant on Virtual Box

Oracle has just included Kubernetes support for its VirtualBox Vagrant GitHub repository. With that it’s now easier than ever to get a Kubernetes cluster up and running inside VMs. If you have not come across Vagrant yet, it’s a great tool by HashiCorp for “Development Environments Made Easy“. tl;dr Install VirtualBox Install Vagrant Clone the GitHub … Continue reading "Deploying a Kubernetes cluster with Vagrant on Virtual Box"

Announcing the results of the Oracle Dev Gym SQL Championship for 2017

A huge round of applause to the top players in the championship:

1st Place: mentzel.iudith of Israel

2nd Place: Pavel Zeman of Czech Republic

3rd Place: Andrey Zaytsev of Russia

Especially noteworthy is the accomplishment of Iudith Mentzel, who also won the DB Design Championship and has been a very regular Dev Gym participant for years. Congratulations, Iudith!

Also congratulations to everyone who played in the championship - it's quite an accomplishment to have (more...)

Local Partitioned Indexes mit postgres 11

Daniel Westermann weist darauf hin, das postgres 11 erweiterte Optionen für die Indizierung partitionierter Tabellen liefern. Während in postgres 10 Indizes noch auf Partitionsebene erzeugt werden mussten, kann man sie jetzt für die partitionierte Tabelle definieren, was dazu führt, dass sie automatisch in den untergeordneten Partitionstabellen erzeugt werden. Vielleicht noch interessanter ist die Möglichkeit, primary keys auf partitionierten Tabellen zu erzeugen. Insgesamt ist deutlich zu erkennen, dass die Partitionierung in postgres allmählich ihre Kinderkrankheiten hinter (more...)