Re: Curious: ORA-02001

Ah, but it *did* allow me to build other indexes. Just not anything with multiple Freelist Groups. That's what had me puzzled.

Brian

"Mercadante, Thomas F" wrote:

> I'll guess the same and say "ITS ABOUT TIME!"
>
> How simple would it have been for Oracle to enforce this in the past! I
> think it's a great idea!
>
> Tom Mercadante
> Oracle Certified Professional
>
> -----Original Message-----
> Sent: Wednesday, October 17, 2001 6:00 PM
> To: Multiple recipients of list ORACLE-L
>
> ok i'll guess, so newbies dont shoot themselves in the foot? :)
>
> joe
> Brian McGraw wrote:
> >
> > Disclaimer: This is more of a 'yeah, but why?' type of question.
> > You've been forewarned.
> >
> > Today I was building out a test schema, and had connected as the SYS
> > user to do so. The testing has to do with using different FREELIST /
> > FREELIST GROUPS values. After creating my table, I was executing the
> > following create index statement:
> >
> > ALTER TABLE "INQUIRY"."TSR_DETAILS"
> > ADD CONSTRAINT "TSR_PK" PRIMARY KEY
> > ("POLICY_NUMBER", "POLICY_SUFFIX", "ENTRY_DATE")
> > USING INDEX PCTFREE 10 INITRANS 4 MAXTRANS 255
> > STORAGE(INITIAL 1m next 1m minextents 1 maxextents unlimited
> > pctincrease 0 freelists 4 freelist groups 2)
> > TABLESPACE "INQUIRY_INDEX" ENABLE ;
> >
> > I got the following error, which I had never seen before:
> >
> > ERROR at line 5:
> > ORA-02001: user SYS is not permitted to create indexes with
> > freelist groups
> >
> > Metalink had the following to say:
> >
> > Error: ORA 2001
> > Text: user SYS is not permitted to create indexes with freelist
> > groups
> >
> >
> ----------------------------------------------------------------------------
> ---
> >
> > Cause: user tried to create an index with freelist groups while
> > running with SYS authorization.
> > Action: Either do not create the index, do not use freelist groups
> > or
> > switch to USER authorization.
> >
> > Support Notes:
> > You should not be creating indexes as SYS without a specific
> > reason.
> >
> > Of course, I connected as my user and finished building out the
> > schema.
> >
> > But now I'm curious - does anybody know why Oracle advises that you
> > not create indexes as SYS? I always assumed that as long as tables
> > were properly prefixed with an owner, you could build out schemas
> > connected as SYS or SYSTEM.
> >
> > Brian
> > --
> > --------------------------------------
> > | Brian McGraw -- Oracle DBA |
> > | Central Alabama Oracle Users Group |
> > |------------------------------------|
> > | mailto:BMcGraw_at_mindspring.com |
> > | http://bmcgraw.home.mindspring.com |
> > --------------------------------------
> >
>
> --
> Joe Testa
> Performing Remote DBA Services, need some backup DBA support?
> For Sale: Oracle-dba.com domain, its not going cheap but feel free to
> ask :)
> IM: n8xcthome or joen8xct
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Joe Testa
> INET: teci_at_the-testas.net
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Mercadante, Thomas F
> INET: NDATFM_at_labor.state.ny.us
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

--
--------------------------------------
| Brian McGraw     --     Oracle DBA |
| Central Alabama Oracle Users Group |
|------------------------------------|
| mailto:BMcGraw_at_mindspring.com      |
| http://bmcgraw.home.mindspring.com |
--------------------------------------


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Brian McGraw
  INET: brian.mcgraw_at_infinity-insurance.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).