how to check default tablespace in Oracle database

Default tablespace

• Default tablespace are the oracle tablespace where the objects like Oracle table, oracle index are created by the user when no tablespace name is specified in the object creation clause.
• We can define both the Permanent and temporary tablespace for the users
• With 9i , you can specify default permanent and default temp tablespace at database creation time and after the database is created also
• Each oracle user created will have the same default Permanent and temporary tablespace as specified at the database level
• You can specify different for user by specifying at the different one at the user creation time

How to find the default tablespace at the Database level

Values of the current setting of the Default can be found using the below queries

COLUMN property_name FORMAT A30
 COLUMN property_value FORMAT A30
 COLUMN description FORMAT A50
 SET LINESIZE 200
 SELECT *
 FROM database_properties
 WHERE property_name like '%TABLESPACE';
 PROPERTY_NAME           PROPERTY_VALUE                 DESCRIPTION
 ------------            -------------                    ---------
DEFAULT_PERMANENT_TABLESPACE   USERS   Default Permanent Tablespace ID
DEFAULT_TEMP_TABLESPACE        TEMP     default temporary tablespace

We can find the individual one using the below queries

SQL> SELECT property_name,property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
 PROPERTY_NAME                  PROPERTY_VALUE
 -------------                  ---------------
 DEFAULT_TEMP_TABLESPACE        TEMP
 
SQL> SELECT property_name,property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';
 PROPERTY_NAME                  PROPERTY_VALUE
 -------------                  --------------
 DEFAULT_PERMANENT_TABLESPACE   USERS

When users are created, and no default permanent and default temp tablespace is given, then users defaults permanent and temp tablespace are set to these settings

How to change the settings

We should have the new tablespace existing and then we can use the below command

alter database default temporary tablespace temp2;
alter database default permanent tablespace user2;

SQL> SELECT property_name,property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
 PROPERTY_NAME                  PROPERTY_VALUE
 ------------                   -------------
 DEFAULT_TEMP_TABLESPACE        TEMP2
 SQL> SELECT property_name,property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';
 PROPERTY_NAME                  PROPERTY_VALUE
 -------------                   ------------
 DEFAULT_PERMANENT_TABLESPACE   USERS2

Warning /Caveat

This command sets the default permanent tablespace and temporary tablespace for all the users irrespective of their previous settings. What it means, if some user was created with some different permanent tablespace and temporary tablespace, then that user will also revert to the new default permanent tablespace and temporary tablespace. So it will be better if we can store the default tablespace of all the users in some backup table before changing it. And then you can do the comparison and change for the users where it is required

create table before_tab_permanent select username,default_tablespace from dba_users where default_tablespace not in (SELECT property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_PERMANENT_TABLESPACE';

create table before_tab_temporary select username,TEMPORARY_TABLESPACE from dba_users where TEMPORARY_TABLESPACE not in (SELECT property_value FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';

spool change_tablespace_user.sql
select 'alter user'|| username||' default tablespace'|| default_tablespace ||';' from before_tab_permanent;
select 'alter user'|| username||' TEMPORARY tablespace'|| TEMPORARY_TABLESPACE||';' from before_tab_temporary;
spool off

@change_tablespace_user.sql

How to check the default tablespace for the particular User

select username,default_tablespace,TEMPORARY_TABLESPACE from dba_users where username = '&1';

How to change the default tablespace for the particular User

alter user <username> default tablespace <tablespace_name>;
alter user <username> temporary tablespace <tablespace_name>;
Verify the Results
select username,default_tablespace,TEMPORARY_TABLESPACE from dba_users where username = '&1';

Hope you like the content on how to check default tablespace in the Oracle database. Please do provide the feedback.

Also Reads
Oracle Database Administration Tutorials
https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_4174.htm#REFRN23302
Supplemental Logging in Oracle
Query to find object dependencies in oracle
JSON in Oracle database