DOITLIKEITSYOURJOB - Temp Space

SET LINESIZE 200COLUMN TABLESPACE_NAME FORMAT A30COLUMN TOTAL_SIZE_MB FORMAT 99999COLUMN USED_SPACE_MB FORMAT 99999COLUMN FREE_SPACE_MB FORMAT 99999
WITH TempSpace AS ( SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024, 2) AS FREE_SPACE_MB FROM DBA_TEMP_FREE_SPACE GROUP BY TABLESPACE_NAME),TempTotal AS ( SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / 1024 / 1024, 2) AS TOTAL_SIZE_MB FROM DBA_TEMP_FILES GROUP BY TABLESPACE_NAME)SELECT t.TABLESPACE_NAME, tt.TOTAL_SIZE_MB, (tt.TOTAL_SIZE_MB - ts.FREE_SPACE_MB) AS USED_SPACE_MB, ts.FREE_SPACE_MBFROM TempTotal ttJOIN TempSpace tsON tt.TABLESPACE_NAME = ts.TABLESPACE_NAMEORDER BY TABLESPACE_NAME;

Temp Table Space

SELECT    s.sid,    s.serial#,    s.username,    s.program,    s.status,    s.osuser,    su.tablespace,    su.contents,    su.blocks * TO_NUMBER(r.VALUE) / 1024 / 1024 AS temp_mb,    su.segtype,    s.sql_id,    sq.sql_textFROM    v$session s    JOIN v$sort_usage su ON s.saddr = su.session_addr    JOIN v$parameter r ON r.name = 'db_block_size'    LEFT JOIN v$sql sq ON s.sql_id = sq.sql_idORDER BY    temp_mb DESC;
--2 Query to check total TEMP space and available spaceSELECT    tablespace_name,    ROUND(SUM(bytes) / (1024 * 1024), 2) AS total_mb,    ROUND(SUM(free_space) / (1024 * 1024), 2) AS free_mb,    ROUND((SUM(bytes) - SUM(free_space)) / (1024 * 1024), 2) AS used_mbFROM (    SELECT        tablespace_name,        bytes,        0 AS free_space    FROM        dba_temp_files    UNION ALL    SELECT        tablespace_name,        0 AS bytes,        BLOCKS_FREE * TO_NUMBER(r.value) AS free_space    FROM        v$temp_space_header,        v$parameter r    WHERE        r.name = 'db_block_size')GROUP BY    tablespace_name;

-- Query to check total TEMP space and available spaceSELECT    tablespace_name,    ROUND(SUM(bytes) / (1024 * 1024), 2) AS total_mb,    ROUND(SUM(free_space) / (1024 * 1024), 2) AS free_mb,    ROUND((SUM(bytes) - SUM(free_space)) / (1024 * 1024), 2) AS used_mbFROM (    SELECT        tablespace_name,        bytes,        0 AS free_space    FROM        dba_temp_files    UNION ALL    SELECT        tablespace_name,        0 AS bytes,        BYTES_FREE AS free_space    FROM        v$temp_space_header)GROUP BY    tablespace_name;

/*Explanation:1. The first query provides details about the active sessions consuming TEMP space.2. The second query provides details about the TEMP tablespace usage:    - `tablespace_name`: Name of the temporary tablespace.    - `total_mb`: Total size of the TEMP tablespace in MB.    - `free_mb`: Available space in the TEMP tablespace in MB.    - `used_mb`: Used space in the TEMP tablespace in MB.*/

Fix Temp SPACE

If the TEMP tablespace is full or almost full, here are some steps you can take to free up TEMP space in Oracle. These commands and actions will help clean up temporary segments and release space without restarting the database.

1. End Inactive Sessions Holding TEMP Space

Sometimes, sessions can hold on to TEMP space even after they are no longer active. You can identify and terminate these sessions if they are no longer needed.

Step 1: Identify Sessions Using TEMP Space

Run this query to identify sessions currently using TEMP space:

sql

Copy code

SELECT

s.sid,

s.serial#,

s.username,

u.tablespace,

u.blocks * TO_NUMBER(p.value) / (1024 * 1024) AS used_mb

FROM

v$session s,

v$sort_usage u,

v$parameter p

WHERE

s.saddr = u.session_addr

AND p.name = 'db_block_size'

ORDER BY used_mb DESC;

Step 2: Kill Inactive Sessions

If you find inactive sessions that are holding onto a large amount of TEMP space, you can terminate them using:

sql

Copy code

ALTER SYSTEM KILL SESSION '<sid>,<serial#>';

Replace <sid> and <serial#> with the session ID and serial number from the previous query. Killing inactive sessions should release their TEMP space.

2. Force a Checkpoint to Clear TEMP Space

Running a checkpoint can help Oracle reclaim unused TEMP space. This does not directly clear TEMP but may allow Oracle to release some temporary segments that were being held unnecessarily.

sql

Copy code

ALTER SYSTEM CHECKPOINT;

3. Shrink TEMP Tablespace (If Autoextend is Enabled)

If your TEMP tablespace is set to autoextend and has grown, you may want to resize it to free up unused space.

Step 1: Resize TEMP Tablespace

First, identify the current size and used space in TEMP, and determine the target size.

sql

Copy code

SELECT

tablespace_name,

file_name,

bytes / (1024 * 1024) AS current_size_mb

FROM

dba_temp_files

WHERE

tablespace_name = 'TEMP';

Step 2: Shrink TEMP Files

Once you know the size you want to shrink to, use the following command to resize the TEMP file. For example, if you want to resize it to 5 GB:

sql

Copy code

ALTER DATABASE TEMPFILE '<file_path>' RESIZE 5000M;

Replace <file_path> with the actual file path of the TEMP file from the previous query. Repeat this for each TEMP file if you have multiple.

4. Recreate TEMP Tablespace

If TEMP space issues persist and none of the above methods work, you can recreate the TEMP tablespace. This process involves creating a new TEMP tablespace, switching to it, and then dropping the old TEMP tablespace.

Step 1: Create a New TEMP Tablespace

sql

Copy code

CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '<path_to_tempfile>'

SIZE 5G REUSE

AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

Replace <path_to_tempfile> with a valid file path for your system. Adjust the initial SIZE and AUTOEXTEND settings as needed.

Step 2: Assign TEMP_NEW as Default

sql

Copy code

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

Step 3: Drop the Old TEMP Tablespace

Make sure there are no active sessions using the old TEMP tablespace before dropping it.

DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES;

Step 4: Rename the New TEMP Tablespace (Optional)

If you want the new tablespace to be named TEMP, you can drop the old TEMP and rename TEMP_NEW.

ALTER TABLESPACE TEMP_NEW RENAME TO TEMP;

Summary

Kill inactive sessions using TEMP space.
Run a checkpoint to release TEMP segments.
Shrink TEMP tablespace if autoextend has grown it too large.
Recreate the TEMP tablespace as a last resort.

These actions should help in freeing up TEMP space in your Oracle database. Be cautious with these steps, especially when terminating sessions and recreating tablespaces, as they can impact database