temp tablespace problem

[Seenivas]

Hi All,

OS : NT 4
DB : Oracle 8.1.5

We have temp tablespace which is occupied more space in our db.Can anyone help us to clear the temp tablespace.if we remove this, will it be a problem for our db.

pls help.

[SANJAY_G]

You can drop and recreate the TEMP tablespace if there are no active transactions using TEMP tablespace.
Or simply bounce the instance if possible.

[adewri]

Make sure no one has the TEMP tablespace as their default tablespace.

1. make the tablespace offline.
2. drop it
3. recreate it with smaller datafiles.

[Orca777]

Hi,
The top of your Temp-Ts allocates Blocks is the High-Water-Mark.
This means that the place could be free for new transaction even it is allocated.
You can monitor which SQL uses the temp-Ts actuall.
Recreating Temp will not help you.
You have to identify and tune the SQL's which fill the Temp-Ts

Orca

SELECT /*+ORDERED*/su.tablespace,
se.sid,
se.user#,
se.serial#,
su.blocks,
su.segtype,
sa.sql_text
FROM v$sort_usage su
,v$sqlarea sa
,v$session se
WHERE su.session_addr = se.saddr
AND se.sql_address = sa.address(+)
/

[SANJAY_G]

Originally posted by adewri
Make sure no one has the TEMP tablespace as their default tablespace.

It doesn't matter, they can not create any object in TEMP tablespace anyway.

[Seenivas]

Dear Mr.Sanjay,

Thanx for ur info and i would like to know which table occupies more space on the tablespace.

[ksridhar]

select segment_name,tablespace_name,sum(bytes/(1024*1024)) "Table Size in MB"
from user_segments
where tablespace_name=''
group by segment_name,tablespace_name
order by sum(bytes/(1024*1024)) desc

This query would help u in finding the Segment Sizes

[SANJAY_G]

Originally posted by Seenivas
Thanx for ur info and i would like to know which table occupies more space on the tablespace.

You can't create any permanent segment in TEMP tablespace so there is no question of having tables there. TEMP tablespace have segments used for sorting the data.
If you want to monitor the space usage by table in permanent tablespaces then you can use the query Sridhar has provided.
HTH