Script to find reclaimable space in the datafiles

Storage costs are a major matter of expense in any large database environment. Therefore, in order to operate a cost-optimized environment, it makes sense to regularly review all databases in terms of datafile sizes. Usage of these files can vary dramatically, depending on the application using the database. Therefore, we need a way to quickly identify datafiles which we can shrink in order to save storage costs.

Read the rest of this entry »

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.

Read the rest of this entry »

Algorithm to find first available number

So recently I stumbled across a programming quiz to which I later returned because it somehow fascinated me.


Finding the first available number (or the smallest missing number) in a list is a common problem in Computer Science (for example for Defragmenting or generating keys) and describes the search for the smallest natural number, which is not part of a set X of natural numbers. X is a set of distinct natural numbers (and being a set, is not ordered).

We are now looking for a function with linear worst-case time complexity O(n).


We define X as a set of distinct natural numbers:

X = {23,9,12,0,11,1,13,7,21,14,5,4,17,19,3,6,2}

So in this set, we find that the number 8 is the first available number (smallest missing number). So running the algorithm over the above set should return 8.
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 »

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 »

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? Read the rest of this entry »

Linux RAMDISK with tmpfs

The Linux kernel provides the tmpfs, which basically creates a file system in memory. This temporary file system can be used to store temporary data, such as caches or log files. Read more about tmpfs in the kernel documentation: tmpfs.txt

After reading this excellent article about using tmpfs in Linux, I decided to put it to the test. Even though the Linux kernel already does a good job caching files, I wanted to see the performance of this solution by applying different loads on it. For this, I am using the IOzone tool I already used for my ZFS tests (1) (2) and my Amazon EC2 IO test.

Read the rest of this entry »

Debian with a vanilla kernel

So one might want to ask why you’d want to sacrifice the fantastic stability and openness of Debian to install a vanilla (= original) kernel (Debian currently has 2.6.32). There are quite a few reasons for doing so. For example, the current kernel (2.6.38) has TRIM support, which is something I am looking for when using SSDs. Also, maybe you want to have a bleeding edge kernel just for the fun of it. So lets get started.

Read the rest of this entry »