Oracle-L: RE: locally managed tbs in 9i and extent sizes

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Tue, 15 Apr 2003 13:09:01 -0800
Message-ID: <F001.00581DE2.20030415130901@fatcity.com>

David

From what I see, you only have room in your tablespace for a single object. This means you can create a single table as long as it doesn't need more than 256k. If you check dba_free_space, you'll probably see that you have some space left over, but that can't be used because it won't be big enough for another 256k extent.

My guess is that you haven't quite figured out how uniform extents work, but you are pretty close.

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Tuesday, April 15, 2003 2:34 PM
To: Multiple recipients of list ORACLE-L

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: DENNIS WILLIAMS
INET: DWILLIAMS_at_LIFETOUCH.COM

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