Toad for Oracle Editor – How can I use TOAD to create and run dynamic SQL?

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;