How is the default tablespace determined when creating a table?

Question

The Tablespace parameter is optional when creating tables.

Upon execution of a CREATE TABLE statement, Oracle assigns the default one if it was not defined.

In the default database there is a "USERS" tablespace. If there were several tablespaces defined, which one would be assigned?

Answer 1

When you create a new user, you can optionally specify the default tablespace and default temporary tablespace for any objects created by that user. For example:

CREATE USER phil IDENTIFIED BY l33t
       DEFAULT TABLESPACE philtablespace
       TEMPORARY TABLESPACE philtemp;

If you omit the clauses when creating the user, the user will inherit the database default values. These can be queried as follows:

SQL> select *
  2  from database_properties
  3  where property_name like 'DEFAULT%TABLESPACE';

PROPERTY_NAME                  PROPERTY_VALUE       DESCRIPTION
------------------------------ -------------------- ----------------------------------------
DEFAULT_TEMP_TABLESPACE        TEMP                 Name of default temporary tablespace
DEFAULT_PERMANENT_TABLESPACE   USERS                Name of default permanent tablespace

SQL>

... and modified like so:

alter database default tablespace PHILTS; 

alter database default temporary tablespace PHILTEMP;

To query the default values for a given user, use the following query:

SQL> select USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
  2  from DBA_USERS
  3  where USERNAME='PHIL';

USERNAME                       DEFAULT_TABLESPACE             TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
PHIL                           USERS                          TEMP

SQL>

Answer 2

SELECT DEFAULT_TABLESPACE FROM DBA_USERS to know the default tablespace for each user. ALTER DATABASE DEFAULT TABLESPACE othertablespace; to change the default tablespace. Usualy if there are a some tablespaces the administrator should set the default tablespace when creating a new user like CREATE USER myuser IDENTIFIED BY mypass DEFAULT TABLESPACE onetablespace