Free Extents in tablespace

SELECT TABLESPACE_NAME,
SUM(BYTES) free_space,
sum(free_extents) free_extents,
max(next_extent) max_next_extent,
max(tbsp_type) tablespace_type,
max(allocated_bytes) allocated_bytes,
round(sum(bytes)/max(allocated_bytes)*100,1) percent_free
FROM (
SELECT A.TABLESPACE_NAME,
round(F.BYTES/A.next_extent)*A.next_extent bytes,
round(F.BYTES/A.next_extent) free_extents,
a.next_extent,
a.tbsp_type,
d.bytes allocated_bytes
FROM DBA_FREE_SPACE F,
(select /*+ rule no_unnext */ tablespace_name, sum(user_bytes) bytes from dba_data_files group
by tablespace_name) d,
( select tablespace_name,
(case when next_extent > seg_next_extent then next_extent else seg_next_extent end)
next_extent,
tbsp_type
from (
select /*+ no_unnest */ tablespace_name, next_extent, next_extent seg_next_extent, ‘LU’
tbsp_type
from dba_tablespaces t
where extent_management = ‘LOCAL’ and
allocation_type = ‘UNIFORM’ and
status!=’READ ONLY’ and
contents=’PERMANENT’
union all
select /*+ no_unnest */ tablespace_name, next_extent,
(select max( nvl(next_extent*power(1+pct_increase/100,3),power(1024,2)))
from dba_segments
where tablespace_name = t.tablespace_name) seg_next_extent, ‘OT’ tbsp_type
from dba_tablespaces t
where not (extent_management = ‘LOCAL’ and allocation_type = ‘UNIFORM’ ) and
status!=’READ ONLY’ and
contents=’PERMANENT’
)
) A
WHERE
A.TABLESPACE_NAME = F.TABLESPACE_NAME(+) and
A.TABLESPACE_NAME = D.TABLESPACE_NAME
)
GROUP BY TABLESPACE_NAME

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: