List all indexes in an Oracle tablespace

Sometimes Oracle DBAs need to do some tablespace cleaning and moving indexes from one tablespace to another. If you have hundreds of indexes this may become an awkward task to do manually. This article describes how to use SQL to list all indexes in a given tablespace so that you can automate the object movement.

The view 'dba_indexes' contains useful information about indexes including which tablespaces they belong to. To produce a list of indexes in any given tablespace, one can use the below code:

SELECT * FROM dba_indexes WHERE owner='USER' AND tablespace_name = 'USERS'

You can then feed this list to another script which will do index administration tasks such as rebuild, movement or drop.

Choose the Database Index: Selectivity and Cardinality

It is a known fact that most of the database performance tuning is achieved by building a suitable index. In most database engines, such as Oracle and MySQL, the most popular indexes types are either B*Tree or Bitmap. This article describes how to choose the appropriate index.

Determining the right index is all about cardinality and selectivity.

What is cardinality? Cardinality is the number of distinct values you have in a field. For example if  you have a table with a field Country, there are around 250 possible countries. Thus the cardinality of the Country field is 250.

What is selectivity? Selectivity is what we actually need to gauge the type of index to use. Selectivity can be determined by the following formula:

Selectivity =             Cardinality
                      Total number of Rows

Taking the previous case, if I have 2500 customers, the selectivity is 250/2500 = 0.1%.

Selectivity is important because it helps us to determine whether to use a Balanced Tree (B*Tree) Index versus a Bitmap index. In general you have to follow these golden rules:

If Selectivity > 4%, use B*Tree index
If Selectivity < 4%, use Bitmap index

In Oracle one can do an audit of the currently used indexes and determine whether they are good or not by using the following query:

  NUM_ROWS > 0

This will list all indexes which have a selectivity lower than 0.1%. These indexes should be Bitmap, otherwise they are not used by the Cost Based Optimizer.

To determine the selectivity of  a particular index, one can use the following query:

 index_name = 'INX_TRAN_FT_EXT_DATE'

One might argue that the Selectivity model above holds only if there are duplicates within the user record set. Of course it is true for small tables - infact in small tables with low cardinality, one might not even use indexes at all. However one can also use the Birthday Paradox to further predict if there are going to be duplicates.

The Birthday Paradox states that for every 23 persons, a pair will share the same birthday, or written mathematically, there is a 99% probability that there is a duplicate if the following is satisfied:

N = 3.0 * sqrt (cardinality)

, where N is the position within the table which returns the first duplicate. For the example above, for a cardinality of 203, you are 99% certain that you will encounter a duplicate on 3.0 * sqrt (203) = 43rd record.This means that even in a table of 100 rows you will most likely have a duplicate even if the country set is 250!

Happy Birthday - 6 years old!

This blog, one of the first ones in the Maltese islands, computes its 6th year today. One can browse through old blogposts in the archives menu on the right - me for one enjoyed reading a couple of posts! As they say, time really flies and I try to capture those interesting moments on this blog...

Create Oracle Backups in Enterprise Manager

If your Oracle database is connected to the Oracle Grid you can easily manage your backups through Enterprise Manager. This allows you to schedule a multitude of backups without going through complex RMAN scripts.

The following screenshots will guide you through the process of scheduling a backup in Enterprise Manager:

Step 1: Click on the Availability tab and select Backup Settings to define your backup policy:

Step 2:  Choose a suitable backup location:

Step 3: Choose your backup retention policy:

Step 4: Go back to the first screen (Availability tab) and this time click on schedule backup. In this screen choose what you want to backup:

Step 5: In this last screen you choose whether you want a full or an incremental backup.

In the next screens you set a date and a time and finally save your backup.

Windows Batch Script to Delete Old Files

Few of us know that Windows has some not-so-known tools which mimick Unix's powerful commands. Often we require to delete old files on a Windows machine to provide a means of backup retention policy. You don't need to install Perl or other text manipulation languages - Windows got tools of its own!

