— Set default tablespace for all users, so when users are created without specifying default tablespace it will be user’s default tablespace
SQL> ALTER DATABASE DEFAULT TABLESPACE abc;
Database altered.
— To find the default tablespace name for the database is
SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = ‘DEFAULT_PERMANENT_TABLESPACE’;
PROPERTY_VALUE
——————
ABC
Once when default tablespace is set for the database it can’t be dropped until it’s changed to a different tablespace otherwise one will get ORA-12919 when trying to drop the tablespace
SQL> drop tablespace abc including contents and datafiles;
drop tablespace abc including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace
So changing the default tablespace for the database will change default tablespace for users who have that tablespace as default tablespace. The following demonstrates what happens when default tablespace is changed for the database.
— set default tablespace to be users
SQL> ALTER DATABASE DEFAULT TABLESPACE users;
Database altered.
— create user with tablespace users as default
SQL> create user test1 identified by test1 default tablespace users;
User created.
— change default tablespace for the database
SQL> ALTER DATABASE DEFAULT TABLESPACE abc;
Database altered.
— it changes default tablespace for user TEST1
SQL> select default_tablespace from dba_users where username = ‘TEST1’;
DEFAULT_TABLESPACE
——————————
ABC
By default if default tablespace is not specified the default tablespace is set to SYSTEM, so you can specify the default tablespace when creating the database (For example: CREATE DATABASE … DEFAULT TABLESPACE abc…)
alexlima.com 
To clarify, setting the default tablespace in an Oracle database does not change the default tablespace for all users, just for those whose default tablespace was the previous default.
LikeLike
can u tell me how many users are using a particular tablespace?
LikeLike
By running the following SQL “SELECT distinct owner FROM dba_segments” and if 11g due to deferred segment clause you will need to check dba_tables, dba_indexes, dba_tab_partitions, dba_ind_partitions and dba_lobs
LikeLike
very nice description
LikeLike
good
LikeLike
i want to know how many users are using a particular tablespace?
LikeLike
“select distinct default_tablespace, count(*) from dba_users group by default_tablespace;”
LikeLike
Is it possible to mention default table space for one session ? means
In a script i am going to perform copy of tables from a remote DB using db link and i want these tables to be created to a specific table space not the default table space of the users.
I can achieve this by creating table first and then doing an insert, but is there a way to do in single step ?
rjk
LikeLike