Set up Performance Warehouse on Oracle Database (separate tablespace for indexes)

When setting up the Performance Warehouse on Oracle database, some users may use a dedicated tablespace for indexes, while the actual data is stored in a separate tablespace. The AppMon default specified in the Oracle DB schema creation script (which can be found in DT_HOME\server\conf\repository\oracle\create.ddl) is that the indexes are in the same tablespace. To have the indexes in a separate tablespace:

  • Edit the schema creation script (DT_HOME\server\conf\repository\oracle\create.ddl) for the indexes. make a copy of the default schema creation script and edit the copy rather than overwrite the existing script file.
  • Execute it manually on the Oracle Database.
  • Once manually connected, connect the Performance Warehouse to the AppMon server by selecting Server settings > Performance Warehouse in the AppMon client.

The recommended setting for the different tablespaces (both data and index tablespace) are:

CREATE TABLESPACE XYZ DATAFILE

LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;