Temp Tablespaces

TO RESIZE TEMP TABLESPACE USE BELOW STEPS.


Step 1) CHECK IF ANYBODY IS USING TEMP TABLESPACE

select b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
from v$session a,v$sort_usage b
where a.saddr = b.session_addr;      

( some below queries can be used to check usage but above is reliable)

select o.owner ,o.object_name ,o.object_type
from sys_objects s ,dba_objects o ,dba_data_files df
where df.file_id = s.header_file
and o.object_id = s.object_id
and df.tablespace_name = 'TABLESPACE_NAME';

select  a.tablespace_name tablespace, d.mb_total,
sum (a.used_blocks * d.block_size) / 1024 / 1024 mb_used,
d.mb_total - sum (a.used_blocks * d.block_size) / 1024 / 1024 mb_free
from v$sort_segment a,
(select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 mb_total
from v$tablespace b, v$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.tablespace_name, d.mb_total;



Step2 ) CREATE NEW TABLESPACE AND SWITCH USERS TO THIS TABLESPACE AND DROP TEMP TABLESPACE IF NO USERS ARE USING.

create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/temp02.dbf' size 2G autoextend on next 1M;

alter database default temporary tablespace temp2;

---- Switch all existing users to new temp tablespace.

begin
for cur_user IN (select username from dba_users where temporary_tablespace = 'TEMP') loop
execute immediate 'alter user'
cur_user.username
'temporary tablespace temp2';
end loop;
end;
/

( check if anybody is using temp tablespace using queries in step1 and if not then drop)


drop tablespace temp including contents and datafiles;