Re: Indices move from one tablespace to another

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1998/03/27
Message-ID: <6fhgvr$pgb@bgtnsc02.worldnet.att.net>#1/1

Were all the indexes moved to the DATA tablespace after you re-enabled the constraints (Step 6) or only the unique indexes that enforce the PRIMARY KEY and UNIQUE constraints ? If only those unique indexes were moved, the reason may be that you did not specify the USING INDEX clause in your ALTER TABLE...ENABLE CONSTRAINT... statement; the enforcing indexes were then re-created in the user's default tablespace, which is (presumably and sensibly so) the DATA tablespace.

If ALL the indexes were moved to the DATA tablespace, I am as puzzled (and helpless) as you are.

Hope this helps.

Michael Serbanescu

---

Hero R. Post wrote:
>
> Maybe someone can help me,
> I am working on with Oracle 7.2 on an HP UNIX machine.
> My database is fairly large, so when I do an import it takes about 22hrs.
> To decrease this time, I decided to disable all the constraints.
> When I do the import this way, the time needed decreases by about 20-30%.
>
> Now a weird thing happens, when I enable the constraints again, the indices
> are created in the wrong tablespace.
> The steps I go through are:
> 1. Drop the schema (cascade)
> 2. Create the User again
> 3. Create all the tables and indices (in the correct tablespaces, even the
> implicit indices)
> 4. Disable the constraints.
> 5. Import the data
> 6. Enable the constraints.
>
> In steps 3,4,5 all the indices use the INDEX tablespace.
> Only after step 6 the indices 'moved' to the DATA tablespace which should be
> used only for the tables.
>
> I am completely lost can any on help???
>
> Hero
Received on Fri Mar 27 1998 - 00:00:00 CST