Oracle Database 11gR2 Performance Tuning Cookbook

Striping objects across multiple disks

In the previous recipe, we have seen how to distribute Oracle files on different disks to obtain better performance. In this recipe, we will see how to stripe objects using different tablespaces or data files, to improve performance.

How to do it...

The following steps will demonstrate how to stripe objects across multiple disks:

1. Connect to the database as SYSDBA:

   CONNECT / AS SYSDBA
   

2. Create a new tablespace, EXAMPLE2, on a different disk:

   CREATE TABLESPACE EXAMPLE2
     DATAFILE '/u01/oradata/TESTDB2/example2.dbf' SIZE 100M;
   

3. Move the CUSTOMERS table of the SH schema to the newly-created tablespace:

   ALTER TABLE SH.CUSTOMERS MOVE TABLESPACE EXAMPLE2 NOLOGGING;
   

4. Identify the indexes that need to be rebuilt:

   SELECT INDEX_NAME, ...