change tablespace for a table/index

[hari]

Hi,

For any given table/index, how do I change the
tablespace in which it resides. We have got into
a problem wherein some of the tables/objects
are created in 'SYSTEM' tablespace which we want
to migrate to another tablespace.

Thanks for the help.

- Hari.

[MaryJoH]

You can export the table then import to the new schema / tablespace and drop the old table.

Or you can create a new table in the new tablespace using create table nnnn as select xxxx. And remember to drop the table in the system area when you are done.

And you will have to deal with the system area fragmentation.

[tansdot]

You can also use the Alter table <> Move stmt to move the table to a different tablespace.

Tansdot

[marist89]

And you can use:
alter index xyz rebuild tablespace abc

[rameshgiri26]

Do one thing
1). Export the database with system/manager user name and passwod While exporting Mention Grant=No then

2). Drop all tables, Procedures, Functions,Views etc... The Things you can list like this ( select * from user_objects Which are not required in system tablespace...

3). connect system/manager
create tablespace user_data datafile 'C:\ORANT\ORADATA\USER_DATA.DBF' SIZE 200M;

4). Create user identified by default tablespace USER_DATA temporary tablespace temp quota unlimited on USER_DATA;

5). Import That dump file to this schema. For That user What you have created that userid/password and say Grant=No,
Ignore=Y


Enjoy..

RAMESH