The functionality of some of these scripts might overlap with the results provided by some of the
tools I??™ve mentioned earlier in the chapter, but they might be more focused and in some cases
provide more detail about the possible space problems in the database.
Segments That Cannot Allocate Additional Extents
In the following script, we want to identify segments (most likely tables or indexes) that cannot
allocate additional extents:
select s.tablespace_name, s.segment_name,
s.segment_type, s.owner
from dba_segments s
where s.next_extent >=
(select max(f.bytes)
from dba_free_space f
where f.tablespace_name = s.tablespace_name)
or s.extents = s.max_extents
order by tablespace_name, segment_name;
TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE OWNER
------------------ ----------------------- ----------------- ---------------
USERS9 EMPLOYEE_SEARCH TABLE HR
198 Oracle Database 11g DBA Handbook
In this example, we??™re using a correlated subquery to compare the size of the next extent to
the amount of free space left in the tablespace. The other condition we??™re checking is whether the
next extent request will fail because the segment is already at the maximum number of extents.
The reason these objects might be having problems is most likely one of two possibilities: The
tablespace does not have room for the next extent for this segment, or the segment has the maximum
number of extents allocated. To solve this problem, the DBA can extend the tablespace by adding
another datafile or by exporting the data in the segment and re-creating it with storage parameters
that more closely match its growth pattern.
Pages:
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355