RE: Locally Managed Tablespace Confusion

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 06 Feb 2002 17:50:31 -0800
Message-ID: <F001.00408393.20020206174320@fatcity.com>

I think it's because dba_segments (or dba_indexes) will show the initial/next specified at creation time, even though the extents were not created at that size. Look in dba_extents to see that all extents are the same size.

Example:

SQL> select * from dba_tablespaces where tablespace_name = 'DATA_SMALL' ;

TABLESPACE_NAME                INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS

------------------------------ -------------- ----------- -----------

MAX_EXTENTS PCT_INCREASE MIN_EXTLEN STATUS    CONTENTS  LOGGING   EXTENT_MAN ----------- ------------ ---------- --------- --------- --------- ---------- ALLOCATIO PLU
--------- ---

DATA_SMALL                              15360       15360           1

 2147483645            0      15360 ONLINE    PERMANENT LOGGING   LOCAL
UNIFORM   NO SQL> select distinct initial_extent, next_extent
  2  from dba_segments
  3  where tablespace_name = 'DATA_SMALL' ;

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
         15360       15360

SQL> create table test (n number) tablespace data_small storage (initial 30720 next 30720) ;

Table créée.

SQL> select distinct initial_extent, next_extent
  2  from dba_segments
  3  where tablespace_name = 'DATA_SMALL' ;

INITIAL_EXTENT NEXT_EXTENT
-------------- -----------
         15360       15360
         30720       15360

SQL> select distinct (bytes) from dba_extents where tablespace_name = 'DATA_SMALL' ;

     BYTES

---

     15360

> -----Original Message-----
> From: Pat Howe [mailto:phowe_at_Illuminet.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 ?  
> 
> ==========
> 
> 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 

Received on Wed Feb 06 2002 - 19:50:31 CST