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.
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# ;
|
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. |