Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

2 Answers

up vote 7 down vote accepted

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>
share|improve this answer
add comment

SELECT DEFAULT_TABLESPACE FROM All_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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.