Space Usage Calculator

##########################################
#
# Space usage calculator
#
#
#
#
##########################################

for i in 1
do
  echo -e "\n\nSpace Calc "
  echo -e "Enter TOTAL size: \c"
  read X
  echo -e "Enter USED size:  \c"
  read Y
  B=`echo "scale=2;"${Y}/${X}*100 | bc -l`
  echo -e "Space Usage: ${B}%\n"
  echo -e "Enter required USED %:  \c"
  read A
  C=`echo "scale=2;"${X}/100*${A} | bc -l`
  D=`echo "scale=2;"${Y}-${C} | bc -l`
  echo -e "Required increase: ${D} for ${A}%\n\n"
done

Bigley Logic

for z in BIGLEY BIGLEYDR
do
FIL=OracleFlag_${z}
if [ -f ${FIL} ]
then
 #echo "Flag set"
 TS=`ls -altr ${FIL} | awk '{print $6, $7, $8}'`
 echo -e "\n\nFlag set for: "${z}" on ${TS} \n\t\tso this database will not be started\n\n"
else
echo -e "\n\n--> Flag NOT set for ${z} <-- "
echo -e "\nWill start database ${z}\n\n"
 STA=`ps -ef | grep smon | grep -Ev "grep|ASM|agent|bin" | awk '{print $NF}' | cut -f3 -d"_"`
 # echo $STA
 if [ ${STA} = ${z} ]
  then
  echo -e "\nDatabase ${STA} is already running!!\n"
  else
  echo -e "Starting database ${STA}"
  fi
fi
done

Script to read a text file and count the occurrences of a full stop.

##########################
#
# Script to read a text file and count the occurrences of a full stop.
#
#
#
##########################

for z in SED
do
A=0
B=0
C=0
while read -r a
do
# echo ${a}
if [[ ${a} == *.* ]]
then
# echo "True"
A=`expr ${A} + 1`
 echo ${a} | sed 's/\./\.\n/g' | sed 's/^ //g'
 B=`echo ${a} | tr -d -C "." | wc -c`
 C=`expr ${B} + ${C}`
 echo -e "Current count of full stops: "${B}
 echo -e "Running count of full stops: "${C}
else
echo -e "\t\t--> This line does not contain a full stop"
fi
done</tmp/sed1    ## Text file here!!
# echo -e "Total count: ${A}"
echo -e "Total count of full stops: ${C}\n\n"
done

DB Parameter change generation script

-- ---------------------------------
--
-- DB Parameter change generation script
--
--
-- ---------------------------------

col name format a30 word_wrapped
col description format a60 word_wrapped
def param = &&1
set verify off

select decode(ISSES_MODIFIABLE,'FALSE','Cannot be changed','TRUE','Can be changed') "Session",
   decode(ISSYS_MODIFIABLE,'FALSE','Cannot be changed','TRUE','Can be changed','IMMEDIATE','Can be changed immdeiate','DEFERRED','Can be changed deferred') "System",
   'ALTER SYSTEM SET '||name||' = '||value||' scope='||
decode(ISSYS_MODIFIABLE,'FALSE','spfile','IMMEDIATE','both or memory',ISSYS_MODIFIABLE)||' sid=''*'';' value
from v$parameter
where name like ('%&param%')
or value like ('%&param%')
/

undef param
undef 1
set verify on

dbms_file_transfer.copy_file example - generation script

-- ---------------------------------------
--
-- dbms_file_transfer.copy_file example for a Data Guard broker file
--
-- NOTE: The Data Guard configuration must be disabled to allow this to happen
-- and also stop DMON process by doing alter system set dg_broker_start='false';
--
-- ---------------------------------------

set serveroutput on format wra

declare
  uname    varchar2(50);
  bfile    varchar2(50);
   cursor C1 is
    select lower(value) uname from v$parameter where name = 'db_unique_name';
   cursor C2 is
    select SUBSTR( value, INSTR( value, '/', -1 ) + 1, LENGTH( value )) bfile from v$parameter where name =

'dg_broker_config_file1';
begin
for x in C1 loop
 dbms_output.new_line;
 dbms_output.put_line('CREATE OR REPLACE DIRECTORY SOURCE_DIR AS ''+DATA01/'||x.uname||''';');
 dbms_output.new_line;
end loop;
for y in C2 loop
 dbms_output.put_line('BEGIN');
 dbms_output.put_line('dbms_file_transfer.copy_file(source_directory_object =>');
 dbms_output.put_line('''SOURCE_DIR'', source_file_name => '''||y.bfile||''',');
 dbms_output.put_line('destination_directory_object => ''DP_DUMP'',');
 dbms_output.put_line('destination_file_name => ''/tmp/'||y.bfile||''');');
 dbms_output.put_line('END;');
 dbms_output.new_line;
 dbms_output.put_line('drop directory SOURCE_DIR;');
 dbms_output.new_line;
end loop;
end;
/

Oracle Inventory Check Script for Linux

###########################################
#
#
# Oracle Inventory Check Script for Linux
#
#
#
###########################################



for z in `cat /etc/oraInst.loc | grep loc | cut -f2 -d"="`
do
 echo -e "\n########################################################################\nThe inventory is located in: "${z}"\n"
 A="${z}/ContentsXML/inventory.xml"
 VER=`cat ${A} | grep -E "SAVED_WIT" | cut -f2 -d">" | cut -f1 -d"<"`
 HME=`cat ${A} | grep -E "HOME NAME" | awk '{print $2,"--> ", $3}' | sed 's/NAME=//g'`
 echo -e "Oracle version in inventory = "${VER}
 echo -e "For HOMES = "
 for y in `cat ${A} | grep -E "HOME NAME" | awk '{print $2}' | sed 's/NAME=//g' | sed 's/LOC/Located at/g' | sed 's/"//g' | tr '[:lower:]' '[:upper:]'`
 do
 echo -e "\t${y}"
 cat ${A} | grep -i ${y} | awk '{print "\t\t--> ", $3}' | sed 's/NAME=//g' | sed 's/LOC/Located at/g' | sed 's/"//g'
 done
 echo -e "\n########################################################################\n"
done

SQL - INSTR & SUBSTR example

-- -----------------------------
--
-- Reverse search using "/" as a separator.
--
-- -----------------------------

col List format a35

select
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 5)+1,length('This/is/a/separation/list'))||chr(10)||
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 4)+1,length('This/is/a/separation/list'))||chr(10)||
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 3)+1,length('This/is/a/separation/list'))||chr(10)||
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 2)+1,length('This/is/a/separation/list'))||chr(10)||
substr('This/is/a/separation/list',INSTR('This/is/a/separation/list', '/', -1, 1)+1,length('This/is/a/separation/list')) List
 from dual
/

-- -----------------------------
--
-- Find how many occurrences of "/" appear
--
-- -----------------------------

variable main_str varchar2(30)
variable srch_str varchar2(20)
exec :main_str := 'This/is/a/separation/list';
exec :srch_str := '/';

select (length(:main_str) - nvl(length(replace(:main_str,:srch_str,'')),0))/
          length(:srch_str) cnt
from dual;

select
substr(:main_str,INSTR(:main_str, :srch_str, -1, 5)+1,length(:main_str))||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 4)+1,length(:main_str))||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 3)+1,length(:main_str))||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 2)+1,length(:main_str))||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 1)+1,length(:main_str)) List
 from dual
/

-- -----------------------------
--
-- Find how many occurrences of "/" appear
--
-- -----------------------------
col cnt heading "How many occurrances of /"

select
substr(:main_str,INSTR(:main_str, :srch_str, -1, 5)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 5)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 5)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str)||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 4)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 4)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 4)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str)||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 3)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 3)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 3)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str)||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 2)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 2)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 2)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str)||chr(10)||
substr(:main_str,INSTR(:main_str, :srch_str, -1, 1)+1,length(:main_str))||chr(10)||
(length(substr(:main_str,INSTR(:main_str, :srch_str, -1, 1)+1,length(:main_str)))- nvl(length(replace(substr(:main_str,INSTR(:main_str, :srch_str, -1, 1)+1,length(:main_str)),:srch_str,'')),0))/length(:srch_str) cnt
 from dual
/

RMAN Backup Details By Day

-- ----------------------------------------------
--
-- Script to run against actual database to look at the backup piece handle by tag for a particular day
--
--
--
-- ----------------------------------------------


col handle format a87
col STRT format a20
col "Inc Level" format a8
col STATUS format a10
col DEVICE_TYPE format a8
col NP noprint
col HN format a25



select
 A.RECID NP,
 to_char(A.START_TIME,'DD-MON-YY HH24:MI:SS')Strt
 , decode(B.INCREMENTAL_LEVEL,'0','FULL',B.INCREMENTAL_LEVEL) "Inc Level"
 , decode(A.STATUS,'A','Available','D','Deleted','X','Expired',A.status) STATUS
--  , SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle)) HN
-- ,SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle))
 , SUBSTR(SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle)),instr((SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle))),'_',-1,6)+1,12) HN
 , A.DEVICE_TYPE
 , DECODE(B.BACKUP_TYPE,'D','full backup','L','archived redo log','I','I,incremental level 1') B_TYPE
 , A.tag
 , round(A.BYTES/1048576,2) MB
 from v$backup_piece A,  v$backup_set B
 where a.SET_STAMP = b.SET_STAMP
 and a.SET_COUNT = b.SET_COUNT
 --
 -- Enter DATE below e.g. 02-OCT-13
 --
 and to_char(A.START_TIME,'DD-MON-YY')='&Date'
--  and A.DEVICE_TYPE = 'DISK'
order by SUBSTR(SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle)),instr((SUBSTR(a.handle,instr(a.handle,'/',-1)+1,LENGTH(a.handle))),'_',-1,6)+1,12) --A.RECID, A.COMPLETION_TIME, A.START_TIME
/