Simon Krenger

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!

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:

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:

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!

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:

dba@KDB01:SQL> CREATE MATERIALIZED VIEW simon.simon_mv AS SELECT * FROM dual;
CREATE MATERIALIZED VIEW simon.simon_mv AS SELECT * FROM dual
                                                         *
ERROR at line 1:
ORA-01031: insufficient privileges

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

So here is another post I keep for my own reference, since I keep forgetting about precision and scale, primarily which is which.

The Oracle Concepts guide states that the NUMBER datatype stores fixed and floating-point numbers. A column with the NUMBER datatype can be defined as follows:

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 11.2.0.3). 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:

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.

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.

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:

SQL> SELECT count(*) FROM simon.HUGE_TABLE;

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

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