Re: indexes in non-system tablespaces

In article <3741_at_nlsun1.oracle.nl>, kverruyt_at_nl.oracle.com (Kees Verruyt) writes:
|> In article <C4nwIB.61K_at_news.iastate.edu>, GO.MSB_at_isumvs.iastate.edu writes:
|> |> Oracle, tell me it isn't true:
|> |>
|> |> ANY USER CAN CREATE AN INDEX IN ANY TABLESPACE.
|> |>
|> |> Your default tablespace concept is excellent, the ability to give users
|> |> RESOURCE only on specific tablespaces is great, but what about indexes???
|> |> Aren't indexes resources? How can you be so d... stupid???
|> |>
|> |> (timidly, calmly: am I missing something?)
|> |>
|> |> Is this addressed in Ver. 7 ?? Since the default tablespace (for other
|> |> than create tables ...) is SYSTEM, how does one keep users out of the
|> |> SYSTEM tablespace???
|> |>
|> |> If this is a repeat complaint/question, I apologize for wasting the
|> |> bandwidth. We are getting ready to open up an ULTRIX-based client/server
|> |> operation for university staff and students and I just found this out.
|> |>
|> |> I'M MAD AS HELL AT ORACLE, TOO. (yes, we also found the install scripts
|> |> inept, were even instructed by support to modify them--no's for absent
|> |> products ignored; had to comment out the queries for absent products).
|> |>
|> |> Marvin Beck Iowa State University
|>
|> Martin,
|>
|> YES! YOU ARE MISSING SOMETHING.
|>
|> Indeed, indexes ARE resources, and are managed with the same "RESOURCE" principle.
|>
|> What you probably missed is that creating an index needs temporary sorting space.
|> If the user's "temporary tablespace" is SYSTEM, it will still use system.
|> To prevent this, alter the user's temporary tablespace to a tablespace reserved
|> for this special purpose.
|>
|> If this isn't your problem, could you perhaps post the statements that you are
|> trying and their results? That way, it is much easier to give advice!
|>
|> Hope that this helps,
|> Kees.
|>
|>
|> PS: I work for Oracle, but I'm not responsible for Sales nor Support. So don't
|> blame Oracle if I get it wrong! I just like to help fellow netlanders...
|> --
|> ------------------------------------------------------------------------
|> Kees Verruijt Rijnzathe 6
|> Software Engineer Gateway development 3454 PV DE MEERN
|> Oracle Europe The Netherlands
|> Email: kverruyt_at_nl.oracle.com Tel: +31 3406 94886
|> or: kverruyt_at_oracle.nl Fax: +31 3406 65603

The thrust of the question has not been addressed. What is needed is a way to put indexes in their own tablespace, that is to not place tables and indexes together. Since indexes are a resource, they will be placed in a user's default tablespace intermingled with the tables. There are performance reasons for keeping tables and indexes separate.

                       Ian MacGregor
                       Stanford Linear Accelerator Center
                       (415) 926-3528