How to set/find default tablespace in 10g?

— Set default tablespace for all users, so when users are created without specifying default tablespace it will be user’s default tablespace
SQL> ALTER DATABASE DEFAULT TABLESPACE abc;

Database altered.

— To find the default tablespace name for the database is
SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME = ‘DEFAULT_PERMANENT_TABLESPACE’;

PROPERTY_VALUE
——————
ABC

Once when default tablespace is set for the database it can’t be dropped until it’s changed to a different tablespace otherwise one will get ORA-12919 when trying to drop the tablespace
SQL> drop tablespace abc including contents and datafiles;
drop tablespace abc including contents and datafiles
*
ERROR at line 1:
ORA-12919: Can not drop the default permanent tablespace

So changing the default tablespace for the database will change default tablespace for users who have that tablespace as default tablespace. The following demonstrates what happens when default tablespace is changed for the database.

— set default tablespace to be users
SQL> ALTER DATABASE DEFAULT TABLESPACE users;

Database altered.

— create user with tablespace users as default
SQL> create user test1 identified by test1 default tablespace users;

User created.

— change default tablespace for the database
SQL> ALTER DATABASE DEFAULT TABLESPACE abc;

Database altered.

— it changes default tablespace for user TEST1
SQL> select default_tablespace from dba_users where username = ‘TEST1’;

DEFAULT_TABLESPACE
——————————
ABC

By default if default tablespace is not specified the default tablespace is set to SYSTEM, so you can specify the default tablespace when creating the database (For example: CREATE DATABASE … DEFAULT TABLESPACE abc…)

8 thoughts on “How to set/find default tablespace in 10g?

  1. To clarify, setting the default tablespace in an Oracle database does not change the default tablespace for all users, just for those whose default tablespace was the previous default.

      • By running the following SQL “SELECT distinct owner FROM dba_segments” and if 11g due to deferred segment clause you will need to check dba_tables, dba_indexes, dba_tab_partitions, dba_ind_partitions and dba_lobs

  2. Is it possible to mention default table space for one session ? means

    In a script i am going to perform copy of tables from a remote DB using db link and i want these tables to be created to a specific table space not the default table space of the users.
    I can achieve this by creating table first and then doing an insert, but is there a way to do in single step ?

    rjk

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s