Monitoring: database extents

1. Objects whose next extent is too big to fit in the tablespace (ORA-1653, ORA-1654, ORA-1655):

SELECT OWNER,SEGMENT_NAME, round(NEXT_EXTENT/(1024*1024),1) next_in_MB,
S.TABLESPACE_NAME,round(MAX_FREE_BYTES/(1024*1024),1) max_free_MB,
autoextensible_bytes/(1024*1024) autoextend_MB
FROM SYS.DBA_SEGMENTS S,
(SELECT TABLESPACE_NAME,MAX(BYTES) MAX_FREE_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(select tablespace_name, sum(maxbytes)-sum(bytes) autoextensible_bytes
from dba_data_files
group by tablespace_name) d
WHERE
S.NEXT_EXTENT > ( F.MAX_FREE_BYTES + d.autoextensible_bytes )
AND S.TABLESPACE_NAME=F.TABLESPACE_NAME and
f.tablespace_name=d.tablespace_name;

2. Objects that are near their MAXEXTENTS (ORA-1632):

select I.OWNER, INDEX_NAME, EXTENTS, I.MAX_EXTENTS
from dba_indexes I, DBA_SEGMENTS S
where I.OWNER=S.OWNER AND I.INDEX_NAME=S.SEGMENT_NAME AND
S.SEGMENT_TYPE=’INDEX’
AND I.max_extents < 100000
AND I.MAX_EXTENTS – S.EXTENTS < 10;

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: