Default Tablespace in Oracle.

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