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

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.