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, STATUS FROM ALL_INDEXES
     WHERE TABLE_OWNER = 'SH' AND TABLE_NAME = 'CUSTOMERS';
   

5. Rebuild the indexes:

   ALTER INDEX SH.CUSTOMERS_PK REBUILD;
   ALTER INDEX SH.CUSTOMERS_GENDER_BIX REBUILD;
   ALTER INDEX SH.CUSTOMERS_MARITAL_BIX REBUILD;
   ALTER INDEX...