Shell Programming and Scripting

#!/bin/bash
export ORACLE_SID=plgc1
export ORACLE_HOME=/opt/oracle/product/11.2.0.2/db_1
export PATH=$PATH:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/opt/dell/srvadmin/bin:/home/oracle/bin:/usr/local/bin:$ORACLE_HOME


##for VARIABLE in sat11cr sat11cru sat11dm sat11dmu saos14 sat14cru sabizos sat14dmu
##for VARIABLE in sat11dm sat11dmu
#for VARIABLE in saos14 sat14cru
for VARIABLE in sabizos sat14dmu

do

/opt/oracle/product/11.2.0.2/db_1/bin/sqlplus -s /nolog << EOF

conn oradba/"password"@${VARIABLE}
@insert_table_counts_master.sql
spool ${VARIABLE}_table_count.csv
select name||','||cnt from table_count_${VARIABLE} order by name;
spool off

EOF

done

exit

set pagesize 0
set linesize 200
set verify off
set echo off
set feedback off
set termout off
set trimspool on
truncate table table_count_${VARIABLE};
spool insert_count_${VARIABLE}.sql
select 'insert into table_count_${VARIABLE} '||chr(10)||'select '''||owner||'.'||table_name||''', count(*) from '||owner||'.'||table_name||';'||chr(10)||'commit;'
from dba_tables
where owner in ('DATAOWNER','DSMOWNERPLI')
and table_name not like 'USLOG%'
and table_name not like 'MLOG%'
-- and num_rows < 10000000
-- and num_rows <> 0
order by 1;
spool off
@insert_count_${VARIABLE}.sql

insert into table_count_${VARIABLE}
select 'DSMOWNERPLI.ADMINUSERS', count(*) from DSMOWNERPLI.ADMINUSERS;
commit;

insert into table_count_${VARIABLE}
select 'DSMOWNERPLI.DSM_CAS', count(*) from DSMOWNERPLI.DSM_CAS;
commit;