isitdevops.com/databases

Process to replace a small files temporary tablespace with a bigfile temp tablespace in asm.

 

create temporary tablespace temp_temp tempfile size 31g;

alter database default temporary tablespace temp_temp;

SELECT a.tablespace, a.segfile#, a.segblk#, a.blocks, b.sid, b.serial#, b.username, b.osuser, b.status
FROM v$session b,v$sort_usage a WHERE b.saddr = a.session_addr;

 

alter system kill session ‘SID_NUMBER, SERIAL#NUMBER’;

drop tablespace temp including contents and datafiles;

create bigfile temporary tablespace temp tempfile size 220G autoextend on maxsize 240g;

alter database default temporary tablespace temp;

drop tablespace temp_temp including contents and datafiles;