How to change the default tablespace of your Oracle Database?

Hi Guys,

This week I’ve come across a requirement to change the default tablespace of one my users. I can do it using the “ALTER USER” command (

Then the requirement has turned out to do it for all the users in the Database.

I thought of doing it by altering every user individually but noticed that my Database contains more than hundred users… Then I thought of writing a small PL/SQL anonymous block and run that.

Isn’t there any other, simple , solution for this?

it wouldn’t be if my Database is running on a version lower than 10g. But in 10g Oracle introduced a new feature which could help in this kind of scenarios.

There’s parameter introduced called ‘DEFAULT_PERMANENT_TABLESPACE’ has been introduced which could change the default tablespace for all users in the Database with a single command.

Please see the demo below,



Now we’ll create a table without specifying any tablespace clause.

SQL> create table t1(col1 number(10));

Table created.

Now observe the tablespace in which the table, t1, is created.

SQL> select tablespace_name from user_segments where segment_name=’T1′;


Now we’ll change the default tablespace of the Database to CSMTEST

SQL> alter database default tablespace csmtest;

Database altered.

So, csmtest is our new default permanent tablespace. Try creating a new table again the same we did earlier

SQL> create table t2(col1 number(10));

Table created.

Now see what tablespace the object, T2, has gone into.

SQL> select tablespace_name from user_segments where segment_name=’T2‘;


This is the way we can change the default tablespaces of an Oracle Database. The good thing with this is we can do this online without bouncing the instance/ doing any extra things…just issue a one line command, Oracle will take care of everything else…

And a bonus for you is, we can use the same strategy for changing the default temporary tablespaces as well.

The parameter for that iiiiis… yes your guess is correct 🙂 it’s DEFAULT_TEMP_TABLESPACE and the statement to change the default temporary tablespace is

alter database default temporary tablespace <temp_tablespace_of_your_choice>;

Hope you like it and this trick could be helpful to you guys.

I’m always happy to receive your suggestions/ feedback. Have a great day ahead 🙂




Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: