Question: I created an Oracle table and
instead of going to the USERS tablespace, it was stored in
my SYSTEM tablespace. Why did Oracle create my table
in the SYSTEM tablespace?
Answer: When creating a table it is customary
to specify a "tablespace XXX" clause. If you
omit the the "tablespace" clause, Oracle will create the
table in the user's default tablespace. To change the
default tablespace for a user you can issue this command:
alter user
scott default tablespace users;
This will
keep new tables from getting into the system tablespace.
To move a table from the system tablespace, you can use the
alter table move tablespace xxx command.
You can
also use the CTAS and rename commands but you must re-add
the table constraints:
create table
temp as select * from mytab tablespace users;
rename mytab
to oldtab;
rename temp
to mytab;
drop table
oldab;
-- rebuild all
table indexes and constraints
