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

RHEL: Add DVD to YUM repository

When setting up a new server in a highly secured network, one does not always have access to the Red Hat Network to download packages for the installation of the server. Often, a local repository is provided later on in the setup process.

This leaves us with the problem of installing the necessary packages for the Oracle database. Luckily, we can use the DVD we used for the installation of the Operating System to get all required packages.

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

logrotate for the Oracle Listener

When deploying a new database, one thing I usually do is to set up a logrotate configuration for the Oracle Listener log. The Oracle Listener logs every connection he makes and when using an Application Server to interface the database, this file can grow quite a bit. So we need to make sure that we properly rotate this log and compress the old logs.

To achieve this, I usually create the following configuration for logrotate:

Read the rest of this entry

Disable all constraints of a table

During maintenance, I had to disable all constraints of a table. I knew that Oracle SQL Developer (I really like it even though it is a developer tool and not aimed at Database Administrators) had built-in functions to do this, but since I could only access the database machine via SSH, I had to do it in SQL*Plus.

Luckily, SQL Developer is quite transparent about the commands it uses and I could therefore easily see what is going on when you disable all constraints on a table using the GUI. So here it goes…

Read the rest of this entry

EM won’t start after upgrade

After upgrading a database from to, I was unable to start the Oracle Enterprise Manager. Whenever I tried to do so, the log showed that it failed to start Database Control. Even when I deconfigured the Enterprise Manager, deleted the repository and started from scratch, I still ran into the same problem. In this post, I will describe the symptoms I encountered and also provide a solution.

Read the rest of this entry

MySQL: Return random row

For, we launched a contest where we award a restaurant voucher to a random person that wrote a review of a restaurant on our site. For this, we will be using the following query:

SELECT DISTINCT comment_author,comment_author_email
FROM wp_comments

What this query does is, that it selects all distinct rows, orders them randomly and shows the first entry of this selection.

Note that this query is relatively slow and should not be used in a program. When you use EXPLAIN on this query, MySQL will explain to you that it will create a temporary table and run a sort. Then only one entry is returned. But when you just use it once to determine a winner, this will work just fine!

New Instance or New Schema?

So when it comes to deploying a new application there is always the question if you should create a new instance on the database server or use the existing instance and just add a new schema? These both are valid options, but lets have a look at both options.

Lets assume you already have one instance running one application, now you need to provide a new database to a user. So depending on the application, you have the choice of creating a new instance for this specific new application or to use an existing database instance and just creating a new schema.

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