01. RETRIEVE CURRENT TEMPFILE
=============================

select name, round(bytes/1024/1024) "MB" from v$tempfile;

NAME                                                                      MB
----------------------------------------------------------------- ----------
/app/orcl/oradata/ORCL/datafile/o1_mf_temp_9ryb5zml_.tmp            11925



_________________________________________________________________________________________________________________________________________

02. SAVE DDL OF CURRENT TEMP TABLESPACE
=======================================

set pages 1000
set lines 200
set long 90000
set longchunksize 90000

begin
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE', FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE', FALSE);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SQLTERMINATOR', true);
    DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'REF_CONSTRAINTS', true);
end;
/

select dbms_metadata.get_ddl('TABLESPACE','TEMP') from dual;

DBMS_METADATA.GET_DDL('TABLESPACE','TEMP')
-------------------------------------------------------------

  CREATE BIGFILE TEMPORARY TABLESPACE "TEMP" TEMPFILE
  SIZE 12504260608
  AUTOEXTEND ON NEXT 104857600 MAXSIZE 33554431M
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 4194304;

  
  
_________________________________________________________________________________________________________________________________________

03. CREATE NEW TEMP TABLESPACE
==============================

create bigfile temporary tablespace TEMP2 tempfile size 1g autoextend on;

Tablespace wurde angelegt.

NAME                                                                      MB
----------------------------------------------------------------- ----------
/app/orcl/oradata/ORCL/datafile/o1_mf_temp_9ryb5zml_.tmp            11925
/app/orcl/oradata/ORCL/datafile/o1_mf_temp2_bf1sko9s_.tmp            1024



_________________________________________________________________________________________________________________________________________

04. ACTIVATE NEW TEMP TABLESPACE
================================

alter database default temporary tablespace TEMP2;

Datenbank wurde geändert.



_________________________________________________________________________________________________________________________________________

05. RETRIEVE 'SID' AND 'SERIAL#' OF EXISTING SESSIONS ON OLD 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;


 
_________________________________________________________________________________________________________________________________________

06. KILL RETRIEVED SESSIONS
===========================

select 'alter system kill session ''' || sid || ',' || serial# || ''';' "KILL_SESSION" from v$session;

---> NOTE: THE DATABASE CAN ALSO BE RESTARTED INSTEAD OF RUNNING STEP 05/06
---> NOTE: THE DATABASE CAN ALSO BE RESTARTED INSTEAD OF RUNNING STEP 05/06
---> NOTE: THE DATABASE CAN ALSO BE RESTARTED INSTEAD OF RUNNING STEP 05/06



_________________________________________________________________________________________________________________________________________

07. DROP OLD TEMP TABLESPACE
============================

drop tablespace TEMP including contents and datafiles;



_________________________________________________________________________________________________________________________________________

08. REPEAT STEPS 03-07 TO RE-CREATE A TEMP TABLESPACE WITH THE "OLD" NAME
=========================================================================
create bigfile temporary tablespace TEMP tempfile size 1g autoextend on;
alter database default temporary tablespace TEMP;
shutdown immediate
startup
drop tablespace TEMP2 including contents and datafiles;




_________________________________________________________________________________________________________________________________________

SEE ALSO:
=========
http://dbatricksworld.com/how-to-create-temporary-tablespace-and-drop-existing-temprary-tablespace-in-oracle-11g/
_________________________________________________________________________________________________________________________________________

WORK AREA
_________________________________________________________________________________________________________________________________________

ALTER TABLESPACE TEMP ADD TEMPFILE SIZE 100M;

ALTER TABLESPACE TEMP RESIZE 10G;

ALTER TABLESPACE TEMP SHRINK SPACE KEEP 100M;
ALTER TABLESPACE TEMP SHRINK TEMPFILE '<file_name>';

ALTER TABLESPACE lmtemp TEMPFILE OFFLINE;
ALTER TABLESPACE lmtemp TEMPFILE ONLINE;