Re: An index being rebuilt changes it's extent sizes?

I am also confused.

I have local indexes on partitioned tables, though not function-based.

When I create the local indexes they go into the tablespace where I tell them to go. Furthermore, since I do not specify DEFAULT, when I split a partition, the new index partitions go into the previous tablespace I built the index in.

When I issue a rebuild and specify the tablespace, the index gets rebuilt into the tablespace I specified.

• Original Message ----- From: Diana Duncan <Diana_at_fileFRENZY.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Wednesday, November 01, 2000 12:45 PM Subject: An index being rebuilt changes it's extent sizes?

> List,
>
> I'm very confused. I have a partitioned index created with LOCAL into a
> locally managed tablespace with uniform extent sizes of 480K. When I create
> the index, everything is OK, except it goes into the data tablespaces where
> the base table is (because of the LOCAL). The extent sizes are 480K, which
> is 30 blocks. For reference, the statement is:
>
> CREATE INDEX DOC_INDEX_VALUE_LOWER_STRING ON DOC_INDEX_VALUE(DOMAIN_ID,
> lower(string_value), INDEX_ID)
> PCTFREE 10
> INITRANS 2
> MAXTRANS 255
> TABLESPACE ACCT0_INDX
> STORAGE (INITIAL 480K NEXT 480K)
> LOCAL
> ;
>
> Then, I rebuild each partition into the associated index tablespace using a
> command like the following:
>
> alter index DOC_INDEX_VALUE_LOWER_STRING rebuild partition ACCT1051
> tablespace ACCT1051_INDX;
>
> After the rebuild, the index partitions suddenly have 330 blocks rather than
> 30. Any idea why? This only happens with the one index out of all of them.
> It is a function-based index, but it isn't the only one...
>
> Any ideas? I'm baffled. ;-)
>
> TIA,
>
> Diana Duncan
> Database Architect
> filefrenzy
> diana_at_filefrenzy.com
> 919.760.1151 <-- New number
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Diana Duncan
> INET: Diana_at_fileFRENZY.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).
Received on Wed Nov 01 2000 - 14:08:11 CST