Help - Search - Members - Calendar
Full Version: Temp Tablespace Not de-allocating
Oracle DBA Forums > Oracle > Oracle Forum
sandeep_verma
Hi all,

We all know that Temp segments in Temporary tablespace don't deallocate even after the process completes, the extents are only marked for reuse.

After an instance restart, it should de-allocate, or what is the default behavior?

Doesn't SMON de-allocate temporary segments here during instance startup?

If it doesn't de-allocate space then it will run out of space, the next time a huge sort operation takes place.

So my next question is how to estimate the correct space of the temp tablespace in the physical design phase.?

CODE
CREATE TEMPORARY TABLESPACE temp02
   TEMPFILE ' /u01/app/oracle/oradata/ora11gR2/temp02.DBF' SIZE 100M
   AUTOEXTEND ON NEXT 2M MAXSIZE 500M
   EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;


CODE
SQL> SELECT * from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME            TABLESPACE_SIZE ALLOCATED_SPACE    FREE_SPACE
------------------------------ ---------------             ---------------                   ----------
TEMP                      30408704          30408704               29360128 – Default for system
TEMP02                  104857600           1048576                     103809024

SQL> select tablespace_name, file_id, bytes_used, bytes_free from v$temp_space_header;

TABLESPACE_NAME          FILE_ID   BYTES_USED                  BYTES_FREE
------------------------------ ----------   ----------                             ----------
TEMP                            1             30408704                       0
TEMP02                    2            1048576              103809024

    Created a user ‘test’ and assigned TEMP02 as its temporary tablespace.

After running sort (a table containing 20 Million records from User test (session 1)

Sys> SQL> select tablespace_name, file_id, bytes_used, bytes_free from v$temp_space_header;


TABLESPACE_NAME            TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------         ---------------             ---------------        ----------
TEMP02                     104857600          35651584     69206016
TEMP                               30408704          30408704     29360128

After Running sort from User test (session 2) but same table

Sys> SQL> select tablespace_name, file_id, bytes_used, bytes_free from v$temp_space_header;

TABLESPACE_NAME            TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP02                     104857600          70254592     34603008
TEMP                      30408704          30408704     29360128

    Free sort space is reduced and more extents allocated for the sort segment



Sys>
select username, tablespace,contents, segtype, extents, blocks from v$sort_usage;

USERNAME               TABLESPACE               CONTENTS  SEGTYPE      EXTENTS      BLOCKS
------------------------------ ------------------------------- --------- ---------            ---------- ----------
TEST                   TEMP02                  TEMPORARY SORT             33    4224

SQL> select tablespace_name, current_users, total_extents, used_extents, free_extents, added_extents, max_used_size
  2  from v$sort_segment;

TABLESPACE_NAME         CURRENT_USERS TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS ADDED_EXTENTS MAX_USED_SIZE
------------------------------- ------------- ------------- ------------ ------------ ------------- -------------
TEMP                        0         28           0       28          0        1
TEMP02                    1         66          33       33            66            66

After the sort completes;

Sys> SQL> SELECT * from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME                                           TABLESPACE_SIZE         ALLOCATED_SPACE        FREE_SPACE
------------------------------                                                ---------------                    ---------------                 ----------
TEMP02                     104857600          70254592    103809024
TEMP                               30408704          30408704     29360128

    Free space shows the Temp02 extents are free for re-use by another process
    Extents not de-allocated

SQL> select tablespace_name, file_id, bytes_used, bytes_free from v$temp_space_header;

TABLESPACE_NAME             FILE_ID BYTES_USED    BYTES_FREE
------------------------------        ---------- ---------- ----------
TEMP                                   1   30408704          0
TEMP02                           2   70254592       34603008

    Actual space (bytes) free in Temp02
    Does it mean that 34603008 is actually free and 103809024 is marked free

select username, tablespace,contents, segtype, extents, blocks from v$sort_usage;

    No records
    Agreed here



SQL> select tablespace_name, current_users, total_extents, used_extents, free_extents, added_extents, max_used_size from v$sort_segment;

TABLESPACE_NAME         CURRENT_USERS TOTAL_EXTENTS USED_EXTENTS FREE_EXTENTS ADDED_EXTENTS MAX_USED_SIZE
------------------------------- ------------- ------------- ------------ ------------ ------------- -------------
TEMP                        0         28           0       28          0        1
TEMP02                        0         66           0       66    66           66

After a startup of instance;

SQL> SELECT * from DBA_TEMP_FREE_SPACE;

TABLESPACE_NAME            TABLESPACE_SIZE ALLOCATED_SPACE FREE_SPACE
------------------------------ --------------- --------------- ----------
TEMP                      30408704          30408704     29360128
TEMP02                  104857600          70254592     34603008

SQL> select tablespace_name, file_id, bytes_used, bytes_free from v$temp_space_header;

TABLESPACE_NAME           FILE_ID BYTES_USED BYTES_FREE
------------------------------        ---------- ---------- ----------
TEMP                           1   30408704          0
TEMP02                       2   70254592   34603008

Why is the sort segments not de-allocated after instance startup??
Steve
There are a lot of questions here that I can't answer right away, but I would have a look at this link for good information on TEMP allocation: http://www.dba-oracle.com/sf_ora_01652_una...pace_string.htm

This link also has some great tips and good links at the bottom: http://www.dba-oracle.com/t_oracle_sorting.htm
NAVEEN KP
Keep temp tablespace in auto extend mode...
SteveC
QUOTE (NAVEEN KP @ Oct 16 2010, 08:46 PM) *
Keep temp tablespace in auto extend mode...


Because why? So a temp tablespace can cause problems for the entire instance if its datafiles fill up a partition? And what if the temp tablespace for the OP already was autoextend and hit max size? What does autoextend fix in that case? Nothing.
sandeep_verma
Naveen,

My temp tablespace is in autoextend mode with maxsize already. But question is different.
sandeep_verma
QUOTE (Steve @ Oct 15 2010, 11:15 AM) *
There are a lot of questions here that I can't answer right away, but I would have a look at this link for good information on TEMP allocation: http://www.dba-oracle.com/sf_ora_01652_una...pace_string.htm

This link also has some great tips and good links at the bottom: http://www.dba-oracle.com/t_oracle_sorting.htm


Thanks Steve for the tips and links.

Well, what is the default behavior of temp tablespace on instance startup. Say my previous sorting has allocated a lot of extents in the temp tablespace.

I restart the instance, now does it de-allocate automatically the extents or I have to manually resize the temp tablespace?
SteveC
Why don't you see for yourself what happens after bouncing the instance?
sandeep_verma
QUOTE (SteveC @ Oct 19 2010, 09:52 AM) *
Why don't you see for yourself what happens after bouncing the instance?



Well Steve, I have seen for myself and have reported in my supporting doc that its not de-allocating the extents.

But I wanted to ask why do we need such manual intervention of resizing or shrinking.?

SteveC
When you said "now does it de-allocate automatically the extents or I have to manually resize the temp tablespace" I figured you didn't know. If the instance has been bounced, the temp tablespace is wide open. Nothing permanent stored there and all that. Anything left marked doesn't matter as those sessions no longer exist.
sandeep_verma
QUOTE (SteveC @ Oct 20 2010, 09:37 AM) *
When you said "now does it de-allocate automatically the extents or I have to manually resize the temp tablespace" I figured you didn't know. If the instance has been bounced, the temp tablespace is wide open. Nothing permanent stored there and all that. Anything left marked doesn't matter as those sessions no longer exist.



Thanks Steve. I got it.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.