[an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] [an error occurred while processing this directive] 24x7 Script Archive


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
)