How is the default tablespace determined when creating a table?

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

  • Philᵀᴹ

    Philᵀᴹ Correct answer

    8 years ago

    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

License under CC-BY-SA with attribution


Content dated before 6/26/2020 9:53 AM