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.

Alright, so when we need to recreate a database link for some reason and we do not happen to have the password handy, we’re usually stuck. However, sometimes there is a way to recover passwords for database links.

The method described below only works for the “old” password versions (<= 11.2.0.2)!

Starting with Oracle 11.2.0.2, Oracle salts the password hashes, therefore you will need to crack the password and cannot just query it. However, if the database link was created pre-11.2.0.2, the password is saved in an “old” format without the salt. To check if there are any database links with this old format, query SYS.LINK$ like so:

So today, during the upgrade of a database, all other databases using the same ORACLE_HOME stopped dead in their tracks. The ALERT log reported the following:

2014-09-30 13:34:41.622000 +02:00
Archived Log entry 24398 added for thread 1 sequence 24398 ID 0x2d91d796 dest 1:
2014-09-30 13:47:39.764000 +02:00
Errors in file /u00/app/oracle/diag/rdbms/kdb12345/kdb12345/trace/kdb12345_psp0_57147560.trc:
ORA-07274: spdcr: access error, access to oracle denied.
IBM AIX RISC System/6000 Error: 2: No such file or directory
PSP0 (ospid: 57147560): terminating the instance due to error 7274
2014-09-30 13:47:41.346000 +02:00
System state dump requested by (instance=1, osid=57147560 (PSP0)), summary=[abnormal instance termination].
System State dumped to trace file /u00/app/oracle/diag/rdbms/kdb12345/KDB12345/trace/KDB12345_diag_49938448_20140930134741.trc
2014-09-30 13:47:44.779000 +02:00
Instance terminated by PSP0, pid = 57147560

Hmm, so what to do? In the trace file, we find out a bit more:

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:

As we are migrating the last databases from Oracle 10gR2 to the latest 11.2.0.4 release, there are some errors that we stumbled upon along our way. For example, when we tried to upgrade one particular database, during the upgrade the following error was thrown:

SQL> SELECT TO_NUMBER('DATA_VAULT_OPTION_ON') FROM v$option
  2   WHERE
  3    value = 'TRUE' and parameter = 'Oracle Database Vault';
SELECT TO_NUMBER('DATA_VAULT_OPTION_ON') FROM v$option
                 *
ERROR at line 1:
ORA-01722: invalid number

Ouch. That is something that you do not want to see during an upgrade! Needless to say, the upgrade won’t continue here, so how to fix this?

Well, basically this is a well-known error, and there is MOS note 1409844.1 that describes how to fix the problem: Catupgrd Returns : Ora-01722 Select To_number(‘DATA_VAULT_OPTION_ON’) From V$option

In preparation for my OCP examination, I am currently playing around with ASM. For this purpose I am using an AWS instance to install and configure my database. So I added my disks, partitioned them using fdisk and installed ASMLib to manage them. But when I wanted to issue the createdisk command, this happened:

[root@ip-10-234-66-103 ~]# oracleasm createdisk DATA0 /dev/xvdc1
Device "/dev/xvdc1" is already labeled for ASM disk ""

So what is the problem here? Before deciding to use ASMLib, I tried to add the disks using only ASM commands from within the ASM instance, which failed for some reason. This is why I then switched to ASMLib.

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.

Before starting, here is the deal: While “ALTER USER simon PASSWORD EXPIRE” exists, the “PASSWORD UNEXPIRE” statement does not exist in Oracle, see the ALTER USER statement in the language reference. Now, there is a good reason that command does not exist.

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:

One thing that you don’t want to see while performing an update of a database with the OPatch utility is a SEGFAULT. In my scenario, I simply wanted to list all the installed patches for an ORACLE_HOME and therefore issued opatch lsinventory:

$ cd $ORACLE_HOME/OPatch
$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.1.0.10.1
Copyright (c) 2013, Oracle Corporation.  All rights reserved.

Unhandled exception
Type=Segmentation error vmState=0x00060000
[..]