Simon Krenger

This is a fun one.

We developed a script to process certain indexes on a database somehow, it kept missing some of the indexes that clearly existed. We then found the problem: DBA_INDEXES had more entries than DBA_SEGMENTS. See the following example:

SQL> SELECT owner, index_name as i_name from dba_indexes WHERE owner = 'SIMON'
  2  MINUS
  3  SELECT owner, segment_name as i_name FROM dba_segments WHERE owner = 'SIMON';

OWNER                I_NAME
-------------------- --------------------------------------------------
SIMON                IDX_...
SIMON                IDX_...
SIMON                IDX_...
SIMON                PK_...
SIMON                PK_...
SIMON                UNQ_...
SIMON                UNQ_...

7 rows selected.

So here we see that there are clearly indexes for which there are no segments. We then looked at the tables where these indexes are located and noticed a particular thing: All the corresponding tables for these indexes were empty.

So the reason for this behaviour is called “Deferred Segment Creation“. This means that when a “CREATE TABLE” statement is issued and no rows are inserted, there are no segments that are created. This behaviour can be controlled by the DEFERRED_SEGMENT_CREATION parameter.

This makes sense in large schemas, where not all tables are populated. Instead of having segments created and extents allocated, only the definition of the table is saved. As soon as the table has at least one row, the segments are automatically created.

While upgrading an environment to Oracle 12c, one thing that needs to be done is to upgrade the RMAN catalog. One change introduced by Oracle is that there are new features used in the version 12 RMAN catalog. So if you try to upgrade an RMAN catalog running on Oracle Standard Edition, you will encounter the following error:

RMAN> upgrade catalog;

error creating create_deleted_object_seq
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: ORA-00439: feature not enabled: Partitioning

Mike Dietrich from the Oracle Upgrade blog has written an excellent article concerning this issue. Most specifically, MOS Note 1927265.1 notes implicitly that Enterprise Edition is a requirement:

Even though I am not really happy about the new recertification requirements from Oracle, I took it to myself to refresh my OCP certificate. I completed the exam 1Z0-060 and can call myself now a “Oracle Database 12c Administrator Certified Professional”.

Although I am not allowed to disclose information about the exam, I must say that the I found this exam easier than previous exams. Since the exam is a multiple-choice exam, one can often answer the questions by just guessing or by exclusion principle…

As of PSU 12.1.0.2, Oracle introduced “PDB State Management Across CDB Restart”, which will automatically put a pluggable database in a previously defined state on database startup. To show the current saved state of the PDBs, you can query the documented view cdb_pdb_saved_states:

SQL> SELECT con_name, instance_name, state FROM cdb_pdb_saved_states;

CON_NAME		       INSTANCE_NAME		      STATE
------------------------------ ------------------------------ --------------
P1			       cdb2			      OPEN
P2			       cdb2			      OPEN

But beware: When you unplug and plug in the database, this saved state will be lost.

I think this is one of the more awesome improvements in 12.1.0.2, since the original startup trigger was more of a workaround than a real solution.

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:

When working with the new multitenant architecture of Oracle 12c, one is often confronted with questions like “Ok, is this or that possible in the pluggable database? And where is that value stored?”. In this article, I want to show you where the parameters of the pluggable databases are stored. Hint: there is no SPFILE for a pluggable database!

Consider the following container database (CDB), named “cdb2”, with the following pluggable databases (PDBs). Using SQL*Plus, connected to the CDB, we can issue show pdbs to get a list of the PDBs and their status:

Update: Starting with Oracle 12.1.0.2, it is possible to save a PDB state so this workaround is no longer necessary.

When starting up an Oracle 12c database with pluggable databases, the pluggable databases in a container database are not automatically opened, just placed in MOUNT mode. This can be a problem in some cases.

To automatically open the pluggable databases on startup, you’ll have to create an AFTER STARTUP trigger in the CDB. For my own reference, I will post such a trigger here:

CREATE TRIGGER open_all_pdbs
AFTER STARTUP ON DATABASE
BEGIN
     EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN';
END open_all_pdbs;
/

Something that always comes up when discussing Oracle versions is that I am not always sure which number is the Major Database Release and which is the Database Maintenance Release. In the Oracle documentation, the numbers are clearly described:

Oracle Release Number Format

12.1.0.1.0
 ┬ ┬ ┬ ┬ ┬
 │ │ │ │ └───── Platform-Specific Release Number
 │ │ │ └────────── Component-Specific Release Number
 │ │ └─────────────── Fusion Middleware Release Number
 │ └──────────────────── Database Maintenance Release Number
 └───────────────────────── Major Database Release Number

Whereas the different numbers mean the following:

Major Database Release Number
The first numeral is the most general identifier. It represents a major new version of the software that contains significant new functionality.

Database Maintenance Release Number
The second numeral represents a maintenance release level. Some new features may also be included.

Fusion Middleware Release Number
The third numeral reflects the release level of Oracle Fusion Middleware.

Component-Specific Release Number
The fourth numeral identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.

Platform-Specific Release Number
The fifth numeral identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this numeral will be the same across the affected platforms.

As with all the new features that Oracle 12c brings us, we want to test them out! So one of the lesser known features in the new release is the Automatic Data Optimization with the Heat Map mechanism. Oracle describes the feature in a White Paper:

Heat Map is a new feature in Oracle Database 12c that automatically tracks usage information at the row and segment levels. Data modification times are tracked at the row level and aggregated to the block level, and modification times, full table scan times, and index lookup times are tracked at the segment level. Heat Map gives you a detailed view of how your data is being accessed, and how access patterns are changing over time. Programmatic access to Heat Map data is available through a set of PL/SQL table functions, as well as through data dictionary views.

Sounds great, how can we use it? It turns out, that is relatively easy, all you have to do is to set the HEAT_MAP parameter to “ON” (source) and add an ILM policy to your table:

For my own reference, as I was asked what the minimum memory size for an Oracle database is. It turns out, that both in Oracle 11g R2 (11.2) as well as in Oracle 12c (12.1), the minimum value for MEMORY_TARGET (sum of SGA and PGA) is 152MB: