Oracle database automation cron jobs

Job ID: 35303190

Budget: ₹600 – ₹1,500 INR

We have multiple oracle database(RAC setup with primary and standby) in our environment for that we required to create a shell script which will automatic send the required data in html format to our email id, following are the scripts which has to be configured in our setup.

server os:Sun OS,Linux

set serveroutput on
set timing on
set echo off
set time on

--Tablespace Output:

set lines 999 pages 999
select a.tablespace_name, round(assigned_space) "ALLOCATED (MB)", round((nvl(free_space,0) + INCRM)) "FREE (MB)",
round((assigned_space - nvl(free_space,0) - INCRM)) "USED (MB)",
round( ((assigned_space - nvl(free_space,0) - INCRM) / assigned_space)*100) "% Full"from(select  tablespace_name, count(*) num1, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) assigned_space,
sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) - sum(bytes)/(1024*1024) INCRM from dba_data_files group by tablespace_name) A,(select  tablespace_name, count(*) num2, (sum(bytes)/(1024*1024)) free_space
from dba_free_space a group by tablespace_name) B where b.tablespace_name(+)=a.tablespace_name
union all
select a.tablespace_name, round(assigned_space) "ALLOCATED (MB)", round((nvl(free_space,0) + INCRM)) "FREE (MB)",
round((assigned_space - nvl(free_space,0) - INCRM)) "USED (MB)",
round(((assigned_space - nvl(free_space,0) - INCRM ) / assigned_space)*100) "% Full"from(select  tablespace_name, count(*) num1, sum(DECODE(SIGN(maxbytes - bytes), -1, bytes, maxbytes))/(1024*1024) assigned_space,
Sum(Decode(Sign(Maxbytes - Bytes), -1, Bytes, Maxbytes))/(1024*1024) - Sum(Bytes)/(1024*1024) Incrm From Dba_Temp_Files Group By Tablespace_Name) A,(Select  Tablespace_Name, Count(*) Num2, (Sum(free_space)/(1024*1024)) Free_Space
FROM DBA_TEMP_FREE_SPACE GROUP BY TABLESPACE_NAME) B WHERE B.TABLESPACE_NAME(+)=A.TABLESPACE_NAME
Order By "% Full" Desc
/


--FRA

set lines 999 pages 999
col name for a20
select name,(SPACE_LIMIT/1024/1024/1024), (SPACE_USED/1024/1024/1024), (SPACE_RECLAIMABLE/1024/1024/1024) from v$recovery_file_dest
/


--Instance Startup :

SELECT inst_id,INSTANCE_NAME,to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM sys.gv_$instance
/

--ASM Disk usage:

SELECT NAME, TOTAL_MB/1024 total_gb, FREE_MB/1024 free_gb, ROUND((1- (FREE_MB / TOTAL_MB))*100, 2)  "percentage used" FROM v$asm_diskgroup order by 4 desc
/
exit;
Related categories: SQL Shell Script