Temporary Tablespaces
When insufficient sort memory is allocated to the user process to perform a required sort, Oracle performs the sort on disk by creating temporary segments in the tablespace specified by the TEMPORARY TABLESPACE parameter for the user. In addition, temporary segments are used to perform complex queries like joins, UNIONs, and MINUSes, and for index creation. Guidelines for temporary areas include the following:
Create a separate tablespace (usually called TEMP) for temporary segments, and place the datafile(s) for this tablespace on a separate disk device, if possible.
Specify INITIAL and NEXT parameters in the DEFAULT STORAGE clause of the CREATE TABLESPACE command. Use the same value for both in order to eliminate space fragmentation, which is particularly likely in the TEMP tablespace, where objects are constantly being created and dropped.
Be certain to specify a TEMPORARY TABLESPACE for each user. If one is not specified, Oracle defaults to SYSTEM, which is almost guaranteed to have a negative impact on performance.
Get Oracle Database Administration: The Essential Refe now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
