Tablespace Standards

Tablespace Definition

• Names
  1. Names will generally be descriptive of the associated system which will use the tablespace as its default, e.g. ARIS for OPS$ARIS, etc.
  2. Names will normally be eight or less characters in length.
• Files
  1. Underlying file names will be of the form ORADISK{m}:[TABLESPACES]{tsname}7_{n}.TBS where:
     1. {m} refers to the ADMIN{m} disk on which the file is to reside.
     2. {tsname} is the name of the tablespace.
     3. {n} is a sequential number used to refer to the various files making up the tablespace.
  2. All tablespaces will consist of at least one datafile, identified by the {_1} in the file name.

Unique Tablespaces

HOT

SCRATCH

TEMPTABLES

The following general guidelines apply to the HOT tablespace.

• Usage
  1. The HOT tablespace should only be used to store tables where access speed is a critical priority.
  2. Since there are no mechanical limitations with SSDs, such as access arm speeds, it is viable to store a tables indexes along with the table itself in the HOT tablespace.
  3. In all cases, the Database Administrator should be informed prior to creating tables or indexes in the HOT tablespace, as space there is of a premium.
• Table Storage Parameters
  1. Because of its limited size, tables stored in the HOT tablespace should be created with maximal storage parameters.
  2. Tables and indexes within the HOT tablespace should be monitored frequently to assure maximum usage of space.

The following general guidelines apply to the SCRATCH tablespace.

• Usage
  1. SCRATCH is used to store temporary tables used in the production of reports or for the storage of transitory tables used in the execution of various processes within Oracle systems.
  2. All Database Users (Schemas) have write access to SCRATCH.
  3. All jobs should remove any SCRATCH tables created by that job at the end of job execution.
• Table Storage Parameters
  1. INITIAL and NEXT extents should normally be set to 100K unless the table is expected to be large (over one megabyte).
  2. In the event the table is expected to be large (over one megabyte), storage parameters should be selected to be increments of 100K, e.g. 500K, 700K, etc.
  3. In all cases, the PCTINCREASE parameter should be set to zero.
• Indexes
  1. Indexes created on SCRATCH tables should also reside in the SCRATCH tablespace.
  2. Guidelines for storage parameters match those for tables.

All Oracle Users (Schemas) are granted full access to the TEMPTABLES tablespace.

All Oracle Users (Schemas) have as their temporary tablespace, TEMPTABLES.

Return to: Oracle Standards / Oracle Database / ACS Staff / CC Staff Server / Houck Computing Center / Computing at Oberlin / Oberlin Online Home