move table/index from specific tablespace

[feroz]

Hi all,

I have user default tablespace called "USERS". This tablespace has plenty of spaces with couple of datafiles. I know that oracle can't drop specific datafile instead you have to drop tablespace. What is the best way to do this? Is there a way I can move all tables and indexes to temporary tablespace? After move them all, drop "USERS" tablespace, recreate with proper size and move back all the tables and indexes to new "USERS" tablespace.

Thank you in advance.

[feroz]

Hi again,

Forget couple of thing. Im runing oracle 8i 8.1.7.2 on Solaris 8 sparc.

thanks again.

[Mr.Hanky]

There are a lot of variables to consider here.

Why do you want to do this?
What are the space issues you are having?
How many users are using the tablespace that you want to drop?

You can not (to my knowledge) assign those objects to a temporary tablespace. I would think about creating a new tablespace that is properly sized, export (FULL if possible), drop the user, create the user and assign the appropriate tablespace, then import the users objects.

Again you need to be certain of what is in that tablespace before you go down this road. I would look at DBA_SEGMENTS first.

MH

[tamilselvan]

ALTER TABLE MOVE and ALTER INDEX REBUILD are sufficient to move objetcs. NO need to exp/imp.

Tamil