#!/usr/bin/ksh
#whatfreespace ()
#------------------------------------------------------------------------------#
# Module Name: whatfreespace #
# #
# Purpose: Report on the Database Freesapce. #
# #
# Maintenance #
# Date Author Description #
# ----------- ------------ -------------------------------------------- #
# 22-Jun-2010 S.Bommareddy Module design/creation #
# M.Barone Module design/creation #
#------------------------------------------------------------------------------#
{
DIRNAM=$(dirname $0)
ORASID=$(echo $CONTEXT_NAME | cut -f1 -d'_')
HNAME=$(uname -n)
#----------------------------------------------------------------------#
# TEST: Number ($#) of arguments passed to this function? #
#----------------------------------------------------------------------#
if [ $# = 0 ]
then
clear
echo
echo " #--------------------------------------------------------#"
echo " # whatfreespace: Oracle Database Freespace #"
echo " #--------------------------------------------------------#"
echo
#------------------------------------------------------------------#
# Trap: Set CNTL-C CNTL-D CNTL-\ Before Password Protection #
#------------------------------------------------------------------#
trap "stty echo; return" 2
trap "stty echo; return" 3
stty -echo
read PASSWD?" Please Enter the Oracle APPS Passwd: "
stty echo
echo
#------------------------------------------------------------------#
# Trap: Reset CNTL-C CNTL-D CNTL-\ After Password Protection #
#------------------------------------------------------------------#
trap 2
trap 3
clear
else
PASSWD=$1
clear
fi
#------------------------------------------------------------------------------#
# Initialize #
#------------------------------------------------------------------------------#
SPOOLNM=/tmp/whatfreespace_$$.txt
#------------------------------------------------------------------------------#
# SQL*Plus ("Here-Document") #
#------------------------------------------------------------------------------#
sqlplus -s /nolog <<-END_FILE
CONNECT APPS/$PASSWD
SPOOL ${SPOOLNM}
clear BREAKS
clear BUFFER
clear COLUMNS
clear COMPUTES
clear SCREEN
clear SQL
clear TIMING
set feedback off
set flush on
set heading on
set pause off
set space 1
set termout on
set verify on
set linesize 80
set pagesize 999
prompt #---------------------------------------------------------------#
prompt #- Oracle Database Freespace (whatfreespace) -#
prompt #---------------------------------------------------------------#
COLUMN datevalue NOPRINT NEW_VALUE DATEVAR
COLUMN timevalue NOPRINT NEW_VALUE TIMEVAR
COLUMN tsname FORMAT A17 HEADING "TableSpace"
COLUMN nfiles FORMAT 990 HEADING "Num|DBFs"
COLUMN nfrags FORMAT 9990 HEADING "Free|Frag"
COLUMN mxfrag FORMAT 9,999,990 HEADING "Largest|Frag (Kb)"
COLUMN usedsize FORMAT 999,999,999,990 HEADING "Used|Space(Kb)"
COLUMN freesize FORMAT 999,999,990 HEADING "Free|Space(Kb)"
COLUMN totsize FORMAT 999,999,999,990 HEADING "Total|Space(Kb)"
COLUMN pctusd FORMAT 990 HEADING "Pct|Used"
BREAK on REPORT
TTITLE CENTER '${ORASID} (${HNAME}) eBusiness Database Report ' -
LEFT DATEVAR skip 1 -
CENTER 'eBusiness 12 eBusiness Database Freespace' -
LEFT TIMEVAR skip 2
alter session set NLS_DATE_FORMAT = 'DD-MON-YYYY HH24:MI:SS';
COMPUTE sum of nfiles nfrags totsize usedsize freesize on report
SELECT TO_CHAR(sysdate, 'DD-MON-YY') datevalue,
TO_CHAR(sysdate, 'HH24:MI:SS') timevalue,
total.tablespace_name tsname,
total.nfiles,
free.nfrags,
total.totsize,
(total.totsize - free.freesize) usedsize,
free.freesize,
round(((1-(free.freesize/total.totsize))*100 ),0) pctusd
FROM (select tablespace_name,
count(*) nfiles,
round((sum(bytes)/(1024)),0) totsize
FROM dba_data_files
GROUP by tablespace_name ) total,
(SELECT tablespace_name,
count(*) nfrags,
round(nvl((sum(bytes)/(1024)),0),0) freesize
FROM dba_free_space
GROUP BY tablespace_name
UNION
(SELECT tablespace_name, 0, 0 from dba_data_files
MINUS
SELECT tablespace_name, 0, 0 from dba_free_space)) free
WHERE total.tablespace_name = free.tablespace_name
AND total.tablespace_name like upper('%')
ORDER BY 7 ;
END_FILE
print
print "Please see /tmp/whatfreespace_$$.txt"
print
}
No comments:
Post a Comment