Skip to content
Home » Oracle Database » Alter User Default Temporary Tablespace Examples

Alter User Default Temporary Tablespace Examples

Default Temporary Tablespace

Normally, all users in the database use the same default temporary tablespace except that we specifically set another one for them.

In this post, we'll talk about the way to set a different and newly created temporary tablespace to an user.

Let's check the current temporary tablespace for the user.

SQL> select temporary_tablespace from dba_users where username = 'HR';

TEMPORARY_TABLESPACE
------------------------------
TEMP

Before you set the temporary tablespace for the user, you should make sure the tablespace you're going to specify does exist.

Then we alter the user's attribute of default temporary tablespace as below.

SQL> alter user hr temporary tablespace temp1;

User altered.

We check the result.

SQL> select temporary_tablespace from dba_users where username = 'HR';

TEMPORARY_TABLESPACE
------------------------------
TEMP1

We have changed the default temporary tablespace of the user.

To change the default temporary tablespace of the entire database, we should do it at database level.

Leave a Reply

Your email address will not be published. Required fields are marked *