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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s