reduce temp tablespace datafile size

[Beantree]

My Temp tablespace uses a single datafile that has grown to almost 9 Gb.

I want to reduce the datafile size, but when I try to I always get ora-03297 File contains used data beyond resize value.

If I restart the DB, can do it, but I would like to do it while the DB is online.

The actual tablespace is only using 800 Mb of space.

Please advise.

 

[karluk]

If you want to keep the database up while you resize, you will probably have to create another temporary tablespace and and make it the temporary tablespace for all users. Then you should be able to take the original tablespace offline and reduce the file size. When you are finished, you can reassign everyone to the original tablespace and drop the new one.

 

[Beantree]

If I try that I get ora-01135 file 4 accessed for DML/query is offline.

 

[karluk]

This is a standard way of dealing with this issue. Are you sure that you altered all user ids so that no one is still trying to access the original temporary tablespace? Maybe you missed someone.

Another possibility is that you have to wait for current sessions to exit before taking the tablespace offline, but I'm unable to find any documentation that it's necessary to wait.

 

[psyco]

when u do alter users to use the new temp tablespace, you will have to allow for current processes to stop.
one of the ways to check which users are using temp tablespce is this script...

select b.tablespace
,b.segfile#
,b.segblk#
,round(((b.blocks*p.value)/1024/1024),2) size_mb
,a.sid
,a.serial#
,a.username
,a.osuser
,a.program
,a.status
from v$session a
,v$sort_usage b
,v$process c
,v$parameter p
where p.name='db_block_size'
and a.saddr = b.session_addr
and a.paddr=c.addr
order by b.tablespace,b.segfile#,b.segblk#,b.blocks;
--I didn't come up with this script , received this thru email from a friend who says they got it off the net.