Shrink temporary tablespace

Der schnellste Weg, einen temporären Tablespace zu verkleinern, ist, einen neuen temporären TS anzulegen, alle User des alten auf den neuen umzuleiten und den alten zu droppen. Da die Extents eines temporären Tablespaces erst beim Herunterfahren der Instanz freigegeben werden, läuft ein SHRINK oder RESIZE in der Regel auf Fehler.

Also funktioniert in 10g folgendes:

-- Anlage eines neuen temporären TS
create temporary tablespace temp1 tempfile …;

-- Generierung von Queries, mit denen allen DB-User 
-- der neue temporären TS zugeordnet wird
select 'alter user ' || username || ' temporary tablespace temp1;'
  from dba_users
 where temporary_tablespace = 'TEMP';

-- den neuen temporären TS als default TS setzen
alter database default temporary tablespace temp1;

-- alle Sessions beenden, die noch Einträge in v$sort_usage halten

-- jetzt kann man den alten temporären TS wegwerfen
drop tablespace temp;

alter tablespace temp1 rename to temp;

Nachtrag 16.05.2011: In 11.2.0.1 funktioniert das Vorgehen immer noch (was nicht allzu sehr überrascht)

Nachtrag 23.03.2012: Tom Kyte weist dieser Tage darauf hin, dass das Verkleinern in 11g deutlich einfacher geworden ist - es genügt ein "alter tablespace temp_xyz shrink space".