Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Im running "Oracle Database 10g Express Edition Release 10.2.0.1.0 - 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

1 Answer 1

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
TEMPORARY TABLESPACE temp;

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

 
discard

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.