Oracle example schema for MySQL

Anyone who has worked with Oracle has encountered the Oracle “hr” schema in one way or another. The Oracle example schema provides a few simple tables with example data to test out some queries or learn SQL.

So when working with MySQL, I like to have the same schema as well. Luckily, Andrei Ciobanu feels the same way and he provides a wonderful adaptation of this sample schema for MySQL on his website: HR Schema for MySQL and Maria DB.

The SQL script for the schema can be found on GitHub: nomemory/hr-schema-mysql (or in my fork: simonkrenger/hr-schema-mysql).

Thank you Andrei!

Oracle Primary Key Sequence Performance

With Oracle 12c, Oracle introduced a new way to insert primary keys into tables: The GENERATED BY DEFAULT and DEFAULT keywords.

In this post, I want to compare the performance of different methods to insert a primary key into a table in Oracle. The comparison will also include older methods, such as using a trigger or specifying the primary key in the INSERT statement. These methods can also be used in Oracle versions earlier than 12c.

First of all, let’s look at the possible ways to insert the value of a sequence into a table:

Read the rest of this entry


One problem that occasionally pops up is that a DBA needs to recreate a user with a password that he does not know. This happened to me when I had to create a schema with the same password on a development database. After I set an initial password for the developer, he exclaimed that he wanted the same password that he had for the schema X, but he did not want to tell me the password (don’t ask here, that’s a completely other story).

One way to do this is to use ALTER USER ... IDENTIFIED BY VALUES. Using the excellent article on the ALTER USER commands from Laurent Schneider, we can generate a dynamic SQL query to set a password without knowing the password itself!

Here is the query to read the password from sys.user$ from Laurents article:

Read the rest of this entry

Oracle 12c: ORA-65090: operation only allowed in a container database

As many of my colleagues, I have been eager to try out the new Pluggable Database (PDB) feature of the newly released Oracle 12c Database. I installed the software, created the database using my default CREATE DATABASE statement I have lying around and then tried to create a pluggable database by issuing the following command:

SQL> create pluggable database kdb121p1 admin user simon identified by tiger file_name_convert = ('/pdbseed/','/kdb121p1/');
create pluggable database kdb121p1 admin user simon identified by tiger file_name_convert = ('/pdbseed/','/kdb121p1/')
ERROR at line 1:
ORA-65090: operation only allowed in a container database

It turns out a database explicitly needs to be created as a Container Database (CBD) in order to be able to create new PDBs!

Read the rest of this entry


Ok, so here is a problem that a developer brought up. I thought that this problem is quite interesting and also a bit confusing. Obviously, according to Oracle, this is not a bug – it’s a feature!

When issuing a CREATE MATERIALIZED VIEW statement for a different schema (as DBA), one might encounter the following error:

ERROR at line 1:
ORA-01031: insufficient privileges

For our setup let’s assume we have two users:

Read the rest of this entry

SQL*Plus on Debian

In this article, I will describe the steps necessary to install Oracle SQL*Plus on a Debian host. I am using Debian 6.0.5 and will install the “Instant Client” package from Oracle (version First, we will prepare the system for the installation, download the installation package, set all the necessary variables, start SQL*Plus and connect to an instance. So let’s get started…

Before we begin the package installation, we need to make sure the necessary prerequisite packages are installed. Oracle Instant Client requires the libaio package, so let’s install that:

Read the rest of this entry

Oracle STATSPACK Quick Reference

Since most of our databases are not licensed with the Oracle Enterprise Manager Diagnostic Pack, we cannot use AWR (Automatic Workload Repository) and ADDM (Automatic Database Diagnostic Monitor). So we have to use the good old Oracle STATSPACK.

The goal of this article is to provide a quick reference for installing and maintaining STATSPACK for an Oracle database. It is based on the excellent “STATSPACK Survival Guide” of Akadia AG. If you need more information on STATSPACK, please refer to the Survival Guide or the Oracle documentation, since this article is only a really quick summary on how to install, configure and use STATSPACK.

Read the rest of this entry

Reuse last query in SQL*Plus on Linux

Ok, here is a quick trick that I just found out about earlier this week. I am currently in an Oracle Database 11g: Administration 11 course and learn many new things about database administration.

One thing you will definitely encounter when working with Linux is that it is not possible to scroll back though your SQL*Plus history with the arrow keys. If you try to do so, the following happens:

SQL> select owner, count(1) from dba_tables group by owner;
OWNER COUNT(1) ------------------------------ ---------- [..] SYS 673 SIMON 88
7 rows selected.
SQL> ^[[A " - rest of line ignored. SQL> command " SQL>

Argh! Some kind of wild character sequence appears on the command line. A quick search shows that this is a common problem. One thing that regularly happens is that you enter a SELECT statement that returns too many rows. Because the terminal buffer only contains a certain number of rows, your query disappears and you have to retype it.

Read the rest of this entry

Difference between DBA_TABLES.num_rows and count(*)

One thing DBAs regularly do is to gather information on the schemas in the database for reporting. So we want to know how many rows there are in the tables of the schema “SIMON”. When doing so, one is tempted to query the DBA_TABLES view:

SQL> SELECT table_name, tablespace_name, num_rows
  2  FROM dba_tables WHERE owner='SIMON' ORDER BY num_rows DESC;

TABLE_NAME                     TABLESPACE_NAME                  NUM_ROWS
------------------------------ ------------------------------ ----------
HUGE_TABLE                     DZ_DATA01                         8227990

However, if we query the table directly, we get a different result:

COUNT(*) ---------- 8230310

Why is that so? When reading through the description of the ALL_TABLES view, one finds the following note:

Read the rest of this entry

Hello world

My name is Simon Krenger, I am a Technical Account Manager (TAM) at Red Hat. I advise our customers in using Kubernetes, Containers, Linux and Open Source.


  1. GitHub
  2. LinkedIn
  3. GitLab