Change tablespace of a table 1

[lalleima]

HI

I have some tables in my oracle Database which belongs to the same Owner(say MyUser). However some of the tables are in the tablespace - SYSTEM and some are in another user tablespace(say - MyTbs).

What will be the most eficient way to change the table in tablespace - SYSTEM to MyTbs without disturbing the structure(constraints-index) and the data?

By the way I'm using Oracle 8i(Release 3 (8.1.7) for Windows NT )

Thanks

 

[SantaMufasa]

Obviously, when I mentioned "SQL writing SQL", that is a euphemism in this case for "SQL writeing 'exp' and 'imp'"

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:40 (05Feb04) GMT, 11:40 (05Feb04) Mountain Time)

 

[gunjan14]

Hi Dave,

Thanks for the advice. Thats what I was looking for.
I have couple of issues:
1. If any foreign key in some other table is referencing to the "moving table" then when I drop the current table, that foreign key is dropped and so I have to re-create it manually. Is there a workaround for this? Or I have to keep my foreign key creation script handy for this case.

2. Since in the import, the tables and indexes will be in separate tablespace, is it better to follow these steps
- Import with indexfile=Y, constraints=N (to create index creation file)
- Change the tablespace name in the index creation file.
- Re-run the import with index=N and constraints=Y
- Run the index creation file.
I was thinking if this will create a problem?

Thanks and Regards,
Gunjan