This query will give you how to find all the objects that can't extend and the reason why they can't extend: max extent reached, no free extend in the tablespace to fit the next extent, quota exceeded:
set echo off
set feedback off
set linesize 120
column owner format a15 heading "Owner"
column segment_name format a30 heading "Objet"
column tablespace_name format a13 heading "Tablespace"
column extents format 999G999 heading "Extents"
column maxext format a10 heading "Max Extents"
column nextext format 9G999G999 heading "Next (K)"
column maxfree format 999G999G999 heading "Max Free (K)"
column quota format a10 heading " Quota (K)"
def nolimit = ' Unlimited'
prompt
select s.owner, s.segment_name, s.tablespace_name,
s.next_extent/1024 nextext, nvl(f.max_free,0)/1024 maxfree,
s.extents,
decode (s.max_extents, 2147483645, '&nolimit',
tcms_char(s.max_extents, '9G999G999')) maxext,
decode (p.privilege, NULL,
decode(nvl(q.max_bytes,decode(s.owner,'SYS',-1,0)),
-1, '&nolimit',
tcms_char(nvl(q.max_bytes,0)/1024,'9G999G999')),
'&nolimit') quota
from dba_ts_quotas q, dba_sys_privs p,
( select t.tablespace_name, nvl(max(f.bytes),0) max_free
from dba_free_space f, dba_tablespaces t
where f.tablespace_name (+) = t.tablespace_name
group by t.tablespace_name) f,
dba_segments s
where q.username (+) = s.owner
and p.grantee (+) = s.owner
and p.privilege (+) = 'UNLIMITED TABLESPACE'
and f.tablespace_name = s.tablespace_name
and nvl(f.max_free,0)



| ©2010 by Barry Chase |
Credits: