Shell Programming and Scripting

#####################################################
-- Author : Mohammed Fareed.
-- Script : log_switch.sql
-- Description : Get log switches on total , daily and hourly basis 
set heading off;
select '******************************************************' from dual;
select '****  Redolog Daily and Hourly volume calculated  ****' from dual;
select '******************************************************' from dual;
timing start Redovol;

--#######################################################################################
--##           PL/SQL used here to gather and display average redo volumes             ##
--#######################################################################################
set serveroutput on;
declare
v_log    number;
v_days   number;
v_logsz  number;
v_adsw   number;
V_advol  number;
v_ahsw   number;
v_ahvol  number;

begin
select count(first_time) into v_log from v$log_history;
select count(distinct(to_char(first_time,'dd-mon-rrrr'))) into v_days from v$log_history;
select max(bytes)/1024/1024 into v_logsz from v$log;
v_adsw := round(v_log / v_days);
v_advol := round(v_adsw * v_logsz);
v_ahsw := round(v_adsw / 24);
v_ahvol := round((v_adsw / 24 )) * v_logsz;
dbms_output.put ('Total Switches' || ' '||v_log||'  ==>  ');
dbms_output.put ('Total Days' || ' '|| v_days||'  ==>  ');
dbms_output.put_line ('Redo Size' || ' ' || v_logsz);
dbms_output.put ('Avg Daily Switches' || ' ' || v_adsw||'  ==>  ');
dbms_output.put_line ('Avg Daily Volume in Meg' || ' ' || v_advol);
dbms_output.put ('Avg Hourly Switches' || ' ' || v_ahsw||'  ==>  ');
dbms_output.put_line ('Avg Hourly Volume in Meg' || ' ' || v_ahvol);

end;
/
--#######################################################################################
--##                                                  END of PL/SQL routine            ##
--#######################################################################################
 
timing stop Redovol;
 
###########################################################

SELECT tablespace_name,table_name 
  FROM user_tables;

tablename1:datecolumnname1
tablename2:datecolumnname2
...

TABLE_DEF=/path/to/your/file.that.lists.tables.and.datecolumns
DB_USER=your_db_user
DB_PASSWORD=your_password

OLD_IFS=$IFS
IFS=:
while read TABLE_NAME DATE_COLUMN
do
   sqlplus -s $DB_USER/$DB_PASSWORD@$ORACLE_SID <<EOF
set heading off
set feedback off
prompt Dataflow for table $TABLE_NAME
select 'Total number of rows',count(*)
  from $TABLE_NAME;
select 'Number of rows inserted today',count(*)
  from $TABLE_NAME
 where trunc($DATE_COLUMN) = trunc(sysdate);
prompt Number of rows per hour today, peak hour first
set heading on
select to_char($DATE_COLUMN,'HH24') "hr",count(*) "rows inserted"
  from $TABLE_NAME
 where trunc($DATE_COLUMN) = trunc(sysdate)
 group by to_char($DATE_COLUMN,'HH24')
 order by 2 desc;
set heading off
select 'Number of rows inserted this week',count(*)
  from $TABLE_NAME
 where trunc($DATE_COLUMN,'IW') = trunc(sysdate,'IW');
EOF
done <$TABLE_DEF
IFS=$OLD_IFS