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

oracle统计表记录数

用法：

sh table_count_ora.sh dbname dbuser userpwd

table_count_ora.sh内容如下：

1. if [ $# -ne 3 ]; then
   
2.     echo "usage: $0 dbname dbuser userpwd"
   
3.     exit;
   
4. fi    
   
5. dbname=$1
   
6. dbuser=$2
   
7. userpwd=$3
   
8. sqlplus $dbuser/$userpwd@$dbname << !
   
9. set linesize 2500;
   
10. set pagesize 0;
    
11. set sqlnumber off;
    
12. set trimspool on;
    
13. set feedback off;
    
14. #取数据库表名
    
15. spool tmp.txt;
    
16. select lower(table_name) from user_tables order by 1;
    
17. spool off;
    
18. !
    
19. echo "debug"
    
20. sed '/^SQL>/d' tmp.txt > tables.txt
    
21. rm tmp.txt
    
22. 
    
23. #生成统计各表记录数的sql
    
24. > tmp.sql
    
25. for i in `cat tables.txt`
    
26. do
    
27.     echo "select '$i'||'|'||count(*)||'|' from $i
    
28.     union all">>tmp.sql
    
29. done
    
30. sed '$d' tmp.sql > file.sql
    
31. echo ";" >>file.sql
    
32. rm tmp.sql
    
33. 
    
34. #执行统计各表数据的sql
    
35. sqlplus $dbuser/$userpwd@$dbname << END
    
36. set linesize 2500;
    
37. set pagesize 0;
    
38. set sqlnumber off;
    
39. set trimspool on;
    
40. set feedback off;
    
41. set numformat 99999999999.9;
    
42. spool tmp.txt
    
43. @file.sql
    
44. spool off
    
45. END
    
46. sed '/^SQL>/d' tmp.txt > count_ora.txt
    
47. rm tables.txt tmp.txt file.sql