Locally Managed Tablespace Confusion

From: Pat Howe <phowe_at_Illuminet.com>
Date: Wed, 06 Feb 2002 16:51:55 -0800
Message-ID: <F001.004082E4.20020206164318@fatcity.com>

I am confused about locally managed tablespaces. I created a locally managed tablespace called "INDEX01" using "UNIFORMED" extents of 128K (131072bytes).
I then imported in the tables and indexes from an different database.

When I query DBA_TABLESPACES it shows that the tablespace has been created as LOCALLY MANAGED (128k extents) - this is good. But when I query DBA_INDEXES and DBA_SEGMENTS the indexes that reside in this LOCALLY MANAGED tablespaces show extents all over the map - this is bad.

I expected that all indexes would be rebuilt using the new Locally Managed extent size of 128K - not true.
Wuz up with that ?

Thanks in advance

---

select tablespace_name, initial_extent, next_extent, pct_increase, extent_management, allocation_type
from dba_tablespaces
where tablespace_name = 'INDEX04' ;

TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE EXTENT_MAN ALLOCATIO --------------- -------------- ----------- ------------ ---------- ---------

INDEX04                 131072      131072            0 LOCAL      UNIFORM

---

select owner, index_name, tablespace_name,initial_extent, next_extent, min_extents, max_extents,pct_increase
from sys.dba_indexes
where tablespace_name = 'INDEX01'
order by 4, 1, 2, 3 ;

OWNER INDEX_NAME TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE
--------------- --------------- --------------- -------------- ----------- ----------- ----------- -

LAWCOPY         WUPSET2         INDEX04                  16384      131072
1  2147483645            0

---

select segment_name, tablespace_name, initial_extent, next_extent, pct_increase
from dba_segments
where segment_name = 'WUPSET2' ;

SEGMENT_NAME TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE --------------- --------------- -------------- ----------- ------------

WUPSET2         INDEX04                  16384      131072            0

_________________________ 

 Patrick J. Howe
 Oracle DBA
 Illuminet Inc. (Carrier Division of Verisign)  4501 Intelco Loop SE
 Olympia, WA 98507
 Phone : 360.493.6284
 Email : phowe_at_illuminet.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Pat Howe
  INET: phowe_at_Illuminet.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 Feb 06 2002 - 18:51:55 CST