Monitoring database free space

This example shows how easily you can perform various database operations in 24x7 Scheduler. To simplify the script we saved SQL part of it in a separate file called free_space.sql. The script will load this file and dynamically executed loaded SQL. This SQL was designed for Oracle 7 databases. You may need to customize it for your database.

// Load and execute SQL script from "free_space.sql" file
// If the result is positive (at least one segment found),
// notify database administrator about potential problem

Dim SQL, string
Dim rows, number
Dim problem, boolean
Dim message, string

// Load file
FileReadAll( "free_space.sql", SQL )
// Connect to database and retrieve "bad" segments,
// see "free_space.sql" file for details
DatabaseConnect( "Sales DB (Oracle 7.2)" )
DatabaseRetrieve( SQL, rows )
DatabaseDisconnect

isGreater( rows, 0, problem )

// If problem detected, notify DBA
If( problem, NOTIFY, END )

NOTIFY:
// Save retrieved data in the temporary file
DatabaseSave( "c:\temp\message.tmp", "TXT", rows )
// Read temp. file contents an then then email to DBA
FileReadAll( "c:\temp\message.tmp", message )
MailSend( "Exchange Settings", "pwrd", "ora_dba@my_company.com", "Database Problem", message )
// Delete temp. file
FileDelete( "c:\temp\message.tmp" )

END:

FREE_SPACE.SQL

SELECT 'Owner: ' || seg.owner || chr(13) || chr(10) ||
'Name: ' || seg.segment_name || chr(13) || chr(10) ||
'Type: ' || seg.segment_type|| chr(13) || chr(10) ||
'Tablespace: ' || seg.tablespace_name || chr(13) || chr(10) ||
'Next extent size: ' || to_char(seg.max_extents, '999,999,999') || chr(13) || chr(10) ||
'Problem: Max extent reached'
FROM sys.dba_segments seg
WHERE seg.extents = seg.max_extents

UNION ALL

SELECT 'Owner: ' || seg.owner || chr(13) || chr(10) ||
'Name: ' || seg.segment_name || chr(13) || chr(10) ||
'Type: ' || seg.segment_type|| chr(13) || chr(10) ||
'Tablespace: ' || seg.tablespace_name || chr(13) || chr(10) ||
'Next extent size: ' || to_char(t.next_extent, '999,999,999') || chr(13) || chr(10) ||
'Problem: Not enough space for next extent'
FROM sys.dba_segments seg,
sys.dba_tables t
WHERE seg.segment_type = 'TABLE'
AND seg.segment_name = t.table_name
AND seg.owner = t.owner
AND NOT EXISTS (SELECT tablespace_name
FROM dba_free_space free
WHERE free.tablespace_name = t.tablespace_name
AND free.bytes >= t.next_extent
)
UNION ALL

SELECT 'Owner: ' || seg.owner || chr(13) || chr(10) ||
'Name: ' || seg.segment_name || chr(13) || chr(10) ||
'Type: ' || seg.segment_type|| chr(13) || chr(10) ||
'Tablespace: ' || seg.tablespace_name || chr(13) || chr(10) ||
'Next extent size: ' || to_char(i.next_extent, '999,999,999') || chr(13) || chr(10) ||
'Problem: Not enough space for next extent'
FROM sys.dba_segments seg,
sys.dba_indexes i
WHERE seg.segment_type = 'INDEX'
AND seg.segment_name = i.index_name
AND seg.owner = i.owner
AND NOT EXISTS (SELECT tablespace_name
FROM dba_free_space free
WHERE free.tablespace_name = i.tablespace_name
AND free.bytes >= i.next_extent
)
UNION ALL

SELECT 'Owner: ' || seg.owner || chr(13) || chr(10) ||
'Name: ' || seg.segment_name || chr(13) || chr(10) ||
'Type: ' || seg.segment_type|| chr(13) || chr(10) ||
'Tablespace: ' || seg.tablespace_name || chr(13) || chr(10) ||
'Next extent size: ' || to_char(c.next_extent, '999,999,999') || chr(13) || chr(10) ||
'Problem: Not enough space for next extent'
FROM sys.dba_segments seg,
sys.dba_clusters c
WHERE seg.segment_type = 'CLUSTER'
AND seg.segment_name = c.cluster_name
AND seg.owner = c.owner
AND NOT EXISTS (SELECT tablespace_name
FROM dba_free_space free
WHERE free.tablespace_name = c.tablespace_name
AND free.bytes >= c.next_extent
)
UNION ALL

SELECT 'Owner: ' || seg.owner || chr(13) || chr(10) ||
'Name: ' || seg.segment_name || chr(13) || chr(10) ||
'Type: ' || seg.segment_type|| chr(13) || chr(10) ||
'Tablespace: ' || seg.tablespace_name || chr(13) || chr(10) ||
'Next extent size: ' || to_char(r.next_extent, '999,999,999') || chr(13) || chr(10) ||
'Problem: Not enough space for next extent'
FROM sys.dba_segments seg,
sys.dba_rollback_segs r
WHERE seg.segment_type = 'ROLLBACK'
AND seg.segment_name = r.segment_name
AND seg.owner = r.owner
AND NOT EXISTS (SELECT tablespace_name
FROM dba_free_space free
WHERE free.tablespace_name = r.tablespace_name
AND free.bytes >= r.next_extent
)