Temporary tablespaces

Summary

Symptom

1. 1. Why is a temporary tablespace required?

1. 2. What is the default name of the temporary tablespace in the SAP environment?

1. 3. Which types of temporary tablespaces are available?

1. 4. How can I find out how the temporary tablespace was created?

1. 5. How is the temporary tablespace assigned to a database user?

1. 6. How I can find out which temporary tablespace is assigned to a database user?

1. 7. Which general problems may occur in connection with temporary tablespaces?

1. 8. What are the restrictions and problems associated with the individual types of temporary tablespaces?

1. 9. Which type of temporary tablespace does SAP recommend?

1. 10. How I can find out how the temporary tablespace is currently filled?

1. 11. If problems occur, can I recreate the temporary tablespace without a restore?

1. 12. What is a default temporary tablespace?

1. 13. How can I change over from DMTS/P to LMTS/T?

1. 14. Where can I find more information about temporary tablespaces?

Other terms

Frequently asked questions

Solution

1. 1. Why is a temporary tablespace required?

              A temporary tablespace is mainly required for the intermediate storage of temporary data during certain database actions, for example:

• Sorting/aggregation with CREATE INDEX, SELECT ... DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS (when the memory area defined using SORT_AREA_SIZE is insufficient)

• Creating and accessing bitmap indexes (if the memory areas defined using BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE are insufficient)

• Executing hash joins (if the memory area defined using HASH_AREA_SIZE is insufficient)

• Explicit or implicit use of temporary tables (as part of the STAR transformation, for example)

              With BW 2. x, permanent objects are also created in the temporary tablespace in some situations (see Note 216440).

1. 2. What is the default name of the temporary tablespace in the SAP environment?

              The default name of the temporary tablespace in the SAP environment is PSAPTEMP.

1. 3. Which types of temporary tablespaces are available?

• Dictionary-managed tablespace with PERMANENT contents (DMTS/P):
  
  CREATE TABLESPACE <temp_tsp> DATAFILE ... EXTENT MANAGEMENT
  DICTIONARY [PERMANENT];

• Dictionary-managed tablespace with TEMPORARY contents (DMTS/T):
  
  CREATE TABLESPACE <temp_tsp> DATAFILE ... EXTENT MANAGEMENT
  DICTIONARY TEMPORARY;

• Locally-managed tablespace with PERMANENT contents (LMTS/P):

• • UNIFORM:
    
    CREATE TABLESPACE <temp_tsp> DATAFILE ... EXTENT MANAGEMENT
    LOCAL UNIFORM SIZE <size> PERMANENT;

• • AUTOALLOCATE:
    
    CREATE TABLESPACE <temp_tsp> DATAFILE ... EXTENT MANAGEMENT
    LOCAL AUTOALLOCATE PERMANENT;

• Locally-managed tablespace with TEMPORARY contents (LMTS/T):
  
  CREATE TEMPORARY TABLESPACE <temp_tsp> TEMPFILE ...
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE <size>;

              As described further below, only DMTS/P and LMTS/T may be considered as temporary tablespaces in the SAP environment. Never use LMTS/P as a temporary tablespace. For detailed information about LMTS, see Note 214995. Only temporary objects can be created in TEMPORARY tablespaces. You can create both temporary and permanent objects in PERMANENT tablespaces.

1. 4. How can I find out how the temporary tablespace was created?

              You can find the relevant information using the following query on DBA_TABLESPACES:

SELECT CONTENTS, EXTENT_MANAGEMENT, ALLOCATION_TYPE
FROM DBA_TABLESPACES WHERE
TABLESPACE_NAME = '<temp_tsp>';

              In this case, CONTENTS contains either PERMANENT or TEMPORARY tablespaces. EXTENT_MANAGEMENT is either DICTIONARY or LOCAL. ALLOCATION_TYPE is USER (for DMTS), UNIFORM (for UNIFORM-LMTS) or SYSTEM (for AUTOALLOCATE-LMTS).

1. 5. How is the temporary tablespace assigned to a database user?

              To assign a temporary tablespace to a new user that you want to create, proceed as follows:

CREATE USER <user> TEMPORARY TABLESPACE <temp_tsp> ...;

              To assign another temporary tablespace to an existing user, proceed as follows:

ALTER USER <user> TEMPORARY TABLESPACE <temp_tsp>;

1. 6. How I can find out which temporary tablespace is assigned to a database user?

              The following SQL statement gives you a list of all database users and their temporary tablespaces:

SELECT USERNAME, TEMPORARY_TABLESPACE FROM DBA_USERS;

1. 7. Which general problems may occur in connection with temporary tablespaces?

• ORA-00959: If a temporary tablespace is assigned to a user that does not exist, transactions that have to carry out an operation in the temporary tablespace terminate with ORA-00959.

