Oracle Temporary Tablespace Test

A temporary tablespace, contrary to what the name might indicate, does exist on a permanent basis as do other tablespaces, such as the SYSTEM and SYSAUX tablespaces. However the data in a temporary tablespace is of a temporary nature, which persists only for the length of a user session. Oracle uses temporary tablespaces as work areas for tasks such as sort operations for users and sorting during index creation. Oracle does not allow users to create objects in a temporary tablespace. By definition, the temporary tablespace holds data only for the duration of the user’s session, and the data can be shared by all users.

Sufficient free space should be available in the temporary tablespace, as critical operations such as sorting and execution of hash-intensive queries may otherwise fail. Periodically checking the space usage in the temporary tablespaces will provide you with early warning signals of potential space contentions.

This test helps you to track the space usage of the temporary tablespace. Using this test, you can figure out the following:

  • The size of the temporary tablespace
  • The size that is allocated from this temporary tablespace for user operations
  • How much of the allocated space is currently utilized by the user operation?
  • What percentage of free space is currently available in the temporary tablespace?

Target of the test : An Oracle server

Agent deploying the test : An internal agent

Outputs of the test : One set of results for every SID monitored.

Configurable parameters for the test
  1. TEST PERIOD - How often should the test be executed
  2. Host – The host for which the test is to be configured
  3. Port - The port on which the server is listening
  4. User – In order to monitor an Oracle database server, a special database user account has to be created in every Oracle database instance that requires monitoring. A Click here hyperlink is available in the test configuration page, using which a new oracle database user can be created. Alternatively, you can manually create the special database user. When doing so, ensure that this user is vested with the select_catalog_role and create session privileges.

    The sample script we recommend for user creation (in Oracle database server versions before 12c) for eG monitoring is:

    create user oraeg identified by oraeg

    create role oratest;

    grant create session to oratest;

    grant select_catalog_role to oratest;

    grant oratest to oraeg;

    The sample script we recommend for user creation (in Oracle database server 12c) for eG monitoring is:

    alter session set container=<Oracle_service_name>;

    create user <user_name>identified by <user_password> container=current default tablespace <name_of_default_tablespace> temporary tablespace <name_of_temporary_tablespace>;

    Grant create session to <user_name>;                                

    Grant select_catalog_role to <user_name>;

    The name of this user has to be specified here.

  5. Password – Password of the specified database user

    This login information is required to query Oracle’s internal dynamic views, so as to fetch the current status / health of the various database components.

  6. Confirm password – Confirm the password by retyping it here.
  7. alternate view – In large environments, where the volume of transactions to the Oracle database server is generally very high, this test may take time to execute and retrieve the desired results. To ensure that the test is faster and is resource-efficient, administrators of such environments can create an alternate ‘view’ on the target Oracle database server, and grant select privileges to the view to the special database user mentioned above. Once the view is created, the test should be configured to use the alternate view for metrics collection; to achieve this, specify the name of the view in the alternate view text box. By default, this text box is set to none, which implies that the alternate view is not used by default.

    This alternate ‘view’ should be created with the following structure:

CREATE OR REPLACE VIEW <VIEW_NAME> (
TABLESPACE_NAME,
FILE_ID,
BLOCK_ID,
BYTES,
BLOCKS,
RELATIVE_FNO
) AS
select /*+ use_hash (tsfi, fet2) */ tsfi.tablespace_name,
       tsfi.file_id,
       fet2.block_id,
       tsfi.blocksize * fet2.blocks,
       fet2.blocks,
       tsfi.relfile#
from   (select /*+ use_hash (ts, fi) */ ts.name tablespace_name,
               fi.file# file_id,
               ts.BLOCKSIZE,
               fi.relfile#,
               ts.ts#
        from   sys.ts$ ts,
               sys.file$ fi
        where  ts.ts# = fi.ts#
        and    ts.online$ in (1,4)) Tsfi,
       (select f.block# block_id,
               f.length blocks,
               f.file# file_id,
               f.ts#
        from   sys.fet$ f
        union all
        select f.ktfbfebno block_id,
               f.ktfbfeblks blocks,
               f.ktfbfefno,
               ktfbfetsn
        from   sys.x$ktfbfe f) Fet2
where  fet2.file_id = tsfi.relfile#
and    fet2.ts# = tsfi.ts# ;

  1. ISPASSIVE – If the value chosen is yes, then the Oracle server under consideration is a passive server in an Oracle cluster. No alerts will be generated if the server is not running. Measures will be reported as “Not applicable'' by the agent if the server is not up.
Measurements made by the test
Measurement Description Measurement Unit Interpretation

Total size:

Indicates the total size of the temporary tablespace.

MB

 

Allocated size:

Indicates the space that is currently allocated for user operations (for e.g., sorting) from the temporary tablespace.

MB

When a user operation is initiated in the Oracle database, the whole of the temporary tablespace is not utilized for that operation. Instead, the database allocates a considerable amount of tempfiles from the temporary tablespace to perform such operations.

Used allocated space:

Indicates the space that is currently utilized by user operations from the allocated size of the temporary tablespace.

MB

 

Free allocated space:

Indicates the free space that is still available for use in the allocated size of the temporary tablespace.

MB

Ideally, the value of this measure should be high.

Total free space:

Indicates the percentage of free space that is currently available in the temporary tablespace.

Percentage

The value of this measure is calculated using the formula: (Total_Free_Size/Total_Size)*100.

If the value of this measure is low, it indicates that the temporary tablespace is running out of space which will eventually lead to the failure of critical operations such as sorting and execution of hash-intensive queries. To avoid such failures, the size of the temporary tablespace should be increased. You can increase the size by just adding datafiles to the temporary tablespace using the ADD TEMPFILE command or through the Add Datafiles option from the Oracle Enterprise Manager.

If free space is not available in the temporary tablespace, an error message stating - “ORA-1652: unable to extend temp segment” will appear.