set echo off set heading off set feedback off SPOOL c:\temp\all_tmp.sql select 'alter user ' || username || ' temporary tablespace TEMP;' from dba_users where temporary_tablespace != 'TEMP'; SPOOL off @c:\temp\all_tmp.sql
You could then save your dynamic SQL script to the Script Manager. You would then always have this query available.
Alternatively and without the need to spool out sql, try this :
begin for x in (select username from dba_users where temporary_tablespace != 'TEMP') loop execute immediate 'alter user '||x.username||' temporary tablespace temp'; end loop; end;