• ORA-01220: This error occurs if, in the case of a closed database, the access to a V$ view or a RMAN activity requires a sorting that cannot be fully carried out in the memory (because of the PGA parameter settings). In this case, increase the size of the PGA parameters such as SORT_AREA_SIZE or PGA_AGGREGATE_TARGET, or avoid extensive sorting. See also Note 919588.

• ORA-01652: This error is caused by a tablespace overflow. As a rule of thumb, PSAPTEMP should be at least twice as large as the largest index. If an overflow occurs despite this size, this is frequently due to external reasons such as incorrect Oracle parameter settings, large-scale parallel processing or unfavorable access paths. See also Note 3155.

• ORA-01658: If this error occurs in BW, use SAP_DROP_TMPTABLES to delete any temporary tables that exist (Note 379499).

• ORA-03232: If this error occurs in connection with the temporary tablespace, check the configuration of the temporary tablespace as described in Note 485755.

1. 8. What are the restrictions and problems associated with the individual types of temporary tablespaces?

• DMTS/P

• • If the selected default extent sizes (INITIAL, NEXT) are too small, performance problems may occur due to space transaction enqueues. Errors such as ORA-01575 (see Note 78595) may also occur. Notes 164925 (R/3) and 359835 (BW) describe the optimal setup.

• • Even if the setup is correct, this tablespace type may perform poorly in comparison with LMTS.

• • If you select a MAXEXTENTS value that is too low, error ORA-01630 may occur on the temporary tablespace. Therefore, you should generally set the MAXEXTENTS default for PSAPTEMP to UNLIMITED.

• • If SYSTEM is a LMTS (Note 706625) or if a default temporary tablespace is used (Note 683075), you cannot create a temporary tablespace of the type DMTS/P.

• • If you try to use a DMTS/P tablespace as a default temporary tablespace, error ORA-12902 occurs.

• • If you open a database in read-only mode (for example, a standby database within structure validation using VALIDATE STRUCTURE or an export - see Note 817253), error ORA-01682 ("read-only DB cannot allocate temporary space in tablespace") may occur when you access a DMTS/P-PSAPTEMP (for example, within sortings). To avoid the problem, change to LMTS/T.

• DMTS/T

• • This tablespace type may result in very long runtimes when you shut down a database (Note 183842) as well as other performance problems (Notes 183791 and 216881). Therefore, we do not recommend that you use this tablespace type.

• • Temporary segments are only released again when you shut down the database. For this reason, the tablespace always appears to be full (see Note 107257).

• • If SYSTEM is a DMTS or if a default temporary tablespace is used (Note 683075), you cannot create a temporary tablespace of the type DMTS/T.

• LMTS/P

• • Generally, tablespaces of this type cannot contain any temporary segments and they return error ORA-3212 (Note 609532) when you try to create a temporary segment. This tablespace type is therefore unsuitable for use as a temporary tablespace. The special BW 2.x configuration described in Note 387946 is the only exception here.

• • As of Oracle 9i, you can no longer assign an LMTS/P tablespace to a database user as a temporary tablespace. An assignment fails with error ORA-12911.

• LMTS/T

• • You can only use this tablespace type as of Oracle 8.1.x.

• • With BW 2.x, partially permanent objects are stored in PSAPTEMP (Note 216440). If PSAPTEMP is of the type LMTS/T, this would fail with error ORA-02195. To avoid the problem, PSAPTEMP must remain PERMANENT, but you can create another (TEMPORARY) tablespace. Note 387946 describes this procedure.

• • The data files from LMTS/T tablespaces ("TEMPFILES") are created as sparse files on certain operating system platforms, and therefore initially only a fraction of the file size is physically allocated. This may cause the information of df, du, ls and similar OS commands to appear to be inconsistent as well as cause inadvertent multiple allocation of memory space.

                    If error ORA-01114 occurs in R/3 with SQL statements that use the temporary tablespace (for example, because of an ORDER BY statement or a sort merge join), and if the placeholders %s are not replaced correctly in the error message, or if a file is referenced with a number > DB_FILES, a TEMPFILE created as a sparse file can no longer be extended because no more space is available in the file system.

                    For more information, see Note 548221 and make sure that the file system has sufficient space to extend the TEMPFILES.

• • If ORA-01257 occurs when you create TEMPFILES in Oracle 9.2 or lower, refer to Note 487318 and enter an explicit SIZE specification.

• • Depending on the release, TEMPFILES are not saved by BRBACKUP as part of the backup (see Notes 416782 and 635552). This may result in ORA-25153 during the restore/recovery. Note 600513 describes some possible solutions.

• • TEMPFILES are not found in the DBA_DATA_FILES view. Instead, you must execute a SELECT on DBA_TEMP_FILES to obtain information about these files.

