This content has been marked as final. Show 5 replies
Temp tablespaces are not released automatically.
Storage in TEMP Tablespace is not automatically released
Create another temporary tablespace and assign it as default temporary tablespace and drop the old one.
How do you know there is anything in the temporary tablespace that needs to be 'clear'ed? Oracle allocates temporary segments to the temporary tablespace, but does not deallocate them when they are no longer needed. Instead they are freed so that other transactions can use them. This saves recursive calls to the data dictionary to allocate and later deallocate temporary segments. It may appear that the temporary tablespace is full, but this is not necessarily the case. If you want to see how temp space is being used at any particular moment, try a query something like this
COLUMN user FORMAT a20 HEADING 'User'
COLUMN username FORMAT a10 HEADING 'Username'
COLUMN osuser FORMAT a12 HEADING 'OS User'
COLUMN sid_ser FORMAT a10 HEADING 'SID,SER'
COLUMN blk_mb FORMAT 99,999 HEADING 'Temp MB'
COLUMN sorts FORMAT 9,999 HEADING 'Sorts'
COLUMN sql_text FORMAT a40 word_wrapped HEADING 'SQL Text'
COLUMN process FORMAT a35 word_wrapped HEADING 'Module:Action'
COLUMN tablespace NOPRINT new_value ts_name
BREAK ON sid_ser SKIP 1 DUPLICATES
BREAK ON tablespace -
COMPUTE SUM OF blk_mb on tablespace report
TTITLE Center 'Current Temp Usage' -
skip Center '~~~~~~~~~~~~~~~~~~' -
skip 1 Left 'Temp Tablespace: ' ts_name -
skip 2 -
SELECT /*+ ORDERED */
s.sid || ',' || s.serial# as sid_ser,
(SUM(u.blocks)*TO_NUMBER(a.value))/1048576 as blk_mb,
s.module || ':' || chr(10) ||'. ' || s.action as process
FROM v$sort_usage u
WHERE s.saddr = u.session_addr
AND s.sql_address = x.address
AND s.sql_hash_value = x.hash_value
AND a.name = 'db_block_size'
create temporary tablespace TEMP2 tempfile '/path/filename' size 500m;
alter database default temporary tablespace TEMP2;
drop tablespace TEMP;
and put a limit on the files capacity
alter database tempfile '/path/filename' autoextend on next 10m maxsize 2000m;
What's your Oracle version?
Instead of asking how, can you elaborate a little bit as to why you want to clear the temporary tablespace?
Maybe you don't need to care about it？