How is the default tablespace determined when creating a table

oracle

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?

Best Answer

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>
Related Question