文章目录
- 1. 数据挖掘：/etc/rc.local，sudo su
- 2.http协议：优先wget
- 3.非结构化数据存储：blob，pzhrain24file
- 4.数据管理子系统：数据字典表
- 5.监控告警子系统：tmpfs，top，/proc/stat，/proc/meminfo，mount ，processes
- 5.1 du，df，fdisk，lsblk：磁盘=硬盘
- 5.2 文件：建表入表，传输
数据挖掘dmining和数据交换exptables差不多（exptables自己设计数据表有keyid字段，从其他地方拿数据未必有keyid字段需要指定，也就是数据是增量的，但不提供增量提取方式即没有keyid字段），将数据从数据库中取出导入文件（执行一个sql拿出数据）。vi /etc/rc.local（随操作系统自启动）。
vi /htidc/gz…sh上面如下，如下要写的其实就是< selectsql >字段里内容。比如取近一小时数据，每次取到都有重复，但在入库时（获取到的数据生成xml再入自己库）处理重复就行。
#include "_public.h"
#include "_ooci.h"
struct st_arg
{char connstr
[101];char charset
[51];char tname
[51];char cols
[1001];char fieldname
[1001];char fieldlen
[501];int exptype
;char andstr
[501];char bname
[51];char ename
[51];char idfieldname
[51];char idfilename
[301];char exppath
[301];int timetvl
;
} starg
;
clogfile logfile
;
connection conn
;
bool
_dmintables();
void exit(int sig
);
vector
<string
> vfieldname
;
vector
<int> vfieldlen
;
int maxfieldlen
;
void splitfields();
void _help(char *argv
[]);
long maxkeyid
;
bool
loadmaxkeyid();
bool
uptmaxkeyid();
bool
_xmltoarg(char *strxmlbuffer
);int main(int argc
,char *argv
[])
{if (argc
!=3) { _help(argv
); return -1; }closeioandsignal();signal(sigint
,exit
); signal(sigterm
,exit
);if (logfile
.open(argv
[1],"a ")==false
){printf("打开日志文件失败（%s）。\n",argv
[1]); return -1;} if (_xmltoarg(argv
[2])==false
) return -1; while (true
){if (conn
.connecttodb(starg
.connstr
,starg
.charset
) != 0){logfile
.write("connect database %s failed.\n",starg
.connstr
); sleep(starg
.timetvl
); continue;}if (_dmintables() == false
) logfile
.write("export tables failed.\n");conn
.disconnect(); sleep(starg
.timetvl
);}return 0;
}
void exit(int sig
)
{logfile
.write("程序退出，sig=%d\n\n",sig
);exit(0);
}
void _help(char *argv
[])
{printf("\n");printf("using:/htidc/public/bin/dminoracle logfilename xmlbuffer\n\n");printf("增量挖掘示例：\n");printf("sample:/htidc/public/bin/dminoracle /log/shqx/dminoracle_surfdata_from_qx.log \"shqx/pwdidc@snorcl11g_198simplified chinese_china.zhs16gbkt_surfdataobtid,to_char(ddatetime,'yyyymmddhh24miss'),t,p,u,wd,wf,r,visobtid,ddatetime,t,p,u,wd,wf,r,vis5,14,8,8,8,8,8,8,81 and obtid in ('59293','50745')surfdata__from_qx/data/dmin/surfdata_from_qx.txtkeyid/data/shqx/sdata/fromqx30\"\n\n");printf("全量挖掘示例：\n");printf("sample:/htidc/public/bin/dminoracle /log/shqx/dminoracle_obtcode_from_qx.log \"shqx/pwdidc@snorcl11g_198simplified chinese_china.zhs16gbkt_obtcodeobtid,obtname,provname,lat,lon,heightobtid,obtname,provname,lat,lon,height5,30,30,8,8,82 and rsts=1 and obtid in ('59293','50745')obtcode__from_qx/data/shqx/sdata/fromqx300\"\n\n");printf("本程序是数据中心的公共功能模块，从其它业务系统的数据库中挖掘数据，用于入库到数据中心。\n");printf("logfilename是本程序运行的日志文件。\n");printf("xmlbuffer为文件传输的参数，如下：\n");printf("数据库的连接参数 shqx/pwdidc@snorcl11g_198\n");printf("数据库的字符集 simplified chinese_china.zhs16gbk 这个参数要与数据源数据库保持>一致，否则会出现中文乱码的情况。\n");printf("待挖掘数据的表名 t_surfdata\n");printf("需要挖掘字段的列表 obtid,to_char(ddatetime,'yyyymmddhh24miss'),t,p,u,wd,wf,r,vis 可以采用函数。\n");printf("挖掘字段的别名列表 obtid,ddatetime,t,p,u,wd,wf,r,vis 必须与cols一一对应。\n");printf("挖掘字段的长度列表 5,14,8,8,8,8,8,8,8 必须与cols一一对应。\n");printf("挖掘数据的方式 1 1-增量挖掘；2-全量挖掘，如果是增量挖掘，要求表一定要有表达记录序号的id字段。\n");printf("挖掘数据的附加条件 and obtid in ('59293','50745') 注意，关键字and不能少。\n");printf("数据文件的命名的前部分 surfdata_\n");printf("数据文件的命名的后部分 _from_qx\n");printf("挖掘数据表记录号字段名 keyid 当exptype=1时该参数有效。\n");printf("已挖掘数据id保存的文件名 /data/dmin/surfdata_from_qx.txt 当exptype=1时该参数有效。\n");printf("挖掘文件存放的目录 /data/shqx/sdata/fromqx\n");printf("挖掘数据的时间间隔 30 单位：秒，建议大于10。\n");printf("以上参数，除了idfieldname、idfilename和andstr，其它字段都不允许为空。\n\n\n");
}
bool
_xmltoarg(char *strxmlbuffer
)
{memset(&starg
,0,sizeof(struct st_arg));getxmlbuffer(strxmlbuffer
,"connstr",starg
.connstr
);if (strlen(starg
.connstr
)==0) { logfile
.write("connstr is null.\n"); return false
; }getxmlbuffer(strxmlbuffer
,"charset",starg
.charset
);if (strlen(starg
.charset
)==0) { logfile
.write("charset is null.\n"); return false
; }getxmlbuffer(strxmlbuffer
,"tname",starg
.tname
);if (strlen(starg
.tname
)==0) { logfile
.write("tname is null.\n"); return false
; }getxmlbuffer(strxmlbuffer
,"cols",starg
.cols
);if (strlen(starg
.cols
)==0) { logfile
.write("cols is null.\n"); return false
; }getxmlbuffer(strxmlbuffer
,"fieldname",starg
.fieldname
);if (strlen(starg
.fieldname
)==0) { logfile
.write("fieldname is null.\n"); return false
; }getxmlbuffer(strxmlbuffer
,"fieldlen",starg
.fieldlen
);if (strlen(starg
.fieldlen
)==0) { logfile
.write("fieldlen is null.\n"); return false
; }getxmlbuffer(strxmlbuffer
,"exptype",&starg
.exptype
);if ( (starg
.exptype
!=1) && (starg
.exptype
!=2) ) { logfile
.write("exptype is not in (1,2).\n"); return false
; }getxmlbuffer(strxmlbuffer
,"andstr",starg
.andstr
);if (strlen(starg
.andstr
)==0) { logfile
.write("andstr is null.\n"); return false
; }getxmlbuffer(strxmlbuffer
,"bname",starg
.bname
);if (strlen(starg
.bname
)==0) { logfile
.write("bname is null.\n"); return false
; }getxmlbuffer(strxmlbuffer
,"ename",starg
.ename
);if (strlen(starg
.ename
)==0) { logfile
.write("ename is null.\n"); return false
; }getxmlbuffer(strxmlbuffer
,"idfieldname",starg
.idfieldname
);if ( (starg
.exptype
==1) && (strlen(starg
.idfieldname
)==0) ) { logfile
.write("idfieldname is null.\n"); return false
; }getxmlbuffer(strxmlbuffer
,"idfilename",starg
.idfilename
);if ( (starg
.exptype
==1) && (strlen(starg
.idfilename
)==0) ) { logfile
.write("idfilename is null.\n"); return false
; }getxmlbuffer(strxmlbuffer
,"exppath",starg
.exppath
);if (strlen(starg
.exppath
)==0) { logfile
.write("exppath is null.\n"); return false
; }getxmlbuffer(strxmlbuffer
,"timetvl",&starg
.timetvl
);if (starg
.timetvl
==0) { logfile
.write("timetvl is null.\n"); return false
; }splitfields();if (vfieldname
.size() != vfieldlen
.size() ) { logfile
.write("fieldname和fieldlen的元素个数不同。.\n"); return false
; }return true
;
}
bool
_dmintables()
{if (loadmaxkeyid()==false
) { logfile
.write("loadmaxkeyid() failed.\n"); return false
; }char strsql
[4096]; char fieldvalue
[vfieldname
.size()][maxfieldlen
1]; memset(strsql
,0,sizeof(strsql
));if (starg
.exptype
==1)sprintf(strsql
,"select %s,%s from %s where 1=1 and %s>%ld %s order by %s",starg
.cols
,starg
.idfieldname
,starg
.tname
,starg
.idfieldname
,maxkeyid
,starg
.andstr
,starg
.idfieldname
);elsesprintf(strsql
,"select %s from %s where 1=1 %s",starg
.cols
,starg
.tname
,starg
.andstr
);sqlstatement
stmt(&conn
);stmt
.prepare(strsql
);for (int ii
=0;ii
<vfieldname
.size();ii
){stmt
.bindout(ii
1,fieldvalue
[ii
],vfieldlen
[ii
]);} if (starg
.exptype
==1) stmt
.bindout(vfieldname
.size()1,&maxkeyid
); if (stmt
.execute() != 0) {logfile
.write("select %s failed.\n%s\n%s\n",starg
.tname
,stmt
.m_cda
.message
,stmt
.m_sql
); return false
;}int ifileseq
=1; char strfilename
[301],strlocaltime
[21];cfile file
;while (true
){memset(fieldvalue
,0,sizeof(fieldvalue
)); if (stmt
.next() !=0) break; if (file
.isopened()==false
) {memset(strlocaltime
,0,sizeof(strlocaltime
));localtime(strlocaltime
,"yyyymmddhh24miss");memset(strfilename
,0,sizeof(strfilename
));sprintf(strfilename
,"%s/%s%s%s_%d.xml",starg
.exppath
,starg
.bname
,strlocaltime
,starg
.ename
,ifileseq
);if (file
.openforrename(strfilename
,"w")==false
){logfile
.write("file.openforrename(%s) failed.\n",strfilename
); return false
;}file
.fprintf("\n");}for (int ii
=0;ii
<vfieldname
.size();ii
){file
.fprintf("<%s>%s",vfieldname
[ii
].c_str(),fieldvalue
[ii
],vfieldname
[ii
].c_str());}file
.fprintf("\n");if (stmt
.m_cda
.rpc
%1000==0) {file
.fprintf("\n");if (file
.closeandrename()==false
){logfile
.write("file.closeandrename(%s) failed.\n",strfilename
); return false
;}if (uptmaxkeyid()==false
) { logfile
.write("uptmaxkeyid() failed.\n"); return false
; }logfile
.write("create file %s ok.\n",strfilename
);}}if (file
.isopened()==true
){file
.fprintf("\n");if (file
.closeandrename()==false
){logfile
.write("file.closeandrename(%s) failed.\n",strfilename
); return false
;}if (uptmaxkeyid()==false
) { logfile
.write("uptmaxkeyid() failed.\n"); return false
; }logfile
.write("create file %s ok.\n",strfilename
);}if (stmt
.m_cda
.rpc
>0) logfile
.write("本次挖掘了%d条记录。\n",stmt
.m_cda
.rpc
);return true
;
}
void splitfields()
{vfieldname
.clear(); vfieldlen
.clear(); maxfieldlen
=0; ccmdstr cmdstr
;cmdstr
.splittocmd(starg
.fieldname
,",");vfieldname
.swap(cmdstr
.m_vcmdstr
);int ifieldlen
=0;cmdstr
.splittocmd(starg
.fieldlen
,",");for (int ii
=0;ii
<cmdstr
.cmdcount();ii
){ cmdstr
.getvalue(ii
,&ifieldlen
);if (ifieldlen
>maxfieldlen
) maxfieldlen
=ifieldlen
; vfieldlen
.push_back(ifieldlen
);}
}
bool
loadmaxkeyid()
{if (starg
.exptype
!=1) return true
;cfile file
;if (file
.open(starg
.idfilename
,"r")==false
) {logfile
.write("注意，%s文件不存在，程序将从新开始挖掘数据。\n",starg
.idfilename
); return true
;}char strbuf
[21];memset(strbuf
,0,sizeof(strbuf
));file
.fread(strbuf
,20);maxkeyid
=atol(strbuf
);logfile
.write("maxkeyid=%d\n",maxkeyid
);return true
;
}
bool
uptmaxkeyid()
{if (starg
.exptype
!=1) return true
;cfile file
;if (file
.open(starg
.idfilename
,"w")==false
) {logfile
.write("file.open(%s) failed.\n",starg
.idfilename
); return false
;}file
.fprintf("%ld",maxkeyid
);return true
;
}
数据共享平台也有一个web系统（服务端）用java做的，vi /etc/rc.local如下。
如下http客户端访问不了，startup后可访问。
按照http格式向网站发报文也会得到回应，如下http客户端。
wgetclient是用wget命令下载，httpclient下载的内容有时候会有断行，wget下载不会。优先wget，wget下载不了的用http。wget支持http，https，ftp，sftp等协议。linux下同步文件rsync，curl，wget，ftp，sftp。
如上调用接口，如下还可以右击网页上图片复制图片地址。
程序在后台跑，有新图就拿下来，wgetclient将网页内容全弄下来，搞清图片命名规律进行解析。
#include "_public.h"
void exit(int sig
);
clogfile logfile
;
int main(int argc
, char *argv
[])
{if(argc
!=6){printf("usage:%s weburl tmpfilename outputfilename logfilename charset\n",argv
[0]); printf("本程序用于获取web网页的内容。\n");printf("weburl 网页web的地址。\n");printf("tmpfilename 获取到的网页的内容存放的全路径的临时文件名，该文件可能是utf-8或其它编码。\n");printf("outputfilename 最终的输出文件全路径文件名，该文件是gb18030编码，注意tmpfilename被转换为outputfilename后，tmpfilename文件被自动删除。\n");printf("logfilename 本程序的运行产生的日志文件名。\n");printf("charset 网页的字符集，如utf-8\n\n");exit(1);}closeioandsignal(); signal(sigint
,exit
); signal(sigterm
,exit
);if (logfile
.open(argv
[4],"a ") == false
){printf("logfile.open(%s) failed.\n",argv
[4]); return -1;}mkdir(argv
[2],true
); mkdir(argv
[3],true
);char strweburl
[3001];memset(strweburl
,0,sizeof(strweburl
));strncpy(strweburl
,argv
[1],3000);char strcmd
[3024];memset(strcmd
,0,sizeof(strcmd
));snprintf(strcmd
,3000,"/usr/bin/wget -c -q -o %s \"%s\" 1>>/dev/null 2>>/dev/null",argv
[2],strweburl
);system(strcmd
);logfile
.write("%s\n",strcmd
);char strfilenametmp
[301];memset(strfilenametmp
,0,sizeof(strfilenametmp
));snprintf(strfilenametmp
,300,"%s.tmp",argv
[3]);memset(strcmd
,0,sizeof(strcmd
));snprintf(strcmd
,256,"iconv -c -f %s -t gb18030 %s -o %s",argv
[5],argv
[2],strfilenametmp
);system(strcmd
);logfile
.write("%s\n",strcmd
);remove(argv
[2]); rename(strfilenametmp
,argv
[3]);return 0;
}void exit(int sig
)
{if (sig
> 0) signal(sig
,sig_ign
);logfile
.write("catching the signal(%d).\n",sig
);logfile
.write("wgetclient exit.\n");exit(0);
}
#include "_public.h"
void exit(int sig
);
clogfile logfile
;
bool
geturl(char *strbuffer
,char *strurl
,char *strfilename
);
int main(int argc
, char *argv
[])
{if(argc
!=4){printf("usage:%s logfilename tmpfilename outputfilename\n",argv
[0]); printf("sample:./wgetrain24 /log/shqx/wgetrain24.log /data/wgettmp /data/wfile/zhrain24\n\n");printf("本程序用于从中国天气网获取逐小时降雨量实况图。\n");printf("中国天气网的url是http://products.weather.com.cn/product/index/index/procode/jc_jsl_zh.shtml\n");printf("如果中国天气网的url改变，程序也在做改动。\n");printf("logfilename 本程序的运行产生的日志文件名。\n");printf("tmpfilename 本程序运行产生的临时文件存放的目录。\n");printf("获取逐小时降雨量实况图存放的目录。\n\n");exit(1);}closeioandsignal(); signal(sigint
,exit
); signal(sigterm
,exit
);if (logfile
.open(argv
[1],"a ") == false
){printf("logfile.open(%s) failed.\n",argv
[1]); return -1;}mkdir(argv
[2],false
); mkdir(argv
[3],false
);while (true
){ char strwgetclient
[2001]; memset(strwgetclient
,0,sizeof(strwgetclient
));snprintf(strwgetclient
,2000,"/htidc/public/bin/wgetclient \"http://products.weather.com.cn/product/index/index/procode/jc_jsl_zh.shtml\" %s/wgetclient_%d.tmp %s/wgetclient_%d.html %s/wgetclient.log utf-8",argv
[2],getpid(),argv
[2],getpid(),argv
[2]);system(strwgetclient
);char stroutputfile
[301];memset(stroutputfile
,0,sizeof(stroutputfile
));snprintf(stroutputfile
,300,"%s/wgetclient_%d.html",argv
[2],getpid());cfile file
;if (file
.open(stroutputfile
,"r")==false
){logfile
.write("file.open(%s) failed.\n",stroutputfile
); sleep(60); continue;} char strbuffer
[1001],strurl
[501],strfullfilename
[301],strfilename
[101]; while (true
){memset(strbuffer
,0,sizeof(strbuffer
));memset(strurl
,0,sizeof(strurl
));memset(strfullfilename
,0,sizeof(strfullfilename
));memset(strfilename
,0,sizeof(strfilename
)); if (file
.fgets(strbuffer
,1000)==false
) break; if (matchfilename(strbuffer
,"*pwcp_twc_weap_sfer_er1_twc_l88_p9_20*.jpg*")==false
) continue; if (geturl(strbuffer
,strurl
,strfilename
)==false
) continue; snprintf(strfullfilename
,300,"%s/%s",argv
[3],strfilename
); if (access(strfullfilename
,f_ok
)==0) continue; logfile
.write("download %s ",strfilename
); memset(strwgetclient
,0,sizeof(strwgetclient
));snprintf(strwgetclient
,500,"wget \"%s\" -o %s/wgetrain24.log -o %s",strurl
,argv
[2],strfullfilename
);system(strwgetclient
); if (access(strfullfilename
,f_ok
)==0) logfile
.writeex("ok.\n");else logfile
.writeex("failed.\n");} file
.closeandremove();sleep(60);}return 0;
}bool
geturl(char *strbuffer
,char *strurl
,char *strfilename
)
{char *start
,*end
;start
=end
=0;if ((start
=strstr(strbuffer
,"http"))==0) return false
;if ((end
=strstr(start
,"\""))==0) return false
; strncpy(strurl
,start
,end
-start
);strcpy(strfilename
,strstr(strurl
,"pwcp"));return true
;
}
void exit(int sig
)
{if (sig
> 0) signal(sig
,sig_ign
);logfile
.write("catching the signal(%d).\n",sig
);logfile
.write("wgetclient exit.\n");exit(0);
}
#include "_ooci.h"
int main(int argc
,char *argv
[])
{connection conn
; if (conn
.connecttodb("scott/tiger@snorcl11g_198","simplified chinese_china.zhs16gbk") != 0){printf("connect database %s failed.\n%s\n","scott/tiger@orcl",conn
.m_cda
.message
); return -1;} sqlstatement
stmt(&conn
); stmt
.prepare("\begin\delete from goods;\insert into goods(id,name,pic) values(1,'商品名称',empty_blob());\end;"); if (stmt
.execute() != 0){printf("stmt.execute() failed.\n%s\n%s\n",stmt
.m_sql
,stmt
.m_cda
.message
); return -1;}stmt
.prepare("select pic from goods where id=1 for update");stmt
.bindblob();if (stmt
.execute() != 0){printf("stmt.execute() failed.\n%s\n%s\n",stmt
.m_sql
,stmt
.m_cda
.message
); return -1;}if (stmt
.next() != 0) return 0; if (stmt
.filetolob((char *)"pic_in.jpg") != 0){printf("stmt.filetolob() failed.\n%s\n",stmt
.m_cda
.message
); return -1;} conn
.commit(); return 0;
}
#include "_public.h"
#include "_ooci.h"
#include "_shqx.h"
clogfile logfile
;
cdir dir
;
bool
_pzhrain24file(char *strargv2
,char *strargv4
,char *strargv5
);
connection conn
;
void exit(int sig
);int main(int argc
,char *argv
[])
{if (argc
!=7){printf("\n本程序用于处理全国逐小时雨量实况图片文件。\n\n");printf("/htidc/shqx/bin/pzhrain24file logfilename connstr srcpathname dstpathname tname timetvl\n");printf("例如：/htidc/shqx/bin/pzhrain24file /log/shqx/pzhrain24file.log shqx/pwdidc@snorcl11g_198 /data/wfile/zhrain24 /qxfile/zhrain24 t_zhrain24 30\n");printf("logfilename 本程序运行的日志文件名。\n");printf("connstr 数据库的连接参数。\n");printf("srcpathname 原始文件存放的目录，文件命名如pwcp_twc_weap_sfer_er1_twc_l88_p9_20191101070000000.jpg。\n");printf("dstpathname 目标文件存放的目录，文件按yyyy/mm/dd组织目录，重命名为zhrain24_yyyymmddhh24miss.jpg。\n");printf("tname 数据存放的表名。\n");printf("timetvl 本程序运行的时间间隔，单位：秒。\n");return -1;}closeioandsignal();signal(sigint
,exit
); signal(sigterm
,exit
);if (logfile
.open(argv
[1],"a ")==false
){printf("打开日志文件失败（%s）。\n",argv
[1]); return -1;}logfile
.write("程序启动。\n");while (true
){if (dir
.opendir(argv
[3],"pwcp_twc_weap_sfer_er1_twc_l88_p9_20*.jpg",1000,true
,true
)==false
){logfile
.write("dir.opendir(%s) failed.\n",argv
[3]); sleep(atoi(argv
[6])); continue;} while (true
) {if (dir
.readdir()==false
) break;if (_pzhrain24file(argv
[2],argv
[4],argv
[5])==false
) {logfile
.writeex("失败。\n"); continue;}}if (conn
.m_state
==1) conn
.disconnect(); sleep(atoi(argv
[6]));}return 0;
}
void exit(int sig
)
{logfile
.write("程序退出，sig=%d\n\n",sig
);exit(0);
}
bool
_pzhrain24file(char *strargv2
,char *strargv4
,char *strargv5
)
{char strddatetime
[21]; memset(strddatetime
,0,sizeof(strddatetime
));strncpy(strddatetime
,strstr(dir
.m_filename
,"20"),14);char strdstfilename
[301]; memset(strdstfilename
,0,sizeof(strdstfilename
));snprintf(strdstfilename
,300,"zhrain24_%s.jpg",strddatetime
);char strdstfilepath
[301]; memset(strdstfilepath
,0,sizeof(strdstfilepath
));snprintf(strdstfilepath
,300,"%s/",strargv4
);strncat(strdstfilepath
,strddatetime
,4); strcat(strdstfilepath
,"/"); strncat(strdstfilepath
,strddatetime
4,2); strcat(strdstfilepath
,"/"); strncat(strdstfilepath
,strddatetime
6,2); strcat(strdstfilepath
,"/"); char strfulldstfilename
[301]; memset(strfulldstfilename
,0,sizeof(strfulldstfilename
));snprintf(strfulldstfilename
,300,"%s%s",strdstfilepath
,strdstfilename
);if (access(strfulldstfilename
,f_ok
) == 0) return true
;if (conn
.m_state
==0){if (conn
.connecttodb(strargv2
,"simplified chinese_china.zhs16gbk")!=0){logfile
.write("connect database(%s) failed.\n%s\n",strargv2
,conn
.m_cda
.message
); return false
;}}if (copy(dir
.m_fullfilename
,strfulldstfilename
)==false
) {logfile
.write("复制文件copy(%s,%s)...failed.\n",dir
.m_fullfilename
,strfulldstfilename
); return false
;}if (filetotable(&conn
,&logfile
,strargv5
,strfulldstfilename
,strddatetime
)!=0){logfile
.write("把文件%s存入%s...failed.\n",strfulldstfilename
,strargv5
); return false
;}logfile
.write("把文件%s存入%s...ok.\n",strfulldstfilename
,strargv5
); return true
;
}
oracle数据字典：是一组表和视图的结构（就像仓库里有什么，每种有多少，哪些空间可用等日记）。自己创建的表，oracle会将这表的信息自动写入系统表，系统表上再创建一视图供客户查看。数据字典中的表是不能被访问修改（自己用的），但是可以访问数据字典的视图就可以知道自己创建的表的详细信息。
tab也是视图，tab里面表，视图，同义词都可以查到。若是select * from dba_tables；必须是dba用户才可以查，三类user_，all_，dba_
数据量大了会出现性能和数据管理，迁移，备份问题。假如有1亿数据量，不能用exp导出，一个文件有几百g导出要一天。索引设计要合理，不然table scan跑不动。一亿以下的数据单表存放，一亿以上的数据考虑分表，十亿虽然比一亿容量大十倍，但性能不会下降十倍，因为索引（比如按首字母找名字）。
如下并行数据库分布式查询可解决多表性能低的问题。
写一些程序实现数据管理，数据删除（deletables.cpp）或放历史表等等。三种表数据结构一样，迁移数据可写成一个通用功能模块程序。若是不同表，只知道表名去查询数据字典得到数据结构。如下hsmtable.cpp（和deletetable.cpp像）：数据源表就是要把数据从哪个表迁移出来，列名从数据字典取出，把列拼成一个字符串，获取列名后把数据rowid从源表查出来，生成插入目的表sql，删除源表sql。
#include "_public.h"
#include "_ooci.h"
char logfilename
[301];
char connstr
[101];
char srctname
[51];
char dsttname
[51];
char where
[1024];
char hourstr
[101];
char localhour
[21];
int maxcount
=1;
connection conn
;
clogfile logfile
;
void exit(int sig
);
char strcolumnstr
[2048];
bool
getcolumnstr();
void _help(char *argv
[]);
bool
_hsmtables();int main(int argc
,char *argv
[])
{if (argc
!= 2) { _help(argv
); return -1; }memset(logfilename
,0,sizeof(logfilename
));memset(connstr
,0,sizeof(connstr
));memset(srctname
,0,sizeof(srctname
));memset(dsttname
,0,sizeof(dsttname
));memset(where
,0,sizeof(where
));memset(hourstr
,0,sizeof(hourstr
));getxmlbuffer(argv
[1],"logfilename",logfilename
,300);getxmlbuffer(argv
[1],"connstr",connstr
,100);getxmlbuffer(argv
[1],"srctname",srctname
,50);getxmlbuffer(argv
[1],"dsttname",dsttname
,50);getxmlbuffer(argv
[1],"where",where
,1000);getxmlbuffer(argv
[1],"maxcount",&maxcount
);getxmlbuffer(argv
[1],"hourstr",hourstr
,2000);if (strlen(logfilename
) == 0) { printf("logfilename is null.\n"); return -1; }if (strlen(connstr
) == 0) { printf("connstr is null.\n"); return -1; }if (strlen(srctname
) == 0) { printf("srctname is null.\n"); return -1; }if (strlen(dsttname
) == 0) { printf("dsttname is null.\n"); return -1; }if (strlen(where
) == 0) { printf("where is null.\n"); return -1; }if ( (maxcount
<1) || (maxcount
>1000) ) { printf("maxcount %d is invalid,should in 1-1000.\n",maxcount
); return -1; }if (strlen(hourstr
) == 0) { printf("hourstr is null.\n"); return -1; }closeioandsignal();signal(sigint
,exit
); signal(sigterm
,exit
);if (logfile
.open(logfilename
,"a ") == false
){printf("logfile.open(%s) failed.\n",logfilename
); return -1;}while (true
){ memset(localhour
,0,sizeof(localhour
)); localtime(localhour
,"hh24");if (strstr(hourstr
,localhour
)==0) { sleep(60); continue; }if (conn
.connecttodb(connstr
,"simplified chinese_china.zhs16gbk") != 0){logfile
.write("connect database %s failed.\n",connstr
); sleep(60); continue; }logfile
.write("from table %s to %s.\n",srctname
,dsttname
);if (_hsmtables() == false
) logfile
.write("_hsmtables failed.\n"); conn
.disconnect();sleep(60); }return 0;
}
void exit(int sig
)
{printf("程序退出，sig=%d\n\n",sig
);exit(0);
}
void _help(char *argv
[])
{printf("\nusing:/htidc/public/bin/hsmtables \"/log/shqx/hsmtables_surfdata.logshqx/pwdidc@snorcl11g_198t_surfdatat_surfdata_hiswhere ddatetime50023,01,02,03,04,05,06\"\n\n");printf("这是一个工具程序，用于清理表中的数据。\n");printf("/log/shqx/hsmtables_surfdata.log 本程序运行日志文件名。\n");printf("szidc/pwdidc@szqx_10.153.97.251 目的数据库的连接参数。\n");printf("t_surfdata 数据源表名。\n");printf("t_surfdata_his 目的数据表名。\n");printf("where ddatetime 待迁移数据的条件。\n");printf("500 单次执行数据迁移的记录数，取值在1-1000之间。\n");printf("23,01,02,03,04,05,06 本程序启动的时次，小时，时次之间用半角的逗号分隔开。\n\n");return;
}
bool
_hsmtables()
{ if (getcolumnstr() == false
) return false
; int ccount
=0;char strrowid
[51],strrowidn
[maxcount
][51];sqlstatement
selstmt(&conn
); selstmt
.prepare("select rowid from %s %s",srctname
,where
);selstmt
.bindout(1, strrowid
,50);if (selstmt
.execute() != 0){logfile
.write("%s failed.\n%s\n",selstmt
.m_sql
,selstmt
.m_cda
.message
); return false
;}int ii
=0; char strinsertsql
[10241],strdeletesql
[10241];memset(strinsertsql
,0,sizeof(strinsertsql
));memset(strdeletesql
,0,sizeof(strdeletesql
));sprintf(strinsertsql
,"insert into %s(%s) select %s from %s where rowid in (",dsttname
,strcolumnstr
,strcolumnstr
,srctname
);sprintf(strdeletesql
,"delete from %s where rowid in (",srctname
);char strtemp
[11];for (ii
=0; ii
<maxcount
; ii
){memset(strtemp
,0,sizeof(strtemp
));if (ii
==0) sprintf(strtemp
,":%d",ii
1);if (ii
>0) sprintf(strtemp
,",:%d",ii
1);strcat(strinsertsql
,strtemp
);strcat(strdeletesql
,strtemp
);}strcat(strinsertsql
,")");strcat(strdeletesql
,")");sqlstatement
insstmt(&conn
);insstmt
.prepare(strinsertsql
);sqlstatement
delstmt(&conn
);delstmt
.prepare(strdeletesql
);for (ii
=0; ii
<maxcount
; ii
){insstmt
.bindin(ii
1,strrowidn
[ii
],50);delstmt
.bindin(ii
1,strrowidn
[ii
],50);}while (true
) {memset(strrowid
,0,sizeof(strrowid
));if (selstmt
.next() != 0) break;strcpy(strrowidn
[ccount
],strrowid
);ccount
;if (ccount
== maxcount
){if (insstmt
.execute() != 0){if (insstmt
.m_cda
.rc
!= 1){logfile
.write("_hsmtables insert %s failed.\n%s\n",dsttname
,insstmt
.m_cda
.message
); return false
;}}if (delstmt
.execute() != 0){logfile
.write("_hsmtables delete %s failed.\n%s\n",dsttname
,insstmt
.m_cda
.message
); return false
;}conn
.commit();memset(strrowidn
,0,sizeof(strrowidn
));ccount
=0;}if (fmod(selstmt
.m_cda
.rpc
,10000) < 1){logfile
.write("%s to %s ok(%d).\n",srctname
,dsttname
,selstmt
.m_cda
.rpc
); memset(localhour
,0,sizeof(localhour
)); localtime(localhour
,"hh24");if (strstr(hourstr
,localhour
)==0) return true
;}}for (ii
=0; ii
<ccount
; ii
){insstmt
.prepare("\begin\insert into %s(%s) select %s from %s where rowid=:1;\delete from %s where rowid=:2;\end;",dsttname
,strcolumnstr
,strcolumnstr
,srctname
,srctname
);insstmt
.bindin(1,strrowidn
[ii
],50);insstmt
.bindin(2,strrowidn
[ii
],50);if (insstmt
.execute() != 0){if (insstmt
.m_cda
.rc
!= 1){logfile
.write("_hsmtables insert %s or delete %s failed.\n%s\n",dsttname
,srctname
,insstmt
.m_cda
.message
); return false
;}}}conn
.commit(); logfile
.write("%s to %s finish(%d).\n",srctname
,dsttname
,selstmt
.m_cda
.rpc
);return true
;
}
bool
getcolumnstr()
{memset(strcolumnstr
,0,sizeof(strcolumnstr
)); char column_name
[51];sqlstatement
stmt(&conn
);stmt
.prepare("select lower(column_name) from user_tab_columns where table_name=upper('%s') order by column_id",dsttname
);stmt
.bindout(1,column_name
,50);if (stmt
.execute() != 0){logfile
.write("%s failed.\n%s\n",stmt
.m_sql
,stmt
.m_cda
.message
); return false
;}while(true
){memset(column_name
,0,sizeof(column_name
));if (stmt
.next()!=0) break;if (stmt
.m_cda
.rpc
>1) strcat(strcolumnstr
,",");strcat(strcolumnstr
,column_name
);}if (stmt
.m_cda
.rpc
==0) { logfile
.write("表%s不存在。\n",dsttname
); return false
; }return true
;
}
$sqlplus shqx/pwdidc，如下数据都是一小时前生成，需要将后台生成数据脚本启动。
如下是数据迁移程序如何兼容其他数据库，oracle有rowid，mysql和pg都没有rowid但有keyid。
不管id字段是数字还是字符或字符串，我们都可用字符绑定。
如下不同数据库查数据字典得到全部列。
我们处理后台数据也会有一个web系统用来管理参数，展示数据，查询数据等界面（不能让用户去登录plsqldeveloper查询）。如下是数据的监控，数据少于某数量显示红色。
如下日志等等都在sda(raid1，2个300g)中。
5.1 du，df，fdisk，lsblk：磁盘=硬盘
du：disk usage。-a：不仅统计目录还包括文件大小。-h：显示k/m/g等字节信息。-s：总的占用空间大小（-a是每个都统计）。一般文件夹大小4096b即4k（不包含里面文件大小）。
df：disk free。如果你想知道某个文件夹或文件大小用du，磁盘相关使用情况用df。
5.2 文件：建表入表，传输
以下是收集磁盘空间信息，现在插入一个u盘，fdisk -l可以看到u盘。
每个服务器上运行一个收集磁盘空间的小程序，收集到磁盘空间后生成xml文件存放在本地目录，通过文件传输系统或ftp将文件传给数据处理服务器（因为有的服务器不安装oracle客户端，只可安装文件传输客户端），统一保存到数据库。
#include "_public.h"
void exit(int sig
);
clogfile logfile
;int main(int argc
,char *argv
[])
{if (argc
!= 4){printf("\n");printf("using:./diskinfo hostname logfilename outputpath\n");printf("example:/htidc/public/bin/diskinfo 118.89.50.198 /tmp/htidc/log/diskinfo.log /tmp/htidc/monclient\n\n");printf("此程序调用df命名，把本服务器的磁盘使用率信息写入xml文件。\n");printf("hostname是本服务器的主机名，为了方便识别，也可以用ip。\n");printf("logfilename是本程序的日志文件名。\n");printf("outputpath是输出的xml文件存放的目录。\n");printf("此程序运行在需要监控的服务器上（本程序只适用linux系统），采集后的xml文件由文件传输程序发送给数据处理服务程序入库。\n\n\n");return -1;}closeioandsignal(); signal(sigint
,exit
); signal(sigterm
,exit
);if (logfile
.open(argv
[2],"a ") == false
){printf("logfile.open(%s) failed.\n",argv
[2]); return -1;}file
*fp
=0;if ( (fp
=popen("df -k --block-size=1m","r")) == null ){logfile
.write("popen(df -k --block-size=1m) failed.\n"); return false
;}char strxmlfilename
[301],strlocaltime
[21];memset(strxmlfilename
,0,sizeof(strxmlfilename
));memset(strlocaltime
,0,sizeof(strlocaltime
));localtime(strlocaltime
,"yyyymmddhh24miss");snprintf(strxmlfilename
,300,"%s/diskinfo_%s_%s.xml",argv
[3],strlocaltime
,argv
[1]);cfile xmlfile
;if (xmlfile
.openforrename(strxmlfilename
,"w ") == false
){logfile
.write("xmlfile.openforrename(%s) failed.\n",strxmlfilename
); pclose(fp
); return -1;}xmlfile
.fprintf("\n");ccmdstr cmdstr
;char strbuffer
[1024],strline
[500];while (true
){memset(strbuffer
,0,sizeof(strbuffer
));if (fgets(fp
,strbuffer
,500) == false
) break;if (strstr(strbuffer
,"%") == 0){memset(strline
,0,sizeof(strline
));if (fgets(fp
,strline
,500) == false
) break;strcat(strbuffer
," "); strcat(strbuffer
,strline
);}deletelrchar(strbuffer
,' '); deletelrchar(strbuffer
,'\n');updatestr(strbuffer
," "," ");tolower(strbuffer
);if (strncmp(strbuffer
,"/dev",4) != 0) continue;cmdstr
.splittocmd(strbuffer
," ");if (cmdstr
.cmdcount() != 6) continue;char strusep
[21];memset(strusep
,0,sizeof(strusep
));strcpy(strusep
,cmdstr
.m_vcmdstr
[4].c_str());updatestr(strusep
,"%","");char strlocaltime
[21];memset(strlocaltime
,0,sizeof(strlocaltime
));localtime(strlocaltime
,"yyyymmddhh24miss");xmlfile
.fprintf(\
"%s"\
"%s"\
"%s"\
"%0.02f"\
"%0.02f"\
"%0.02f"\
"%0.02f"\
"%s\n",argv
[1],strlocaltime
,cmdstr
.m_vcmdstr
[0].c_str(),atof(cmdstr
.m_vcmdstr
[1].c_str())/1024.0,atof(cmdstr
.m_vcmdstr
[2].c_str())/1024.0,atof(cmdstr
.m_vcmdstr
[3].c_str())/1024.0,(atof(cmdstr
.m_vcmdstr
[2].c_str())/atof(cmdstr
.m_vcmdstr
[1].c_str()))*100.0,cmdstr
.m_vcmdstr
[5].c_str());}xmlfile
.fprintf("\n");pclose(fp
);xmlfile
.closeandrename();logfile
.write("create %s ok.\n",strxmlfilename
);exit(0);
}void exit(int sig
)
{if (sig
> 0) signal(sig
,sig_ign
);logfile
.write("catching the signal(%d).\n",sig
);logfile
.write("diskinfo exit.\n");exit(0);
}
以下为收集cpu和内存信息，top命令显示如下，zombie表示僵尸，q退出，cpu用到20%算忙了。
ps -ef |grep htidc，cpuinfo.cpp思路是定义三个结构体变量，加载cpu信息到结构体里，睡60s，再继续加载cpu信息到结构体里，再将两结构体成员相减，就可以知道一分钟内cpu情况，采用的是一分钟信息。vi /proc/stat如下。
#include "_public.h"
void exit(int sig
);
clogfile logfile
;
struct st_cpuinfo
{double user
;double sys
;double wait
;double nice
;double idle
;double irq
;double softirq
;double total
;
};
struct st_cpuinfo stcpuinfo1
,stcpuinfo2
,stcpuinfo3
;
bool
loadcpuinfo(struct st_cpuinfo &stcpuinfo
);int main(int argc
,char *argv
[])
{if (argc
!= 4){printf("\n");printf("using:./cpuinfo hostname logfilename outputpath\n");printf("example:/htidc/public/bin/cpuinfo 118.89.50.198 /tmp/htidc/log/cpuinfo.log /tmp/htidc/monclient\n\n");printf("此程序读取/proc/stat文件，把本服务器的cpu使用率信息写入xml文件。\n");printf("hostname是本服务器的主机名，为了方便识别，也可以用ip。\n");printf("logfilename是本程序的日志文件名。\n");printf("outputpath是输出的xml文件存放的目录。\n");printf("此程序运行在需要监控的服务器上（本程序只适用linux系统），采集后的xml文件由文件传输程序发送给数据处理服务程序入库。\n\n\n");return -1;}closeioandsignal(); signal(sigint
,exit
); signal(sigterm
,exit
);if (logfile
.open(argv
[2],"a ") == false
){printf("logfile.open(%s) failed.\n",argv
[2]); return -1;}memset(&stcpuinfo1
,0,sizeof(struct st_cpuinfo));memset(&stcpuinfo2
,0,sizeof(struct st_cpuinfo));memset(&stcpuinfo3
,0,sizeof(struct st_cpuinfo));if (loadcpuinfo(stcpuinfo1
) ==false
) return -1; sleep(60);if (loadcpuinfo(stcpuinfo2
) ==false
) return -1;stcpuinfo3
.user
=stcpuinfo2
.user
-stcpuinfo1
.user
;stcpuinfo3
.sys
=stcpuinfo2
.sys
-stcpuinfo1
.sys
;stcpuinfo3
.wait
=stcpuinfo2
.wait
-stcpuinfo1
.wait
;stcpuinfo3
.nice
=stcpuinfo2
.nice
-stcpuinfo1
.nice
;stcpuinfo3
.idle
=stcpuinfo2
.idle
-stcpuinfo1
.idle
;stcpuinfo3
.irq
=stcpuinfo2
.irq
-stcpuinfo1
.irq
;stcpuinfo3
.softirq
=stcpuinfo2
.softirq
-stcpuinfo1
.softirq
;stcpuinfo3
.total
=stcpuinfo3
.userstcpuinfo3
.sysstcpuinfo3
.waitstcpuinfo3
.nicestcpuinfo3
.idlestcpuinfo3
.irqstcpuinfo3
.softirq
;char strlocaltime
[21];memset(strlocaltime
,0,sizeof(strlocaltime
));localtime(strlocaltime
,"yyyymmddhh24miss");char strxmlfilename
[301];memset(strxmlfilename
,0,sizeof(strxmlfilename
));snprintf(strxmlfilename
,300,"%s/cpuinfo_%s_%s.xml",argv
[3],strlocaltime
,argv
[1]);cfile xmlfile
;if (xmlfile
.openforrename(strxmlfilename
,"w ") == false
){logfile
.write("xmlfile.openforrename(%s) failed.\n",strxmlfilename
); return -1;}xmlfile
.fprintf("\n");xmlfile
.fprintf("%s%s%0.02f%0.02f%0.02f%0.02f%0.02f%0.02f\n",argv
[1],strlocaltime
,stcpuinfo3
.user
/stcpuinfo3
.total
*100.0,stcpuinfo3
.sys
/stcpuinfo3
.total
*100.0,stcpuinfo3
.wait
/stcpuinfo3
.total
*100.0,stcpuinfo3
.nice
/stcpuinfo3
.total
*100.0,stcpuinfo3
.idle
/stcpuinfo3
.total
*100.0,100.0-stcpuinfo3
.nice
/stcpuinfo3
.total
*100.0);xmlfile
.fprintf("\n");xmlfile
.closeandrename();logfile
.write("create %s ok.\n",strxmlfilename
);exit(0);
}
void exit(int sig
)
{if (sig
> 0) signal(sig
,sig_ign
);logfile
.write("catching the signal(%d).\n",sig
);logfile
.write("cpuinfo exit.\n");exit(0);
}bool
loadcpuinfo(struct st_cpuinfo &stcpuinfo
)
{cfile cpufile
;if (cpufile
.open("/proc/stat","r") == false
){logfile
.write("cpufile.openforread(/proc/stat) failed.\n"); return false
;}ccmdstr cmdstr
;char strbuffer
[1024];while (true
){memset(strbuffer
,0,sizeof(strbuffer
));if (cpufile
.ffgets(strbuffer
,500) == false
) break;deletelrchar(strbuffer
,' ');updatestr(strbuffer
," "," ");tolower(strbuffer
);cmdstr
.splittocmd(strbuffer
," ");if (strcmp(cmdstr
.m_vcmdstr
[0].c_str(),"cpu")==0) {stcpuinfo
.user
=atof(cmdstr
.m_vcmdstr
[1].c_str());stcpuinfo
.sys
=atof(cmdstr
.m_vcmdstr
[2].c_str());stcpuinfo
.wait
=atof(cmdstr
.m_vcmdstr
[3].c_str());stcpuinfo
.nice
=atof(cmdstr
.m_vcmdstr
[4].c_str());stcpuinfo
.idle
=atof(cmdstr
.m_vcmdstr
[5].c_str());stcpuinfo
.irq
=atof(cmdstr
.m_vcmdstr
[6].c_str());stcpuinfo
.softirq
=atof(cmdstr
.m_vcmdstr
[7].c_str());return true
;}}logfile
.write("read /proc/stat failed.\n"); return false
;
}
vi /tmp/htdic/monclient/cpu*，如下是收集到的信息。
收集内存信息#free -m，和top命令查看的内存是一样的，也在系统文件vi /proc/meminfo。
以下是收集oracle表空间信息，表空间就像磁盘空间一样，表空间的信息收集要去读取oracle的数据字典，如下sql是查询oracle表空间使用率，取出后写入xml文件里，vi tbspaceinfo.cpp，要dba权限。
select * from (
select a
.tablespace_name
,
to_char
(a
.bytes
/1024/1024,'99,999.999') total_bytes
,
to_char
(b
.bytes
/1024/1024,'99,999.999') free_bytes
,
to_char
(a
.bytes
/1024/1024 - b
.bytes
/1024/1024,'99,999.999') use_bytes
,
to_char
((1 - b
.bytes
/a
.bytes
)*100,'99.99') || '%' use
from (select tablespace_name
,
sum(bytes
) bytes
from dba_data_files
group by tablespace_name
) a
,
(select tablespace_name
,
sum(bytes
) bytes
from dba_free_space
group by tablespace_name
) b
where a
.tablespace_name
= b
.tablespace_name
union all
select c
.tablespace_name
,
to_char
(c
.bytes
/1024/1024,'99,999.999') total_bytes
,
to_char
( (c
.bytes
-d
.bytes_used
)/1024/1024,'99,999.999') free_bytes
,
to_char
(d
.bytes_used
/1024/1024,'99,999.999') use_bytes
,
to_char
(d
.bytes_used
*100/c
.bytes
,'99.99') || '%' use
from
(select tablespace_name
,sum(bytes
) bytes
from dba_temp_files
group by tablespace_name
) c
,
(select tablespace_name
,sum(bytes_cached
) bytes_used
from v$temp_extent_pool
group by tablespace_name
) d
where c
.tablespace_name
= d
.tablespace_name
)
#include "_public.h"
#include "_ooci.h"
void exit(int sig
);
struct st_tbspaceinfo
{long taskid
;char nodip
[31];char tablespace
[101];double total
;double used
;double available
;double usep
;double alarmvalue
;int alarmsts
;char crttime
[21];int rsts
;
};
struct st_tbspaceinfo sttbspaceinfo
;
clogfile logfile
;
connection conn
;
int main(int argc
,char *argv
[])
{if (argc
!= 5){printf("\n");printf("using:./tbspaceinfo hostname logfilename outputpath username/password@tnsnames\n");printf("example:/htidc/public/bin/tbspaceinfo 10.153.98.13 /tmp/htidc/log/tbspaceinfo_10.153.98.13.log /tmp/htidc/monclient shqx/pwdidc@szqx_10.153.98.13\n\n");printf("此程序连接远程数据库，把远程数据库表空间使用率信息写入xml文件。\n");printf("hostname是本服务器的主机名，为了方便识别，也可以用ip。\n");printf("logfilename是本程序的日志文件名。\n");printf("outputpath是输出的xml文件存放的目录。\n");printf("username/password@tnsnames为待监控的远程数据库的用户名/密码@连接名。\n");printf("此程序运行在数据中心应用程序的服务器上。\n\n\n");return -1;}closeioandsignal(); signal(sigint
,exit
); signal(sigterm
,exit
);if (logfile
.open(argv
[2],"a ") == false
){printf("logfile.open(%s) failed.\n",argv
[2]); return -1;}if (conn
.connecttodb(argv
[4],"simplified chinese_china.zhs16gbk") != 0){logfile
.write("conn.connecttodb(%s) failed.\n",argv
[4]); return -1;}sqlstatement stmt
;stmt
.connect(&conn
);stmt
.prepare("\select f.tablespace_name,a.total,u.used,f.free,(u.used/a.total)*100 from\(select tablespace_name,sum(bytes/(1024*1024*1024)) total from dba_data_files\group by tablespace_name) a,\(select tablespace_name,round(sum(bytes/(1024*1024*1024))) used from dba_extents\group by tablespace_name) u,\(select tablespace_name,round(sum(bytes/(1024*1024*1024))) free from dba_free_space\group by tablespace_name) f\where a.tablespace_name = f.tablespace_name\and a.tablespace_name = u.tablespace_name\and f.tablespace_name in (select tablespace_name from dba_tablespaces where contents='permanent')");stmt
.bindout(1,sttbspaceinfo
.tablespace
,100);stmt
.bindout(2,&sttbspaceinfo
.total
);stmt
.bindout(3,&sttbspaceinfo
.used
);stmt
.bindout(4,&sttbspaceinfo
.available
);stmt
.bindout(5,&sttbspaceinfo
.usep
);if (stmt
.execute() != 0){logfile
.write("select dba_data_files,dba_extents,dba_free_space failed.\n%s\n",stmt
.m_cda
.message
); return -1;}char strlocaltime
[21];memset(strlocaltime
,0,sizeof(strlocaltime
));localtime(strlocaltime
,"yyyymmddhh24miss");char strxmlfilename
[301];memset(strxmlfilename
,0,sizeof(strxmlfilename
));snprintf(strxmlfilename
,300,"%s/tbspaceinfo_%s_%s.xml",argv
[3],strlocaltime
,argv
[1]);cfile xmlfile
;if (xmlfile
.openforrename(strxmlfilename
,"w ") == false
){logfile
.write("xmlfile.openforrename(%s) failed.\n",strxmlfilename
); return -1;}xmlfile
.fprintf("\n");while (true
){memset(&sttbspaceinfo
,0,sizeof(sttbspaceinfo
));if (stmt
.next() != 0) break;xmlfile
.fprintf(\
"%s"\
"%s"\
"%s"\
"%0.02f"\
"%0.02f"\
"%0.02f"\
"%0.02f\n",argv
[1],strlocaltime
,sttbspaceinfo
.tablespace
,sttbspaceinfo
.total
,sttbspaceinfo
.used
,sttbspaceinfo
.available
,sttbspaceinfo
.usep
);}xmlfile
.fprintf("\n");xmlfile
.closeandrename();logfile
.write("生成文件%s.\n",strxmlfilename
);exit(0);
}
void exit(int sig
)
{if (sig
> 0) signal(sig
,sig_ign
);logfile
.write("catching the signal(%d).\n",sig
);logfile
.write("tbspaceinfo exit.\n");exit(0);
}
以下是收集oracle会话信息，我们用客户端通oracle的监听连上oracle数据库，oracle数据库会启动一个进程向会话提供服务，是多进程的服务端，每增加一连接(进程)，需要消耗系统资源(内存，socket连接)。如下一共有6个客户端连上，sql>exit退出一个就剩下5个。ps -ef |grep oracle可查看oracle数据库系统进程。
如下两个sql是查询数据字典查到进程和上面一样，第一个sql是查全部包括系统进程。如下表有一个osuser表示连上来的客户端操作系统名字，还可以通过sql_id查到连上来的客户端sql语句操作。还可以指定oracle用户的会话数。
#include "qxmon.h"
void exit(int sig
);
struct st_dbsessioninfo stdbsessioninfo
;
clogfile logfile
;
connection conndst
;
cprogramactive programactive
;int main(int argc
,char *argv
[])
{if (argc
!= 5){printf("\n");printf("using:./dbsessioninfo hostname logfilename outputpath username/password@tnsnames\n");printf("example:/htidc/htidc/bin/procctl 300 /htidc/qxmon/bin/dbsessioninfo 10.153.98.13 /log/qxmon/dbsessioninfo_10.153.98.13.log /qxdata/qxmon/qxmonclient szidc/pwdidc@szqx_10.153.98.13\n\n");printf("此程序连接远程数据库，把远程数据库会话信息写入xml文件。\n");printf("hostname是本服务器的主机名，为了方便识别，也可以用ip。\n");printf("logfilename是本程序的日志文件名。\n");printf("outputpath是输出的xml文件存放的目录。\n");printf("username/password@tnsnames为待监控的远程数据库的用户名/密码@连接名。\n");printf("此程序运行在监控平台的服务器上。\n\n\n");return -1;}closeioandsignal(); signal(sigint
,exit
); signal(sigterm
,exit
);if (logfile
.open(argv
[2],"a ") == false
){printf("logfile.open(%s) failed.\n",argv
[2]); return -1;}programactive
.setprograminfo(&logfile
,"alarmserver",300);if (conndst
.connecttodb(argv
[4])!=0){logfile
.write("connmon.connecttodb(%s) failed.\n",argv
[4]); exit(-1);}sqlstatement stmt
;stmt
.connect(&conndst
);stmt
.prepare("select count(*) from v$session where type!='background' and status!='killed'"); stmt
.bindout(1,&stdbsessioninfo
.total
);if (stmt
.execute() != 0){logfile
.write("select v$session failed.\n%s\n",stmt
.cda
.message
); exit(-1);}char strlocaltime
[21];memset(strlocaltime
,0,sizeof(strlocaltime
));localtime(strlocaltime
,"yyyymmddhh24miss");char strxmlfilename
[301];memset(strxmlfilename
,0,sizeof(strxmlfilename
));snprintf(strxmlfilename
,300,"%s/dbsessioninfo_%s_%s.xml",argv
[3],strlocaltime
,argv
[1]);cfile xmlfile
;if (xmlfile
.openforrename(strxmlfilename
,"w ") == false
){logfile
.write("xmlfile.openforrename(%s) failed.\n",strxmlfilename
); exit(-1);}xmlfile
.fprintf("\n");while (true
){memset(&stdbsessioninfo
,0,sizeof(stdbsessioninfo
));if (stmt
.next() != 0) break;xmlfile
.fprintf(\
"%s"\
"%s"\
"%d\n",argv
[1],strlocaltime
,stdbsessioninfo
.total
);}xmlfile
.fprintf("\n");xmlfile
.closeandrename();logfile
.write("生成文件%s.\n",strxmlfilename
);exit(0);
}void exit(int sig
)
{if (sig
> 0) signal(sig
,sig_ign
);logfile
.write("catching the signal(%d).\n",sig
);logfile
.write("dbsessioninfo exit.\n");exit(0);
}
oracle里有一个系统参数processes（最大允许的会话总数）。_ooci.h中connection类连接上一个后进程及processes 1，sqlstatement类实例化并指定数据库连接也会消耗一种资源即open_cursors参数。oracle有缓存机制存sql，下次用到则不进行语法分析，所以oracle需大内存，存的越多，缓存命中率越高。
总结
以上是凯发k8官方网为你收集整理的【c/c 13】天气app：数据挖掘/http协议/非结构化数据存储（filetoblob.cpp），数据管理/监控告警（hsmtable.cpp，tbspaceinfo.cpp）的全部内容，希望文章能够帮你解决所遇到的问题。
如果觉得凯发k8官方网网站内容还不错，欢迎将凯发k8官方网推荐给好友。
