How to set and Check default tablespace

What is default tablespace

When we create a user and do not specify the default tablespace, the user is assigned the SYSTEM tablespace (If  we  have   not  specify a default  tablespace  for  database )

But  if  we  have specifed  a  default  tablespace  for  database  before  All users created without the DEFAULT TABLESPACE clause will have database default tablespace as their default.

How  to specify  default  tablespace

Default tablespace can be specified one of two ways at the database level:

• During database creation via the CREATE DATABASE command .If the default tablespace is not specified during the database creation, it defaults to SYSTEM

• After database creation via the ALTER DATABASE command. e.x
  
  SQL> alter database default tablespace TOOLS;
  Database altered.

Chaning the default tablespace

Changing the database default tablespace will change the default tablespace of the user even if the tablespace has been assigned during creation time.

 e.x  The  default tablespace  od  database is  tools   and  we  will  create 

SQL>CREATE USER TEST1 IDENTIFIED BY TEST1 ;

SQL>CREATE USER TEST2 IDENTIFIED BY TEST2 DEFAULT TABLESPACE TOOLS;

SQL>SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME IN ('TEST1','TEST2');


USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST1 TOOLS         ---User Default TS=tools  because  database default TS
TEST2 TOOLS         ---User Default TS=tools because create user ... default tablespace clause


SQL>alter database default tablespace  USERS ;
Database altered.

SQL>SELECT USERNAME,DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME IN ('TEST1','TEST2');

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TEST1 USERS
TEST2 USERS


Both users default  tablespace  changed  to  USERS

How  to check default tablespace

SQL>SELECT PROPERTY_NAME, PROPERTY_VALUE FROM DATABASE_PROPERTIES where PROPERTY_NAME in ('DEFAULT_PERMANENT_TABLESPACE');

PROPERTY_NAME PROPERTY_VALUE

------------------------------ --------------------------------------------------
DEFAULT_PERMANENT_TABLESPACE TOOLS

How to  drop  default  tablespace

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 TOOLS  including contents and datafiles;
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace