查询database和table占用空间的脚本

cruelsun

其实很简单，适合新手。欢迎大家提意见哦。

1. 
   
2. #!/usr/bin/sh
   
3. 
   
4. if [ $# -ne 1 ]
   
5. then
   
6.   echo "  Usage: dbsize.sh DBNAME "
   
7.   exit
   
8. fi
   
9. 
   
10. DBNAME=$1; export DBNAME
    
11. TMPFILE=/tmp/dbsize.tmp
    
12. RESULT=dbsize.out
    
13. export TMPFILE RESULT
    
14. 
    
15. dbaccess << !
    
16. database sysmaster;
    
17. unload to '$TMPFILE' delimiter "|"
    
18. select  dbsname,
    
19.         tabname,
    
20.         count(*) num_of_extents,
    
21.         sum( pe_size ) total_size
    
22. from systabnames, sysptnext
    
23. where partnum = pe_partnum and dbsname="$DBNAME"
    
24. group by 1, 2
    
25. order by 3 desc, 4 desc;
    
26. !
    
27. 
    
28. awk -F '|' ' \
    
29. BEGIN {
    
30.   size=0;
    
31. }
    
32. {
    
33.   size = size + $4
    
34. }
    
35. END {
    
36.   printf (" The Database Size of %s is %d pages. \n\n", ENVIRON["DBNAME"], size )
    
37. }
    
38. ' $TMPFILE >; $RESULT
    
39. 
    
40. cat $TMPFILE >;>; $RESULT
    
41. view $RESULT
    
42. rm -f $TMPFILE
    
43. 
    

复制代码

czw1413_cn

shell写不错,

顶一下

lmtok

不错，不错，收下来。

顺便顶一下。

horse

顶一下。

gzdaily_sap

如何使用该程序？
使用的时候需要修改吗？

cruelsun

将代码保存为文本文件，文件名称建议为dbsize.sh，
执行 chmod +rx dbsize.sh 赋可执行权限。
要求执行的用户具有连接数据库权限，并设置了INFORMIXDIR INFORMIXSERVER等informix环境变量。

基本上不需要修改即可使用，当然，你也可以修改它使它更完善。

onlywxw

执行时只要写上：dbsize.sh databasename就可以了是吧？

onlywxw

我在执行时报201错误：
-201    A syntax error has occurred.

This general error message indicates mistakes in the form of an SQL
statement. Look for missing or extra punctuation (such as missing or
extra commas, omission of parentheses around a subquery, and so on),
keywords misspelled (such as VALEUS for VALUES), keywords misused (such
as SET in an INSERT statement or INTO in a subquery), keywords out of
sequence (such as a condition of "value IS NOT" instead of "NOT value
IS", or a reserved word used as an identifier.

Database servers that provide full NIST compliance do not reserve any
words; queries that work with these database servers might fail and
return error -201 when they are used with earlier versions of Informix
database servers.

The cause of this error might be an attempt to use round-robin syntax with
CREATE INDEX or ALTER FRAGMENT INIT on an index. You cannot use round-robin
indexes.

请cruelsun老兄看看是什么问题啊？

gzdaily_sap

什么是“INFORMIXDIR INFORMIXSERVER等informix环境变量”
能具体一点吗？
root用户行吗？

gzdaily_sap

试过了，我在/usr/bin创建了dbsize.sh，用的是db2，name is DEV
/usr/bin dbsize.sh DEV
/usr/bin/dbsize.sh[14]: dbaccess:  not found.
还差点什么？