Today in this article, we will learn how to check default tablespace for user in Oracle and then how to change it. Let’s go through the steps :
Table of Contents
Step 1: Check the default tablespace
Issue the command :
select username, default_tablespace from dba_users where username = ‘JOHN’;
select username, default_tablespace from dba_users where username = 'JOHN';
USERNAME DEFAULT_TABLESPACE
——– ——————
JOHN SYSAUX
Step 2: Change the user’s Default tablespace :
Issue the alter command with the desired tablespace name.
Syntax :
alter user JOHN default tablespace <desired_tablespace_name>;
Example :
SQL> alter user JOHN default tablespace USERS;
User altered.
Step 3: Verify
Issue the same command for Step1 to verify again
select username, default_tablespace from dba_users where username = 'JOHN';
USERNAME DEFAULT_TABLESPACE
——– ——————
JOHN USERS
So, here are the three simple and easy clear steps to check default tablespace for user in Oracle and to change it as well. Hope this helps!!
Also, you can get more details on from the Oracle Forum as well.
Other Oracle-related Articles :
