Description:
The output will provide you a list of segments which will fail when allocating next extent.
Code:
REM Check Next Extents which could not be allocated.
REM Author Sanjay Gupta
REM This script also takes care of Locally Managed Tablespaces as well as Dictionary Managed Tablespace.
REM If you are using Locally Managed Tablespace(LMT) with the Autoallocate Segment Allocation, then there is no
REM Oracle provided algorithm to find the the value of next extent size. So, in case of LMT and Autoallocate
REM segment allocation ,this scripts assumes that the size of next extent would be same as size of previous
REM extent size atleast something is better than nothing.
SET LINESIZE 200
select b.tablespace_name "Tablespace",
b.segment_type "Type",
substr(ext.owner||'.'||ext.segment_name,1,50) "Object Name",
To_Char(decode(freespace.Extent_Management,'DICTIONARY',decode(b.extents,1,b.next_extent, ext.bytes * (1+b.pct_increase/100))
,'LOCAL',decode(freespace.Allocation_Type,'UNIFORM',freespace.INITIAL_EXTENT,'SYSTEM',ext.bytes)
)/1024,'9,999,999,999') "Required Extent(K)",
to_char(freespace.largest/1024,'9,999,999,999') "MaxAvail K"
from dba_segments b, dba_extents ext,
(select B.tablespace_name, B.Extent_Management, B.Allocation_Type, B.INITIAL_EXTENT, B.NEXT_EXTENT, max(A.bytes) largest
from dba_free_space A, dba_tablespaces B
Where B.Tablespace_Name = A.Tablespace_Name
And B.Status='ONLINE'
group by B.tablespace_name, B.Extent_Management, B.Allocation_Type, B.INITIAL_EXTENT, B.NEXT_EXTENT
) freespace
where
b.owner=ext.owner and
b.segment_type=ext.segment_type and
b.segment_name=ext.segment_name and
b.tablespace_name= ext.tablespace_name and
(b.extents-1) =ext.extent_id and
b.tablespace_name = freespace.tablespace_name and
decode(freespace.Extent_Management,'DICTIONARY',decode(b.extents,1,(b.next_extent),ext.bytes*(1+b.pct_increase/100))
,'LOCAL',decode(freespace.Allocation_Type,'UNIFORM',freespace.INITIAL_EXTENT,'SYSTEM',ext.bytes)
) > freespace.largest
order by b.Tablespace_Name,b.Segment_Type,b.Segment_Name
/
submitted by:
Sanjay Gupta
Hits :
5428
:: Rating:
3.00
:: Votes:
2
|
