Cleanup of TEMPORARY segments

In this short blog post, I will explain how to get rid of TEMPORARY segments in a permanent tablespace.

What is a TEMPORARY segment?

When you hear the term TEMPORARY, you might find about segments in a temporary tablespace, for example when you join tables or perform a sort operation that does not fit into the memory.

But such segments can also exist in a permanent tablespace. Such a segment is created, when you perform one of the following operations.

• ALTER TABLE … MOVE
• CREATE TABLE … AS SELECT
• CREATE INDEX …
• ALTER INDEX … REBUILD

During these operations, a TEMPORARY segment is created. When the operation terminates unexpectedly, these segments are not cleaned up.

Identify TEMPORARY segments

With the help of the following queries, TEMPORARY segments can be identified for a specific tablespace. The first query reads the information from DBA_SEGMENTS.

SQL> SELECT segment_name, segment_type, header_file, relative_fno, header_block, 
            bytes/POWER(1024, 2) AS "SIZE_MB"
       FROM dba_segments
      WHERE tablespace_name = 'USERS'
            AND segment_type = 'TEMPORARY';

SEGMENT_NAME         SEGMENT_TYPE         HEADER_FILE RELATIVE_FNO HEADER_BLOCK    SIZE_MB
-------------------- -------------------- ----------- ------------ ------------ ----------
395.159978           TEMPORARY                    395            4       159978          4
395.605266           TEMPORARY                    395            4       605266      20454

The naming convention of the segment name is <Header File ID>.<Header Block ID>.

As an alternative to DBA_SEGMENTS, the table SEG$ can be quiered directly.

SQL> SELECT file#, block#, type#, ts# 
       FROM seg$ 
      WHERE type#=3 
            AND ts# = (SELECT ts# FROM v$tablespace WHERE name = 'USERS'); 

     FILE#     BLOCK#      TYPE#        TS#
---------- ---------- ---------- ----------
         4     159978          3          6
         4     605266          3          6

TEMPORARY segments occupy space in the tablespace and should be cleaned up.

Cleanup of TEMPORARY segments

Normally the user process who created the TEMPORARY segment is responsible for its cleanup (e.g. convert the TEMPORARY segment to an INDEX segment). For sort operations or if the session was terminated, SMON performs a cleanup of these segments. But it seems that SMON does not drop all “types” of TEMPORARY segments – bug or expected behavior?!

PL/SQL package DBMS_SPACE_ADMIN is used to clean up TEMPORARY segments manually. In the first step, the segment 395.605266 is marked as corrupt. The relative file number is required at this point – use the value of column RELATIVE_FNO.

SQL> BEGIN
   DBMS_SPACE_ADMIN.SEGMENT_CORRUPT(
      tablespace_name => 'USERS', 
	  header_relative_file => 4, 
	  header_block => 605266,
	  corrupt_option => DBMS_SPACE_ADMIN.SEGMENT_MARK_CORRUPT
   );
END;
/

After the segment is marked as corrupt, it can be dropped.

SQL> BEGIN
   DBMS_SPACE_ADMIN.SEGMENT_DROP_CORRUPT(
      tablespace_name => 'USERS', 
	  header_relative_file => 4, 
	  header_block => 605266
   );
END;
/

At this point the segment is dropped, but its occupied space is not visible in DBA_FREE_SPACE.

SQL> SELECT SUM(bytes)/POWER(1024,3) AS "FREE_SPACE" 
       FROM dba_free_space 
      WHERE tablespace_name = 'USERS';

FREE_SPACE
----------
337.322449

As final step, a rebuild of the bitmaps of the tablespace is required.

SQL> BEGIN
   DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS(
      tablespace_name => 'USERS'
   );
END;
/

Now the free space increases by the size of the dropped segments.

SQL> SELECT SUM(bytes)/POWER(1024,3) AS "FREE_SPACE" 
       FROM dba_free_space 
      WHERE tablespace_name = 'USERS';

FREE_SPACE
----------
357.300964

References

• Temporary Segments In Permanent Tablespaces Aren’t Cleaned For A Long Time (Doc ID 1271120.1)
• How to clear a block corruption in a TEMPORARY segment (Doc ID 1332088.1)