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?
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>
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
