Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Im running "Oracle Database 10g Express Edition Release - Production" and trying to get familiar with it. I have created a tablespace und set it online. How do I make sure the next create table statement is using this tablespace? Is store as dedicated to this? Does oracle automatically figure it out?

Thanks nn

share|improve this question

You have a few options available. The easy one:

CREATE TABLE your_table (field_a CHAR(1), ... ) TABLESPACE your_tablespace;

This will create the table "your_table" in the "your_tablespace" tablespace.

You could also create a user to make him use your tablespace as a default tablespace, thus all his objects (not only tables!) would be created in that tablespace:

CREATE USER your_user 
IDENTIFIED BY "your_password" 
DEFAULT TABLESPACE your_tablespace

GRANT CONNECT to your_user;

You could also modify an existing user to change the default tablespace:

ALTER USER your_user DEFAULT TABLESPACE your_tablespace;

From that moment on, all NEW objects will be created in your_tablesapce (Previously existing objects will NOT be moved automatically!)

To move a table from one tablespace to another:

ALTER TABLE your_table MOVE TABLESPACE your_tablespace;

Think that's all...

share|improve this answer
hey, thanks for the quick reply, anyone please vote for this guy – Dieter Aug 22 '11 at 14:41

Your Answer


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.