Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Unix Max Extent Script

RE: Unix Max Extent Script

From: Smith, Ron L. <rlsmith_at_kmg.com>
Date: Thu, 16 Jan 2003 12:40:09 -0800
Message-ID: <F001.00532060.20030116124009@fatcity.com>


Here is what we run. It is part of a group of scripts we run every 15 minutes that monitor all the databases. The script will either send a page or an email, depending on how it is called.  

The command to run the script (cron every 15 minutes): nextext.sh prod zrls1 > /dev/null 2>&1  

The nextext.sh Unix script:  

#! /bin/sh -x
# DBA MONITORING SCRIPTS
# ******************************************************************
#
# Author: Ron Smith
# Date: 06/18/98
# Funtion: Checks for objects that cannot allocate next
# extent.
#
# ******************************************************************
#
# CHANGE HISTORY
#
# DATE WHO Reason for Change
 

# 06/18/98 Ron Smith New Prog
 

#
# ******************************************************************
#
# FUNCTION
 
#
# This script calls nextext.sql.
# The function of this script is to report database objects that
# cannot allocate the next extent in the tablespace.
# If an object is found that cannot be extended, an error file
# is created and a page is sent to the DBA.
#
# If an error file already exists, the script exits without any
# action. The DBA should delete the error file when the problem
# is resolved. Another script should be scheduled to run daily
# to delete the error file so the DBA is paged at least once a
# day if the condition continues.
#
# If the id of the DBA is a Zid, a page will be sent. If the
# id of the DBA is an email address (determined by looking for
# an "@" ) , an EMAIL will be sent.
#
# ******************************************************************
#
# PREREQUISITES
#
# The OPS$ORACLE user must exist in the instance. This can be
# created by running the opsuer.sql script in SQLPLUS while
# logged on as SYSTEM.
#
# The cdmonitoring script must exist in the home/oracle
# directory.
#
# ******************************************************************
#
# RUN SYNTAX
#
# nextext.sh (sid) (oncall dba)
#
#
# ******************************************************************
 

# cd to the monitoring script directory

. $HOME/cdmonitoring.sh  

ORACLE_SID=$1
export ORACLE_SID
DBA=$2
export DBA
ATCNT=`echo $DBA | grep @ | wc -l`
export ATCNT
EMAIL=$3
export EMAIL  

ORACLE_HOME=`grep "^$ORACLE_SID:" /etc/oratab | head -1 | cut -d: -f2` export ORACLE_HOME
PATH=$ORACLE_HOME/bin:/usr/local/bin:$PATH:. export PATH  

# Delete the old list file if it exists
 

if [ -f nextext_$ORACLE_SID.lst ]

        then rm nextext_$ORACLE_SID.lst
fi  

# Check to see if an error file exists. If it does get out.
 

if [ -f nextext_$ORACLE_SID.err ]
then echo 'Error file nextext_'$ORACLE_SID'.err exists - will exit now'

        exit
fi  

# If sending to EMAIL address, run sql with headings on
 

if [ "$ATCNT" -gt "0" ]

        then
        sqlplus / @nextext.sql on
        else
        sqlplus / @nextext.sql off

fi  

# If there is anything in the lst file then send a message
 

if [ -s nextext_$ORACLE_SID.lst ]

        then echo "-DBA- Cannot Alloc Next Ext " > nextext_$ORACLE_SID.err
        echo "SID=" $ORACLE_SID " " >> nextext_$ORACLE_SID.err
        cat nextext_$ORACLE_SID.lst >> nextext_$ORACLE_SID.err
        if [ "$ATCNT" -gt "0" ]
                then
                echo "email sent"
                elm -s "-DBA- Warning! $ORACLE_SID Next Extent Warning" $DBA
< nextext_$ORACLE_SID.err
                else
                LC=`cat nextext_$ORACLE_SID.lst | sed -e 's/  */ /g' | wc
-c`
                echo $LC
                if [ "$LC" -gt "160" ]
                        then echo "Too many errors to send. Check
nextext_$ORACLE_SID.lst" >> nextext_$ORACL E_SID.err
                        else
                        cat nextext_$ORACLE_SID.lst >>
nextext_$ORACLE_SID.err
                fi
                echo "page sent"
                pager $DBA "`cat nextext_$ORACLE_SID.err`"
        fi

fi    

The nextext.sql script:  

set linesize 80
set feedback off
set verify on
set heading &1
column owner format a10
column tablespace_name format a15
column table_name format a15
column index_name format a15
column next_extent format 999,999,990
column ord_col noprint  

spool nextext_$ORACLE_SID.lst  

select /*+ RULE */
owner, tablespace_name, table_name, 1 ord_col,'' index_name, next_extent/1024 next_extent
from all_tables at
where owner like upper('%')
  and next_extent > (select max(a.bytes) largest

                     from dba_free_space a
                     where a.tablespace_name = at.tablespace_name
                    )

union
select /*+ RULE */
owner, tablespace_name, table_name, 2 ord_col, index_name, next_extent/1024 next_extent
from all_indexes ai
where owner like upper('%')
  and next_extent > ( select max(a.bytes) largest
                      from dba_free_space a
                      where a.tablespace_name = ai.tablespace_name
                     )

order by 1,2
/
spool off
exit    

That's it! Works Great!
R.Smith
Kerr-McGee Corp

-----Original Message-----
Sent: Thursday, January 16, 2003 2:15 PM To: Multiple recipients of list ORACLE-L

search for "smenu" in Google ... it is a bunch of scripts with all sh interface.

Raj



Rajendra Jamadagni MIS, ESPN Inc. Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.

QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
Sent: Thursday, January 16, 2003 3:01 PM To: Multiple recipients of list ORACLE-L

maybe u should use orasnap from a windows client??? bye
Paulo

-----Original Message-----
Sent: quinta-feira, 16 de Janeiro de 2003 18:36 To: Multiple recipients of list ORACLE-L

I have found many great SQL scripts to identify segments whose next extents will not fit into their tablespace and segments whose number of extents are approaching the max number of extents. But, what I am looking for is a Unix shell script that will run one of these scripts and mail alerts based on the

results. Has anyone written a shell script that will do this that they would

like to share? There are many other SQL scripts that I would like to run from cron to evaluate SQL script results.

Thanks
Erik   

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
<http://www.orafaq.net>  
-- 
Author: Erik Williams 
  INET: ewilliams_at_brownco.com 

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
<http://www.fatcity.com>  
San Diego, California        -- Mailing list and web hosting services 
--------------------------------------------------------------------- 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
<http://www.orafaq.net>  
-- 
Author: Paulo Gomes 
  INET: PGomes_at_Datinfor.pt 

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
<http://www.fatcity.com>  
San Diego, California        -- Mailing list and web hosting services 
--------------------------------------------------------------------- 
To REMOVE yourself from this mailing list, send an E-Mail message 
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in 
the message BODY, include a line containing: UNSUB ORACLE-L 
(or the name of mailing list you want to be removed from).  You may 
also send the HELP command for other information (like subscribing). 

If you are not the intended recipient of this e-mail message, any use,
distribution or copying of the message is prohibited.  Please let me know
immediately by return e-mail if you have received this message by mistake,
then delete the e-mail message. Thank you.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Smith, Ron L.
  INET: rlsmith_at_kmg.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Jan 16 2003 - 14:40:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US