extents question on LMT

From: Guang Mei <zlmei_at_hotmail.com>
Date: Tue, 28 Jan 2003 12:06:22 -0800
Message-ID: <F001.0053C7A6.20030128120622@fatcity.com>

Hi:

Oracle 8173 on Sun 2.8.

When we had tablespace created as DMT, I used to occuasionally find all indexes in a schema which have multiple extents and run a script to compress each of them into one single extent (maybe this is not necessary, but that's another topic). But I find lately that with LMT, I can not simply do that. Oracle will decide how many extents it allocates depending on the storage parameters I pass in:

ALTER INDEX ID_IG_PK REBUILD TABLESPACE INDEXES STORAGE (INITIAL 5M NEXT 5M pctincrease 0) nologging;

Does this mean I just can not "compress" some large index into a single extent in a LMT?

Example:

SQL> ALTER INDEX MT.ID_IG_PK REBUILD TABLESPACE INDEXES STORAGE (INITIAL 50K NEXT 50K pctincrease 0) nologging;

Index altered.

SQL> select BYTES,EXTENTS,blocks, INITIAL_EXTENT,NEXT_EXTENT   2 from dba_segments where owner='MT' and segment_name='ID_IG_PK';

     BYTES EXTENTS BLOCKS INITIAL_EXTENT NEXT_EXTENT ---------- ---------- ---------- -------------- -----------

    516096 1 63 57344 516096

SQL> ALTER INDEX MT.ID_IG_PK REBUILD TABLESPACE INDEXES STORAGE (INITIAL 5M NEXT 5M pctincrease 0) nologging;

Index altered.

SQL> select BYTES,EXTENTS,blocks, INITIAL_EXTENT,NEXT_EXTENT   2 from dba_segments where owner='MT' and segment_name='ID_IG_PK';

     BYTES EXTENTS BLOCKS INITIAL_EXTENT NEXT_EXTENT ---------- ---------- ---------- -------------- -----------

   5677056 11 693 5242880 516096

TIA. Guang

---

The new MSN 8: advanced junk mail protection and 2 months FREE* http://join.msn.com/?page=features/junkmail

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Guang Mei
  INET: zlmei_at_hotmail.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

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 Tue Jan 28 2003 - 14:06:22 CST