Default tablespace for User in Oracle

Change default tablespace for User in Oracle

Check default tablespace for user

col username for a8
col default_tablespace for a18
col temporary_tablespace for a20
select username,default_tablespace,temporary_tablespace from dba_users;

USERNAME DEFAULT_TABLESPACE  TEMPORARY_TABLESPACE
-------- ------------------  ---------------------
SYS      SYSTEM              TEMP
SYSTEM   SYSTEM              TEMP
HR       USERS               TEMP

Change user default tablespace

SQL> alter user hr default tablespace sysaux;
User altered.

Change temp default tablespace

SQL> alter user hr temporary tablespace temp2;
User altered.

Check Default tablespace for Instance
When any user created without specify the tablespace clause then it assigned to default tablespace.

col property_name for a30
col property_value for a15
SELECT property_name,property_value FROM database_properties WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ---------------
DEFAULT_PERMANENT_TABLESPACE   USERS

Change the default tablespace for user data

alter database default tablespace users;

Check default TEMP tablespace

col property_name for a30
col property_value for a15
SELECT property_name,property_value FROM database_properties WHERE property_name = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE
------------------------------ ---------------
DEFAULT_TEMP_TABLESPACE        TEMP

Advertisements

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 )

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.