Can anybody help me with this? I used the following dba_views on one table(emp) and got this
SQL> select segment_name, segment_type, bytes, blocks,tablespace_name from dba_segments where segment_name = 'EMP';
SEGMENT_NAME SEGMENT_TYPE BYTES BLOCKS TABLESPACE_NAME
------------- ------------- ---------- ---------- ----------------
EMP TABLE 10485760 2560 EMP_TBS
SQL> select segment_name, bytes, tablespace_name from dba_extents where segment_name = 'EMP';
SEGMENT_NAME BYTES TABLESPACE_NAME
------------- ---------- ----------------
EMP 10485760 EMP_TBS
NOTE:the initial extent is 5242880, next extent is 5242880,max_extents is 450.
Question:Does the bytes(10485760) mean that is the number of bytes that has been used so far out of the number allocated?
I think 10485760 is the number of bytes allocated to this EMP segment. Part of it may not have been used at all. I mean to say.... when the first extent become full, Oracle would allocate the next extent of 5242880 bytes no matter if it needs a seconed extent for 1 row or 10 rows or 1000 rows.
So, when ever the current extent get full Oracle would allocate the next extent based on the NEXT extent value and the PCTINCREASE value.
Anybody, please correct me if I am wrong.....
I don't think I agree with you
Why should the number of bytes allocate to the segment be 10485760 if both initial and next extents are 5242880.
When I multiplied 5242880 by 2, I got 104855760 which I think indicates that both the initial and next extents have been used.
So I am thinking that if initial and next extent have both been set to 5242880 and pct_increase is 0, then 5242880 will be allocated each time an extent has been used till the max extent of 450 will all being used.
CAn any one please intervene and help us out or probably correct us with this discussion?
NOTE: The initial and the next extent for the segment is 5242880, pct_increase is 0 and max extents is 450, So I do not agree with you saying the number of bytes allocated to the segment is 10485760
What I mean was:
Currently, there are 2 extents in the EMP segments of size (5242880 each). Hence the total size of the segemnts comes to 5242880 + 5242880 = 10485760 bytes. However, it doesn't mean that the second extent is fully utilized. The second extent is out there and may be partly filled up. When the second extent is fully utlilized, then a thrid extent of 5242880 bytes would be allocated (if PCTINCREASE = 0).
Hope this clarifies.
Sorry for the mis communication. That is exactly what I was thinking. Thanks for the help
Click Here to Expand Forum to Full Width