Spool - Append and Calculation

Shell Programming and Scripting
1

I am running a cron job, every 6 hours a day (6 AM, 12 PM, 6 PM, and 12 AM),

#!/usr/bin/ksh
......
$SQLPLUS / as sysdba <<!
@/home/oracle/scripts/daily.sql
!
cat /home/oracle/scripts/dbsizedaily.lst | mail -s "$TODAY: PROD DB Size" $RECIPIENTS

"@/home/oracle/scripts/daily.sql" has

spool /home/oracle/scripts/dbsizedaily append

select sum(bytes)/1024/1024/1024 "TOTAL_GB"
from dba_data_files;

select sum(bytes)/1024/1024/1024 "USED_GB"
from dba_segments;  

spool off
exit

and it simply generates

  TOTAL_GB                                                                      
----------                                                                      
3151.24316                                                                      


   USED_GB                                                                      
----------                                                                      
2330.40381              

  TOTAL_GB                                                                      
----------                                                                      
3151.24316                                                                      


   USED_GB                                                                      
----------                                                                      
2347.41551       

  TOTAL_GB                                                                      
----------                                                                      
3151.24316                                                                      


   USED_GB                                                                      
----------                                                                      
2349.76498

What I want to have out of the "@/home/oracle/scripts/daily.sql" is a tab delimeter file (to import into Excel better):

DATE         TIME           TOTAL_GB      USED_GB      %USED
3/5/2013    06:00 AM     3151.24316    2330.40381   73.95
3/5/2013    12:00 PM     3151.24316    2347.41551   74.49
3/5/2013    06:00 PM     3151.24316    2349.76498   74.56
.....

Please advise how to modify "@/home/oracle/scripts/daily.sql" or the ksh to get the output I want. If I can re-direct the output of spool /home/oracle/scripts/dbsizedaily append to another file, that works for me.

2

Join two tables and generate the result:

select to_char(sysdate, 'mm/dd/yyy') "DATE", to_char(sysdate, 'HH24:MI AM') "TIME", 
sum(a.bytes)/1024/1024/1024 "TOTAL_GB", sum(b.bytes)/1024/1024/1024 "USED_GB"
from dba_data_files a, dba_segments b;

For percentage: add another field, apply your math on a.bytes and b.bytes

2 Likes
3

If this combined query gives me the accurate result, it will be great, it doesn't..

select to_char(sysdate, 'mm/dd/yyy') "DATE", to_char(sysdate, 'HH24:MI AM') "TIME", 
sum(a.bytes)/1024/1024/1024 "TOTAL_GB", sum(b.bytes)/1024/1024/1024 "USED_GB"
from dba_data_files a, dba_segments b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME
4

Fantastic input from bipinajith

Would this be better?

select a.tablespace_name,to_char(sysdate, 'mm/dd/yyy') "DATE", to_char(sysdate,
'HH24:MI AM') "TIME",
sum(a.bytes)/1024/1024/1024 "TOTAL_GB", sum(b.bytes)/1024/1024/1024 "USED_GB"
from dba_data_files a, dba_segments b
where a.TABLESPACE_NAME = b.TABLESPACE_NAME
group by a.tablespace_name

Is there a reason for the time to be both 24-Hour and have the AM/PM label?

Robin

1 Like
5

It gives me long details rather the the sum, so it doesn't work for me.

1)

select to_char(sysdate, 'mm/dd/yyy') "DATE", to_char(sysdate, 'HH24:MI AM') "TIME", sum(bytes)/1024/1024/1024 "TOTAL_GB"
from dba_data_files

2)

select sum(bytes)/1024/1024/1024 "USED_GB"
from dba_segments

With scripts, if we put the output of Query 2 right next to Query 1, then that will be the perfect output.

It seems hard to get the output through a single query.

AM/PM is not needed with 24 Hour Format.

Please advise.

6

How about using a PL/SQL?

SET SERVEROUTPUT ON;
DECLARE
        V_DATE          VARCHAR2(10);
        V_TIME          VARCHAR2(10);
        V_TOTAL_GB      dba_data_files.bytes%type;
        V_USED_GB       dba_data_files.bytes%type;
        V_USED_PER      dba_data_files.bytes%type;
BEGIN
        select to_char(sysdate, 'mm/dd/yyyy') into V_DATE from dual;
        select to_char(sysdate, 'HH24:MI') into V_TIME from dual;
        select round(sum(bytes)/1024/1024/1024,4) into V_TOTAL_GB from dba_data_files;
        select round(sum(bytes)/1024/1024/1024,4) into V_USED_GB from dba_segments;
        select round((V_USED_GB * 100)/V_TOTAL_GB,4) into V_USED_PER from dual;
        dbms_output.put_line (V_DATE || ' ' ||  V_TIME || ' ' ||  V_TOTAL_GB || ' ' || V_USED_GB || ' ' || V_USED_PER);
END;
/

Create a CSV instead, so that you can distinguish the fields and read it in a script:

dbms_output.put_line (V_DATE || ',' ||  V_TIME || ',' ||  V_TOTAL_GB || ',' || V_USED_GB || ',' || V_USED_PER);

Also turn off the FEEDBACK:

SET SERVEROUTPUT ON FEEDBACK OFF;
2 Likes
7

That is really great! Appreciate it!

8

To improve bipinajiths solution you could use the utl_file package and write to your outputfile directly.
And for completeness a solution without using PL/SQL:

sqlplus -s / as sysdba <<SQL |grep -v "^$" >> /home/oracle/scripts/dbsizedaily
set heading off feedback off space 0 tab off colsep ','
column d format a11
column t format a6
column total_gb format 99999.9999
column used_gb format 99999.9999
column perc format 999.99
SELECT to_char(sysdate,'MM/DD/YYYY') d,
       to_char(sysdate,'HH24:MI') t,
       total_gb,
       used_gb,
       used_gb*100/total_gb perc
 FROM (SELECT sum(bytes)/1024/1024/1024 total_gb
         FROM dba_data_files),
      (SELECT sum(bytes)/1024/1024/1024 used_gb
         FROM dba_segments);
SQL
1 Like