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 (http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_4003.htm).

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,

SQL> SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = ‘DEFAULT_PERMANENT_TABLESPACE‘;

PROPERTY_VALUE
——————————————————————————–
USERS

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′;

TABLESPACE_NAME
——————————
USERS

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‘;

TABLESPACE_NAME
——————————
CSMTEST

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 🙂

Regards,

CSM

Advertisements

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

%d bloggers like this: