| 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
Hi,
Note: This error comes when mentioned segment is not able to allocate
extent because insufficient contiguous space in this tablespace. Check
that you have sufficient contiguous space available in this tablespace i.e.
more than the size of required extent. To know the largest available
contiguous chunk of space, use
( select max(bytes) from dba_free_space where
tablespace_name='<tablespace name>'; )
And check the extent size required using: ( select next_extent, pct_increase, tablespace_name from dba_rollback_segs where segment_name = '<RS name>'; extent size = next_extent * (1 + (pct_increase/100) For Rollback Segment pct_increase is zero.)
If the tablespace don't have sufficient contiguous space, the required
space for extent can be increased by increasing tablespace by adding more
datafile .
In case you have sufficient empty space, but lack of contiguous chunk of
space, rebuild your tablespace to overcome this error. The same is also
true for table/index/temporary segments/Rollback segments.
With Regards,
Manoj Kumar Jha
Mobile No : 9810090974
A transcendentalist engaged in auspicious activities does not meet with destruction either in this world or in the spiritual world; one who does good, is never overcome by evil.
DENNIS WILLIAMS
<DWILLIAMS_at_LIFE To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
TOUCH.COM> cc:
Sent by: Subject: RE: locally managed tbs in 9i and extent sizes
root_at_fatcity.co
m
04/16/03 02:39
AM
Please respond
to ORACLE-L
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 servicesReceived on Wed Apr 16 2003 - 04:13:50 CDT
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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: ManojKr Jha INET: jmanoj_at_delhi.tcs.co.in 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).
 |
 |