Default Tablespace in Oracle.

•A default tablespace in oracle is the tablespace which will be used as default tablespace whenever a new user is created that user is implicitly assigned with that tablespace. So if that new user create any objects the objects will be by default created in his default tablespace.

•If default tablespace is not specified when creating a database or when created schema/schema objects, then the SYSTEM tablespace is the default permanent tablespace for non-SYSTEM users.

•A default tablespace can be specified one of two ways at the database level:

1) during database creation via the CREATE DATABASE command
or
2) after database creation via the ALTER DATABASE command.

•The default tablespace can only be locally managed.

•In order to see the database default tablespace issue,

SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';

PROPERTY_VALUE
--------------------------------------------------------------------------------
USERS

•In order to see a particular user default tablespace use,
SQL> SELECT DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='ARJU';

DEFAULT_TABLESPACE
------------------------------
USER_TBS


•In order to set database default tablespace compatible parameter must be greater than 10.0 To see current compatiblity settings issue,
SQL> show parameter compatible

If current compatibility settings is less than 10.0 the following error will come,

SQL> ALTER DATABASE DEFAULT TABLESPACE new_tbs;
*
ERROR at line 1:
ORA-12916: Cannot use default permanent tablespace with this release

•Database default tablespace can't be dropped. if you are going to drop the following error will arise.

SQL> drop tablespace users including contents;
drop tablespace users including contents
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

In order to drop a database default tablespace assign other tablespace as database default tablespace and then drop.

SQL> alter database default tablespace user_tbs;
Database altered.

SQL> drop tablespace users including contents;
Tablespace dropped.