| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: locally managed tbs in 9i and extent sizes
You can specify different INITIAL extent values for a segment in a locally
managed tablespace with uniform allocation type, but all the extents will
still be the same size. Oracle will just allocate the number of uniform
extents necessary to satisfy your INITIAL specification. The NEXT will be
forced to the same value as the extent size for the tablespace. See a Proof
of Concept in an 8.1.7 database. (By the way, the INITIAL and NEXT in
dba_segments differ from what I specify in the create statement. Part of
that is due to finding the right number of uniform extents, and part is also
due to the fact that this test database was created with a weird blocksize,
namely 3K.)
SQL> select tablespace_name, extent_management, allocation_type,
initial_extent, next_extent
2 from dba_tablespaces
3 where tablespace_name = 'DATA_SMALL' ;
TABLESPACE_NAME EXTENT_MAN ALLOCATIO INITIAL_EXTENTNEXT_EXTENT
------------------------------ ---------- --------- -------------- ----------- DATA_SMALL LOCAL UNIFORM 1536015360
SQL> create table t1 (n number)
2 tablespace data_small storage (initial 1M next 1M minextents 3) ;
Table créée.
SQL> create table t2 (n number)
2 tablespace data_small storage (initial 2M next 2M minextents 3) ;
Table créée.
SQL> create table t3 (n number)
2 tablespace data_small storage (initial 3M next 3M minextents 3) ;
Table créée.
SQL> column segment_name format a30
SQL> select segment_name, extents, initial_extent, next_extent
2 from user_segments
3 where segment_name in ('T1', 'T2', 'T3') and segment_type = 'TABLE' ;
SEGMENT_NAME EXTENTS INITIAL_EXTENT NEXT_EXTENT ------------------------------ --------- -------------- ----------- T1 206 3151872 15360 T2 410 6294528 15360 T3 615 9437184 15360
SQL> select segment_name, bytes, count (*)
2 from user_extents
3 where segment_name in ('T1', 'T2', 'T3') and segment_type = 'TABLE'
4 group by segment_name, bytes ;
SEGMENT_NAME BYTES COUNT(*) ------------------------------ --------- --------- T1 15360 206 T2 15360 410 T3 15360 615
> -----Original Message-----
> From: Ehresmann, David [mailto:David.Ehresmann_at_ps.net]
>
> I have a problem when I do a full import into an instance
> that I just made.
> No matter what size I make the "DIAMOND" tablespace ( or
> datafile) I seem
> to always get an ORA-1658 or ORA-1659. I have gone to
> metalink and read all
> about it. Check storage clause, check free space, check
> extent size, etc.
> This is 9iRel2 on AIX5.1.
> So , I just make the datafile bigger and try again. But what
> is really
> going on?
>
> Below is my DIAMOND tablespace creation statement:
>
> create tablespace diamond datafile '/.../diamond_01.dbf' size 500m
> extent management local uniform size 256k;
>
> As you can see it is extent management local with uniform
> size of 256k.
>
>
>
> If I query dba_tablespaces after creation with this statement
> you can see
> initial_extent,
> next_extent, and so on:
>
> SQL> l
> 1 select tablespace_name, initial_extent, next_extent, min_extents,
> max_extents, extent_management, allocation_type,
> segment_space_management
> 2 from dba_tablespaces
> 3* where tablespace_name='DIAMOND'
> SQL> /
>
> TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
> MAX_EXTENTS
> EXTENT_MAN ALLOCATIO SEGMEN
>
> --------------------------------- -----------------------
> --------------------- ----------------------
> -----------------------
> --------------------- ------------------ ---------
>
> DIAMOND 262144 262144
> 1 2147483645 LOCAL
> UNIFORM MANUAL
>
>
>
> Now for the question. Then why do I end up with all these different
> initial_extent sizes?
> Each segment_type (table) basically corresponds to a table I
> believe. As
> you can see I end up with 128k, 256k,
> 16k, 32k, 40k, 50M extent sizes, etc. This just a small
> sampling. By
> using "extent management local and uniform extent size 256k"
> shouldn't
> I end up with all the same extent sizes?
> What am I not seeing here? Should I create my tablespace
> with a much larger
> initial_extent size such as 1M, 8M, or 64M?
>
> SQL>
> 1 select tablespace_name, segment_type, extents, initial_extent,
> next_extent, min_extents from dba_segments
> 2* where tablespace_name='DIAMOND' and segment_type='TABLE'
> SQL> /
>
>
>
> TABLESPACE_NAME SEGMENT_TYPE EXTENTS
> INITIAL_EXTENT NEXT_EXTENT
> MIN_EXTENTS
>
> --------------- ------------------
> ---------- -------------- - ----------
> -----------
>
> DIAMOND TABLE 1
> 122880 262144 1
>
> DIAMOND TABLE 1
> 16384 262144 1
>
> DIAMOND TABLE 1
> 245760 262144 1
>
> DIAMOND TABLE 1
> 16384 262144 1
> DIAMOND TABLE 5
> 1228800 262144 1
> DIAMOND TABLE 210
> 54779904 262144 1
>
> DIAMOND TABLE 2
> 286720 262144 1
>
> DIAMOND TABLE 6
> 1474560 262144 1
>
> DIAMOND TABLE 1
> 32768 262144 1
>
> DIAMOND TABLE 1
> 122880 262144 1
> DIAMOND TABLE 5
> 1064960 262144 1
>
> DIAMOND TABLE 1
> 16384 262144 1
> DIAMOND TABLE 1
> 40960 262144 1
> TABLE 2
> 286720 262144 1
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jacques Kilchoer INET: Jacques.Kilchoer_at_quest.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 Apr 15 2003 - 19:03:49 CDT
 |
 |