RE: Unix Max Extent Script
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 -- Author: Erik Williams INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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 -- Author: Paulo Gomes INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Unix Max Extent Script
Title: RE: Unix Max Extent Script 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- From: Paulo Gomes [mailto:[EMAIL PROTECTED]] Sent: Thursday, January 16, 2003 3:01 PM To: Multiple recipients of list ORACLE-L Subject: RE: Unix Max Extent Script 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 -- Author: Erik Williams INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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 -- Author: Paulo Gomes INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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). This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you.*2
RE: Unix Max Extent Script
Run as stored procedure scheduled using DBMS_JOB and if an alert occurs send it to the alert log using the procedure in DBMS_SYSTEM. I presume you already have a system which monitors you alert logs so it should be pretty easy to generate your email/pages without any further modification without creating a whole bunch of new scripts for people to manage. If you still insist on going the UNIX scripting route please DO NOT write a specific script to check extents. Instead write a script which runs any .sql file and checks for rows returned, then takes some action based upon the fact that rows are returned or not. Now you can use this single script to run any number of checks against your database. - Ethan -Original Message- Sent: Thursday, January 16, 2003 12:36 PM 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 -- Author: Post, Ethan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Unix Max Extent Script
Title: Message
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 21
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=$1export ORACLE_SIDDBA=$2export DBAATCNT=`echo
$DBA | grep @ | wc -l`export ATCNTEMAIL=$3export
EMAIL
ORACLE_HOME=`grep "^$ORACLE_SID:" /etc/oratab | head -1 | cut -d:
-f2`export
ORACLE_HOMEPATH=$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.lstfi
#
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'
exitfi
# If
sending to EMAIL address, run sql with headings on
if [
"$ATCNT" -gt "0" ]
then sqlplus / @nextext.sql
on
else sqlplus / @nextext.sql
offfi
# 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_$ORACLE_SID.err
else
cat nextext_$ORACLE_SID.lst
nextext_$ORACLE_SID.err
fi
echo "page
sent"
pager $DBA "`cat
nextext_$ORACLE_SID.err`"
fifi
The nextext.sql
script:
set
linesize 80set feedback offset verify onset heading 1column
owner format a10column tablespace_name format a15column table_name
format a15column index_name format a15column next_extent format
999,999,990column ord_col noprint
spool
nextext_$ORACLE_SID.lst
select
/*+ RULE */owner, tablespace_name, table_name, 1 ord_col,'' index_name,
next_extent/1024 next_extentfrom all_tables atwhere owner like
upper('%') and next_extent (select max(a.bytes)
largest
from dba_free_space
a
where a.tablespace_name =
at.tablespace_name
)unionselect /*+ RULE */owner, tablespace_name, table_name, 2
ord_col, index_name, next_extent/1024 next_extentfrom all_indexes
aiwhere 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 offexit
That's it!
Works Great!
R.Smith
Kerr-McGee
Corp
-Original Message-From: Jamadagni,
Rajendra [mailto:[EMAIL PROTECTED]] Sent: Thursday,
January 16, 2003 2:15 PMTo: Multiple recipients of list
ORACLE-LSubject: RE: Unix Max Extent Script
search for "smenu" in Google ... it is a bunch of scripts with
all sh interface.
Raj __ Rajendra Jamadagni
RE: Unix Max Extent Script
Or perhaps this... Write a program that will manually allocate extents to each object until you run out of space, then capture error and number of extents and write to a flat file using UTL_FILE package. Then export object, truncate table and import to recover the space you allocated during each test. Then use SQL loader to bring the data from the flat file into the database and write queries against the X$ constructs to see if you are approaching too many extents. Use a minimum of 5 .sh scripts and 8 control files to accomplish this task. From what I have seen this will qualify you for numerous jobs as a Senior Oracle DBA. DISLAIMER: THE ABOVE WAS A JOKE BUT DOES REFLECT THE TYPES OF THINGS I HAVE SEEN IN THE PAST. :) -Original Message- Sent: Thursday, January 16, 2003 12:36 PM 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 -- Author: Post, Ethan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
Re: Unix Max Extent Script
Post, Ethan wrote: Or perhaps this... Write a program that will manually allocate extents to each object until you run out of space, then capture error and number of extents and write to a flat file using UTL_FILE package. Then export object, truncate table and import to recover the space you allocated during each test. Then use SQL loader to bring the data from the flat file into the database and write queries against the X$ constructs to see if you are approaching too many extents. Use a minimum of 5 .sh scripts and 8 control files to accomplish this task. From what I have seen this will qualify you for numerous jobs as a Senior Oracle DBA. DISLAIMER: THE ABOVE WAS A JOKE BUT DOES REFLECT THE TYPES OF THINGS I HAVE SEEN IN THE PAST. :) Reminds me of something which I have seen which basically was a spool to a file of a SELECT * on the various views in the dictionary, wrapped into a shell script and followed by a number of greps ... Why use SELECT when you can grep with regular expressions ? -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
RE: Unix Max Extent Script
My favorite is when people go nutty checking every possible variable in their scripts before getting to the thing the script was actually written for i.e... Check to see if database sid is valid, check to see if user name is valid, check to see if time zone on server is set, what is ambient temperature of server room, log every bit of meaninless info to a log file yada yada yada -Original Message- Sent: Thursday, January 16, 2003 3:40 PM To: Multiple recipients of list ORACLE-L Post, Ethan wrote: Or perhaps this... Write a program that will manually allocate extents to each object until you run out of space, then capture error and number of extents and write to a flat file using UTL_FILE package. Then export object, truncate table and import to recover the space you allocated during each test. Then use SQL loader to bring the data from the flat file into the database and write queries against the X$ constructs to see if you are approaching too many extents. Use a minimum of 5 .sh scripts and 8 control files to accomplish this task. From what I have seen this will qualify you for numerous jobs as a Senior Oracle DBA. DISLAIMER: THE ABOVE WAS A JOKE BUT DOES REFLECT THE TYPES OF THINGS I HAVE SEEN IN THE PAST. :) Reminds me of something which I have seen which basically was a spool to a file of a SELECT * on the various views in the dictionary, wrapped into a shell script and followed by a number of greps ... Why use SELECT when you can grep with regular expressions ? -- Regards, Stephane Faroult Oriole Software -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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 -- Author: Post, Ethan INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
