O'Reilly logo

Oracle Database Administration: The Essential Refe by Brian Laskey, David C. Kreines

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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, ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required