oracle11g进行数据库迁移后,数据比对靠人工的话比较麻烦,通过如下脚本可以直接取数,获取对象及数据结果文件后,通过notpad++即可进行对比
脚本内容如下
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
#!/bin/bash
#2023/09 by jia.zhenhua
#auto check oracle11g object && data
#########################脚本说明#########################
#1. 脚本直接执行即可,不需要手工定义变量;脚本执行完成后,对象结果文件存放在/tmp/object_check_result.txt;数据结果文件存放在/tmp/data_check_result.txt
#2. 如果需要调整结果文件存放位置,那么调整全局变量中的 object_result 及 data_result 即可
#########################全局变量#########################
#定义对象结果存放文件,存放所有对象数量
object_result=/tmp/object_check_result.txt
#定义表数据结果存放文件,存放所有表行数
data_result=/tmp/data_check_result.txt
#定义系统自带用户
system_user="('DIP','MDDATA','SCOTT','SPATIAL_WFS_ADMIN_USR','SPATIAL_CSW_ADMIN_USR','ORACLE_OCM','XS$NULL','DBSNMP','SI_INFORMTN_SCHEMA','ORDPLUGINS','CTXSYS','ORDSYS','XDB','EXFSYS','DMSYS','SYSMAN','ANONYMOUS','WMSYS','MDSYS','OLAPSYS','APPQOSSYS','ORDDATA','SYSTEM','SYS','MGMT_VIEW','OUTLN','TSMSYS','OWBSYS','OWBSYS_AUDIT','APEX_030200','FLOWS_FILES','APEX_PUBLIC_USER','GGADMCAP')"
#########################脚本主体#########################
function get_blank(){
name_length=$(echo ${1}|awk '{print length($0)}')
let blank_length=${2}-${name_length}
reality_blank=$(seq -s " " $[${blank_length}+1]|sed 's/[0-90]//g')
echo -e "${1}${reality_blank}|"
}
####确认目录及文件
#创建存放对象结果文件
if [ -f "$object_result" ];then
echo "存放对象结果文件文件已存在,清空该文件"
cat /dev/null > $object_result
else
echo "存放对象结果文件文件不存在,创建该文件"
touch $object_result
fi
#创建存放数据结果文件
if [ -f "$data_result" ];then
echo "存放数据结果文件文件已存在,清空该文件"
cat /dev/null > $data_result
else
echo "存放数据结果文件文件不存在,创建该文件"
touch $data_result
fi
#1. 获取所有对象信息
echo "开始获取object信息"
#%-10d指定第一列序号的长度
objects_sum=`echo -e "set line 300 pages 999 long 999\n col OWNER for a30\n col OBJECT_TYPE for a30\n select owner,object_type,status,count(*) from dba_objects where owner not in $system_user group by owner,object_type,status order by owner,object_type,status;" | sqlplus -S / as sysdba | grep -v 'selected'`
echo -e '\n' >> $object_result
if [ -n "$objects_sum" ];then
echo "object对象类型、状态、数量详细信息:" >> $object_result
echo -e '\n' >> $object_result
echo "$objects_sum" >> $object_result
echo -e '\n' >> $object_result
else
echo "object对象数量:0" >> $object_result
echo -e '\n' >> $object_result
fi
echo "object信息获取完成"
#2. 循环获取数据库中每张表的数据量
#定义username变量,获取所有username名称(已排除系统用户)
user_name_list=`echo -e "select distinct USERNAME from dba_users where USERNAME not in $system_user;" | sqlplus -S / as sysdba | grep -v "USERNAME\|^$\|selected\|-"`
echo "开始取数"
#循环获取每个表数量并打印
for us in $user_name_list
do
tb_name_list=`echo -e "select distinct TABLE_NAME from dba_tables where owner in ('$us');" | sqlplus -S / as sysdba | grep -v "TABLE_NAME\|^$\|selected\|-"`
#若tb_name_list非空,则进入循环
if [[ -n "$tb_name_list" ]];then
for i in $tb_name_list
do
tb_count=`echo -e "select /*+ parallel(4) */ count(*) from $us.$i;" | sqlplus -S / as sysdba | grep -v "COUNT\|^$\|-" | column -t`
x=$(get_blank $us 30)
y=$(get_blank $i 60)
echo "表$us.$i取数完成"
echo "$x$y$tb_count" >> $data_result
done
fi
done
echo "取数结束"
echo "脚本执行结束"
标签:文件,name,data,object,echo,result,&&,数据,oracle11g From: https://www.cnblogs.com/jzhsw/p/17712548.html