Removing datafiles from tablespaces

Folks,

I am currently transforming a development database into a production database and need some help with the following:

Currently, both table data and indexes are held in the same tablespace, in this case "USERS". Additionally, multiple schemas exist; also in the same tablespace ("USERS").

I'd like to split out the indexes for one of the schemas into a new tablespace, with the purpose of moving them to a separate hard drive. Unfortunately I don't have room on the hard drive to create another tablespace large enough to hold the indexes.

So what I'd like to do is shrink/remove some of the datafiles from the "USERS" tablespace to free up space. How do I do this?

Do I need to export all the schemas (sans indexes), redo the tablespaces/datafiles, import, then recreate the indexes in the new tablespace? Is there an easier way? Perhaps more importantly, will this work?

Thanks in advance for any help

David Received on Thu Nov 16 1995 - 00:00:00 CET