This content has been marked as final. Show 7 replies
Extents in a segment are "used" -- i.e. there are never "free" extents in a segment.
Free Extents may exist in a Tablespace. A Free Extent is allocated to a segment that needs a new extent. Once an extent is allocated to a segment, it is "used" and belongs to the segment. The extent must be explicitly deallocated (i.e. "freed") be either of
a. ALTER TABLE tablename DEALLOCATE UNUSED
b. ALTER TABLE tablename SHRINK
c. ALTER TABLE tablename MOVE
Hemant K Chitale
To add to Hemant's reply, you can use an implementation from Tom Kyte to do teh same . Look at this thread,
you can refer to
DBA_FREE_SPACE - lists the free extents in all tablespaces
DBA_FREE_SPACE_COALESCED: -This view help to determine how coalesce is a tablespace
- Pavan Kumar N
thanks, a very good knowledge for me , also what i was looking forward to
to know the MAX_EXTENTS of a particular tablespace and then list the segment in that tablespace that have no of Currently USED EXTENTS to them and their MAX_EXTENTS ? May be like a below scenario
USER tablespace has MAX_EXTENTS :=2000
it has 3 segments
seg1: MAX_EXTENTS :=1000 USED_EXTENTS:200
Seg2: MAX_EXTENTS :=500 USED_EXTENTS:200
Seg3: MAX_EXTENTS :=500 USED_EXTENTS:200
is there any script i can get like this
Thanks in advance
MAX_EXTENTS set at the Tablespace level is not a Tablespace limit. It acts as the default value inherited by any segments created in the Tablespace without specifying their own MAX_EXTENTS limit.
For the example you quote, since each Segment has it's own MAX_EXTENTS limit, the Tablespace "setting" is irrelevant -- it is not used.
For each segment, USER_SEGMENTS shows both MAX_EXTENTS and EXTENTS -- the EXTENTS column is the current number of Extents.
Hemant K Chitale
Thanks. So can any1 tell me how m i suppose to find the total or max extents for a given tablespace and how many segments are there for this tablespace ?
thanks in advance
how m i suppose to find the total or max extents for a given tablespace and how many segments are there for this tablespace ?Answer depends upon unprovided details.
query DBA_SEGEMENTS, DBA_EXTENTS, DBA_TABLESPACES & DBA_FREE_SPACE to obtained desired details.