List users who are using permanent tablespace instead of temporary tablespace

— If sorting is performed on permanent tablespace it can affect performance because it’s in nologging mode, reduces backup and recovery as it doesn’t get backed-up

SELECT username, temporary_tablespace, b.contents
FROM dba_users a, dba_tablespaces b
WHERE a.temporary_tablespace = b.tablespace_name
AND b.contents ‘TEMPORARY’;

— Using the following ALTER statement one can modify user’s temporary tablespace
SQL> ALTER USER <USER> TEMPORARY TABLESPACE <temporary tablespace name>;

Leave a Reply