Re: locally managed tbs in 9i and extent sizes

David,

To shed some light on your last question, when doing the import, Oracle is creating your tables with a specified intial extent size and because it is a locally managed tablespace, it is trying to allocate enough extents to add up to that specied initial size. If you wish, to avoid this, you might create the tables first without a storage clause and then do the import with a 'ignore=y'. Here's an example of where I'm coming from...

// Creating tablespace with uniform extent management at 16K

SQL> create tablespace dwl_data
  2 extent management local uniform size 16K   3 datafile '/aimp/u05/oradata/AIMP/dwl_data01.dbf' size 50M;

Tablespace created.

Elapsed: 00:00:02.06

// Creating a table with a storage clause specifying initial / next of 128K

SQL> create table dwl_tab (column_a varchar2(50), column_b date)   2 storage(initial 128K next 128K) tablespace dwl_data;

Table created.

Elapsed: 00:00:00.04

// Even though the table has just been created empty, Oracle has built it wiŻ
//    8 extents of 16K to satisfy my request for initial of 128K.
//     ( 16384 * 8 = 131072 )

SQL> select tablespace_name, segment_type, extents, initial_extent, next_extent, min_extents   2 from dba_segments where tablespace_name = 'DWL_DATA';

TABLESPACE_NAME                SEGMENT_TYPE          EXTENTS INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS
------------------------------ ------------------ ---------- -------------- ----------- -----------
DWL_DATA                       TABLE                       8         131072       16384           1

>>> David.Ehresmann_at_ps.net 04/15/03 02:33PM >>>
List,
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

thanks,

David Ehresmann

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net 
-- 
Author: Ehresmann, David
  INET: David.Ehresmann_at_ps.net 

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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Darrell Landrum
  INET: DLANDRUM_at_zalecorp.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).