Reorganize temporary segment ??

[Chris Fung]

Hi all,

When I run the query "select segment_name, count(*) from user_extents group by segment_name;" I found that there is a strange output which is just a number showing in the segment_name field. Then I issue "select object_name, object_type from user_objects where object_name='The segment_name I found in the previous query';" I found that the object_type is temporary !! Just want to know what happened to my system?? How to remove the temporary segment ??

Please help,

Chris,

[Brian Crabtree]

That is a normal temporary segment, and owned to the 'SYS' user. You shouldn't worry about it, as it should only be in a tablespace that is used for your sorts (normally TEMP).

This will be cleaned up on a shutdown.

Brian

[Aashish Raj · Accepted solution]

These temporary segments are used for saving the sort results when the sort_area_size allocated in the memory is not sufficients to perform the sorts.
If you see too many extents for this segments(>50), then you should change the next extent of the temporary tablepace holding that temporary segment.Otherwise the shutdown of database will take a lot of time becuase SMOn process needs to clean up these temporary segments before the DB can be shutdown.

Thanks
AR

[Chris Fung]

Thanks for advise, one more question.

Is that in the tablespace can be extended automatically in Oracle8i ? Cause the System default "Temp" tablespace of my machine grew up to 4GB something !!

I am afraid if the Temp tablespace will grow and may cause file system full in Unix level and that may halt the database. Is that something I can do to prevent this from happen ?

Thanks,
Chris,

[Thierry Poels_1]

Hi,

datafiles of a tablespace can only grow automatically if autoextend has been defined for this datafile.
I opt to prevent autoextend for temp and rollback segments: any crazy SQL statement might cause temp and/or rollback to grow beyond control.


FYI:
If the TEMPORARY TABLESPACE is of type TEMPORARY, the space is not released until the instance is shutdown. If the TEMPORARY TABLESPACE is of type PERMANENT, then cleanup is performed by SMON after completion of the process using it.

regards,
Thierry.

[Simeon Fox]

Thierry is correct, you will not have a problem at unix level if auto extend has not been enabled. Without auto-extend, if a rogue sql statement consumes all available temp tablespace, session will generate "ORA-01652 unable to extend temp segment" error, and statement will rollback.

[Aashish Raj]

I keep autoextensible option disabled for all my datafile.This gives me more control over filesystem usage.
I have script which monitors the free space in my tablespace and alerts me when it goes down below a certain threshold.This way i can closely monitor the segment growth in all the tablespaces.

You can closely monitor the temporary segment usage through following views.
v$sort_segment
v$sort_usage
v$sysstat-name in sorts(memory) and sorts(disk).

If you have lot of free memory, then you can increase the sort_area_size to reduce the number of sorts in disks.

For sort intensive operations like create index,analyze you can set a higher sort_area_size at session level to redice the disk level sort usage.
alter session set sort_area_size=25000000;

Thanks
AR



thanks
AR