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 here is another post I will keep just for my own reference.

Whenever I need to perform an incomplete recovery of the database, I usually use the SET UNTIL TIME to specify the time for an incomplete recovery. Sadly, when NLS_LANG is not set properly, the time format that needs to be used is not something I can remember.

So here is an example for using SET UNTIL TIME with a sane time format:

run {
set until time "to_date('22-APR-2015 13:30:00','DD-MON-YYYY HH24:MI:SS')";
restore database;
recover database;
alter database open resetlogs;
}

You can find more examples in the Oracle documentation.

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.