1. 9. Which type of temporary tablespace does SAP recommend?

              As of Oracle 8I, SAP recommends that you use LMTS/T. However, when you use R/3 Releases 4.5B or lower, you must refer to Note  662900 when you activate LMTS/T. Important advantages of this PSAPTEMP type are: you can avoid ABAP access in Space transactions; you can avoid redo information, since LMTS/T tablespace is automatically created with NOLOGGING.

              In theory, you can also use DMTS/P, as long as the SYSTEM tablespace is a DMTS and you do not use a default temporary tablespace (Note 683075).

              However, due to some drawbacks such as the greater effort involved for Space Management, LMTS/T is the preferred option.

1. 10. How I can find out how the temporary tablespace is currently filled?

              With the following query, you receive the work areas that are currently reserved in the temporary table space (OPERATION_ID therefore refers to the lines in the execution plan that are responsible for the PSAPTEMP requirement):

SELECT
  SID,
  OPERATION_TYPE,
  OPERATION_ID,
  TEMPSEG_SIZE
FROM
  V$SQL_WORKAREA_ACTIVE
WHERE
  TEMPSEG_SIZE > 0
ORDER BY
  TEMPSEG_SIZE DESC;

              You can use the following selection to determine how many bytes are currently used in the temporary tablespace. You can use this query only with LMTS/T because V$TEMP_EXTENT_POOL is restricted to this type:

SELECT
  TABLESPACE_NAME,
  ROUND(SUM(BYTES_USED) / 1024 / 1024) BYTES_MB
FROM
  V$TEMP_EXTENT_POOL
GROUP BY
  TABLESPACE_NAME;

              In the V$SORT_SEGMENT and V$SORT_USAGE views, you can find information about the sort segments that currently exist and the corresponding users.

              Note that the information from V$SORT_SEGMENT and V$TEMP_SPACE_HEADER often indicates that the temporary tablespace is extremely full. This information is usually incorrect, since many areas can be released and reused if necessary. For more information, see Note 827019.

              You can use V$TEMPORARY_LOBS to determine whether temporary LOBs currently exist. If they exist, you can then determine how many exist. The workaround described in Note 500340, to guarantee atomicity, may be the cause of a larger number of temporary LOBs in the J2EE environment.

1. 11. If problems occur, can I recreate the temporary tablespace without a restore?

              If no permanent objects existed in the temporary tablespace, you can use drop/create to recover the tablespace without a restore (see also Note 4161).

              In the case of an LMTS/T tablespace, you can also proceed as follows:

ALTER DATABASE TEMPFILE  '<path_and_filename>' DROP;
ALTER TABLESPACE PSAPTEMP ADD TEMPFILE '<path_and_filename>'
  SIZE <size>;

              This is particularly necessary if you use a default temporary tablespace because it is not possible to drop the tablespace in this case.

1. 12. What is a default temporary tablespace?

              If you do not specify a temporary tablespace when you create a user, the SYSTEM tablespace is automatically used as a temporary tablespace. To prevent this, you can define a default temporary tablespace, which is then entered as a temporary tablespace for the user. For more information, see Note 683075.

              With newer releases and patches, BRCONNECT checks whether the default temporary tablespace is still set to SYSTEM. If it is, the system issues warning BR0971W. To solve the problem, define PSAPTEMP as the default temporary tablespace as described in Note 683075:

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE PSAPTEMP;

1. 13. How can I change over from DMTS/P to LMTS/T?

              To change over from DMTS/P to LMTS/T, carry out the following steps:

• Make sure that there are no R/3 tables and indexes in PSAPTEMP:
  
  SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE FROM DBA_SEGMENTS
  WHERE TABLESPACE_NAME = 'PSAPTEMP';

• If you want to make the change while the system is running, you can temporarily assign another existing tablespace to the SAP user or you can assign an additional tablespace created especially for this purpose:
  
  ALTER USER <sapuser> TEMPORARY TABLESPACE <temp_tsp>;

           Otherwise, there is a risk that transactions may terminate with error ORA-00959. However, it is better to make the change during downtime.

• You can now drop the previous temporary tablespace:
  
  DROP TABLESPACE PSAPTEMP INCLUDING CONTENTS;

           Note: When you drop a tablespace, current accesses to PSAPTEMP terminate with ORA-00959.

• In the next step, you can recreate the LMTS/T-PSAPTEMP as described above. If you want to reuse the previous data file as a temp file, replace "SIZE <size>" in the CREATE command with "REUSE".

• If you have converted the temporary tablespace for the SAP user in the meantime, you can now reset it to PSAPTEMP. If you created a help tablespace, you can now drop it again.

1. 14. Where can I find more information about temporary tablespaces?

              Note 416772 contains more information about tablespaces in the SAP environment in general and about temporary tablespace in particular.

              The Oracle 9i Online Documentation also contains information about tablespaces in general and temporary tablespaces in particular in the section entitled:

Administrator's Guide
-> 11 Managing Tablespaces