Re: indexes in non-system tablespaces

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