Re: Removing datafiles from tablespaces

From: Nick Keeman <nick_at_bart.nl>
Date: 1995/11/16
Message-ID: <48imfr$obi_at_unix1.bART.nl>#1/1

In article <donigian_dave-1611952105140001_at_156.80.155.107>,

   donigian_dave_at_bah.com (David Donigian) wrote:

>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?

David,

I presume you do have a script to recreate your indexes. So, just remove all your indexes. Export your database (full). Recreate your database as you did before but with a smaller tablespace for your tables. Import the export.dmp. Create a tablespace for your indexes. Edit your index script to use this new tablespace and run the script.

If you don't have a 'create all indexes script' then make this script with the exp command. I'm quite sure there's an option like 'indexfile='. So do an export like: $ exp system/manager full=y rows=n indexfile=recreateindexes.sql

                        grants=n constraints=n indexes=y

Please check all of the options cause I'm not quite sure of them and I'm not close to an Oracle computer.

Good Luck.

---

       ////
      (o o)
___oOo_(_)_oOo________________________________________________________________
                  |                                     |
Nick Keeman       | The Golden rule:                    | email   nick_at_bart.nl
The Hague         | He who has the gold makes the rules | Voice +31-70-3466029
The Netherlands   |                                     |   Fax +31-70-3463418
______________________________________________________________________________
 (   )    (   )
  \ (      ) /

   \_) (_/ Received on Thu Nov 16 1995 - 00:00:00 CET