Emptying Temp and undo tablespaces

[manasa]

How would i empty these tablespaces?

When i shutdown and restart the database my temp tbs still shows full. As for undo when the transaction completes execution may empty it, but not by bouncing the db.

[davey23uk]

you dont need to touch them, they will always show as full (or near) full all the time due to online undo / temp segments

[oracli]

You don't need to free the temp tablespace it is done automatically when needed. And the free space might not always be correctly shown so if you don't receove errors that there is not enogh space in it you should not bother.
If the undo tablespace is automatic you also don't need to bother with it. If it is manual you can free space by shirnking the rollback segments in it.

[ebrian]

As the others have indicated, you normally don't have to worry about the TEMP & UNDO tablespaces. However, if you have encountered an abnormal process that has greatly increased the size of these datafiles/tempfiles, then you can shrink them by using the following:

TEMP Tablespace:

-- create a new TEMP tablespace
-- make this new TEMP tablespace the default temporary tablespace for the database
        (ALTER DATABASE DEFAULT TEMPORARY TABLESPACE tempts2;)
-- drop the old TEMP tablespace

UNDO Tablespace:

-- create a new UNDO tablespace
-- switch the database to use the new UNDO tablespace
        (ALTER SYSTEM SET UNDO_TABLESPACE = undotbs_02;)
-- drop the old UNDO tablespace
   
   

[DaPi]

I'd avoid making a temp TS autoextend, especially in development. Forget a join condition and you've a Cartesian product that can blow out the biggest disks. So you should almost never need to shrink them.

[manasa]

thanks all for your time