#!/bin/sh
##设置sqlplus查询输出模式,不输出冗余内容
echo "set heading off" > test.sql
echo "set echo off" >> test.sql
echo "set flush off" >> test.sql
echo "set feedback off" >> test.sql
echo "set pagesize 9999" >> test.sql
echo "set linesize 9999" >> test.sql
echo "set long 9999" >> test.sql
##查询所有表名称和所属用户
echo "select OWNER,TABLE_NAME from all_tab_comments;" >> test.sql
echo "quit;" >> test.sql
##执行并保存结果到文件中
sqlplus sys/123456 as sysdba @test.sql > test.result
##删除前11行
sed -i '1,11d' test.result
##删除倒数2行
sed -i '$d' test.result
sed -i '$d' test.result
##删除空行
sed -i '/^$/d' test.result
##过滤系统用户行
cat test.result | awk '{if( \
!($1~/SYS/) &&\
!($1=="ORDDATA") &&\
!($1~/APEX/) &&\
!($1=="XDB") &&\
!($1=="OUTLN") &&\
!($1=="DBSNMP") &&\
!($1==" B") &&\
!($1=="FLOWS_FILES") &&\
!($1=="SCOTT") ){print $1"."$2}}' > test.new
##依据过滤后的结果,查询表格最后修改时间
echo > last_time.txt
while read line
do
echo > test.result
##设置sqlplus查询输出模式,不输出冗余内容
echo "set heading off" > test.sql
echo "set echo off" >> test.sql
echo "set flush off" >> test.sql
echo "set feedback off" >> test.sql
echo "set pagesize 9999" >> test.sql
echo "set linesize 9999" >> test.sql
echo "set long 9999" >> test.sql
echo "select max(ora_rowscn),to_char(scn_to_timestamp(max(ora_rowscn)),'yyyy-mm-dd hh24:mi:ss') from "$line";" >> test.sql
echo "quit;" >> test.sql
#cat test.sql;
sqlplus sys/123456 as sysdba @test.sql >> test.result
##删除前11行
sed -i '1,11d' test.result
##删除倒数2行
sed -i '$d' test.result
sed -i '$d' test.result
##删除空行
sed -i '/^$/d' test.result
##跳过空表
if [[ $(grep -nw 'NULL' test.result | grep -v grep | wc -l) -eq 1 ]]; then
continue;
fi
##保存到文件
cat test.result >> last_time.txt
done < test.new
sed -i '/^$/d' last_time.txt
cat last_time.txt
##比较scn号,获取最新时间
echo '--------------------------------------------------'
cat last_time.txt | awk 'NR==1{max=$1;last_date=$2;last_time=$3;next}{if(max<$1){max=$1; last_date=$2 ; last_time=$3}}END{print max" "last_date" "last_time}'
执行过程耗时较长,此环境为单机环境没有多少数据,耗时3秒,若是生产环境rac集群数据量较大的环境,时间将更长不少。