You can use a tool called FORFILES (download from Microsoft FTP server if you don't have it!) to select a file or set of files and execute a command on each of them. This allows us to filter files by name, timestamp, size and other attributes, and then perform a command based on their result.

The following are some common examples to use FORFILES to delete or display files older than N days:

Print a warning if the testfile is 3 days old or older:
C:\> forfiles /m testfile.txt /c "cmd /c echo file is too old" /d -3
Delete the testfile if it is is 3 days old or older:
C:\> forfiles /m testfile.txt /c "cmd /c Del testfile.txt " /d -3
Find .xls file that were last modified 60 days ago or older
C:\> FORFILES /M *.xls /C "cmd /c echo @path was changed 60 days ago" /D -60
List the size of all .doc files:
C:\> FORFILES /S /M *.doc /C "cmd /c echo @fsize"

Automating Windows SCP scripts with WinSCP

Legacy FTP usage is being phased out and most of you will have to face newer company security policies to start using SCP to transfer your daily backups from one server to another. Luckily for the system administrators, this is easily done using WinSCP's command line console as this article will demonstrate.

The example code below connects to '' with account 'user' and password 'pass123', uploads a file and closes the session. For the sake of this article, let's call this example.ini:

# Automatically abort script on errors
option batch abort
# Disable overwrite confirmations that conflict with the previous
option confirm off
# Connect using a password
# Force binary mode transfer
option transfer binary
# Upload file to the local directory d:\
put examplefile.txt d:\
# Disconnect

You can then call this script through WinSCP console as follows:

winscp.exe /console /script=example.ini

Copy and Skip Existing Files in Windows

If you are hardcore old school Batch coders like me, you most probably have stumbled across the requirement to copy your backups from one place to another while skipping files which already exist on the destination point. This article describes a Windows tool which does that very easily.

This tool is called Robocopy and is freely available to all windows users inside the Windows Resource Kit Tools. You can get it from here. Robocopy has many options which we will not discuss in this article, but its default behaviour allows us to copy files and skipping existing ones:

robocopy C:\source\*.bak D:\destination\

Best FREE Firewalls for Windows

Most of the Windows users prefer having a different firewall solution other than the builtin Windows Firewall. This has its advantages and luckily for us there are really great firewalls free of charge!

As of the writing of this article, the best free firewalls for Windows are:

Comodo - Best product in the free-firewall category with impressive performance and an easy-to-use interface
Zone Alarm - Extremely easy to use
Ashampoo - Good free firewall, but not as reliable as our #1 and #2 picks
Agnitum - Recommended by website visitors
Sunbelt/Kerio - The Sunbelt Kerio Personal Firewall will keep on working after the first 30 days, only in a more limited mode, but free

Granting privileges on ALL tables in Postgresql

If you have a database and you would like to grant select on all tables to a particular user or role, in Postgresql one must follow an unorthodox procedure. This is described in the following article.

Most users mistakenly believe that to give a privilege on all tables to a user/role, they have to grant the privelege at a database level like so:

grant all on database my_db to developers

This will not work because it will not cascade at a table level. Therefore the correct procedure to grant a privilege on all tables is in following these two steps:

1) Generate grant statements as follows:

select 'grant all on '||schemaname||'.'||tablename||' to developers;' from pg_tables where schemaname in ('my_db', 'public') order by schemaname, tablename;

2) Copy and paste the generated output and run the resulting scripts.

This will grant all privileges on all tables to a particular user or role.

Integrating Postgresql with LDAP + PAM

Integrating Postgresql with LDAP and PAM is very easy as this article will show. The only requirements are that you have already setup LDAP and PAM on your UNIX box.

Create a PAM configuration file for Postgresql, such as /etc/pam.d/postgresql90:

auth           required service=system-auth
account        required service=system-auth
session        required service=system-auth

Edit pg_hba.conf and add the following line, including the subnet of the client:

# TYPE  DATABASE        USER            CIDR-ADDRESS            METHOD
host    all             all            pam   pamservice=postgresql90

The pamservice method must reflect the name of the PAM configuration file for postgresql created earlier.

Finally edit postgresql.conf and set the 'listen_addresses' to the ip address of the postgresql server. One final last step to affect the changes is to reload the postgres configuration file like this:

pg_ctl reload

Restore Oracle RMAN backup on a new Windows machine

Sometimes disaster strucks (ex. virus, disk failures, etc..) and the only option to get up and running is to restore our Oracle backup onto a new Windows server. This article simplifies the whole process of completely restoring and recovering a database on a brand new Windows server.

1) It is important that we maintain the same directory structure as the original server when restoring Oracle through RMAN. Gather information about your directory structure from your previous (failed) machine:
  • Datafile Location
  • Archivelog Location
  • Oracle Binaries Location
  • RMAN Backups Location
2) Install Oracle software with the same Oracle version as the original server.

3) Place the RMAN backups in the appropriate location as defined in the controlfile.

4) Create Windows service for the database:

C:\>oradim -new  -sid ORCL -startmode m
C:\>oradim -edit -sid ORCL -startmode a

Make sure that the service is started so we may be able to connect to it in the next steps.

5) Get the DBID of the database - I normally get it from the backup report - although this steps is not really necessary (since we restore the SPFILE first) I do recommend it.

6) Create bdump,udump,cdump and create folder at $ORACLE_HOME (if using Oracle versions before 11g)

7) Connect with rman to the target database at local server:

C:\>Documents and Settings\Administrator>set ORACLE_SID=ORCL
C:\>Documents and Settings\Administrator>rman target /

8) Configure RMAN:

RMAN> set dbid 123456789
RMAN> startup nomount

9) Restore SPFILE:

RMAN> restore spfile from 'D:\EXPORT\RMAN\C-3648252040';

10) Startup database:

RMAN> startup force nomount;

11) Restore controlfile:

RMAN> restore controlfile from 'D:\EXPORT\RMAN\C-3648252040';
RMAN> shutdown;
RMAN> exit;

12) Proceed to restore and recovery:

RMAN> startup mount;
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;
RMAN> exit

Now you should have a completely recovered database!