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