Oracle Database Administration: The Essential Refe

SQL queries to monitor storage utilization

When monitoring storage, you need to pay attention to two separate areas: objects that are going to run out of room or extents in the near future, and total growth over time. You worry about objects in the short term so that you can avoid having your applications fail. You worry about growth in space over time so that you can project when you will need to acquire more disk space.

The following script identifies segments that are getting close to running out of contiguous free space for a NEXT extent:

SELECT owner, 
s.tablespace_name, 
segment_name, 
s.bytes, 
next_extent, 
MAX(f.bytes) largest 
FROM dba_segments s,dba_free_space f 
WHERE s.tablespace_name = f.tablespace_name(+) 
GROUP BY owner,s.tablespace_name,segment_name,s.bytes,next_extent 
HAVING next_extent*2>max(f.bytes) 
/

The following script identifies segments that are getting close to their MAX-EXTENTS value:

SELECT owner,tablespace_name,segment_name,bytes,extents,max_extents 
FROM dba_segments 
WHERE extents*2 > max_extents 
/

The following scripts store information about the size and number of extents of objects in the database. The scripts create historical tables to store information about tablespaces and segments:

CREATE TABLE dba_tablespace_history ( timestamp DATE, tablespace_name VARCHAR2(30), num_of_files NUMBER, num_of_blocks NUMBER, num_of_bytes NUMBER ) PCTFREE 0 TABLESPACE tools STORAGE (INITIAL 393216 NEXT 196608 PCTINCREASE 0); CREATE TABLE dba_segments_history ( timestamp DATE, ...