oracle统计表记录数（count（*）实现）

oracle统计表记录数

用法：

sh table_count_ora.sh dbname dbuser userpwd

if [ $# -ne 3 ]; then
    echo "usage: $0 dbname dbuser userpwd"
    exit;
fi
dbname=$1
dbuser=$2
userpwd=$3
sqlplus $dbuser/$userpwd@$dbname << !
set linesize 2500;
set pagesize 0;
set sqlnumber off;
set trimspool on;
set feedback off;
#取数据库表名
spool tmp.txt;
select lower(table_name) from user_tables order by 1;
spool off;
!
echo "debug"
sed '/^SQL>/d' tmp.txt > tables.txt
rm tmp.txt

#生成统计各表记录数的sql
> tmp.sql
for i in `cat tables.txt`
do
    echo "select '$i'||'|'||count(*)||'|' from $i
    union all">>tmp.sql
done
sed '$d' tmp.sql >  file.sql
echo ";" >>file.sql
rm tmp.sql

#执行统计各表数据的sql
sqlplus $dbuser/$userpwd@$dbname << END
set linesize 2500;
set pagesize 0;
set sqlnumber off;
set trimspool on;
set feedback off;
set numformat 99999999999.9;
spool tmp.txt
@file.sql
spool off
END
sed '/^SQL>/d' tmp.txt > count_ora.txt
rm tables.txt tmp.txt file.sql