-- 有关CPU监测和NET监测，METADATA表中记录修改 update metadata t set t.show_num=t.show_num+1 where t.table_name='DETECT_INFO_CPU'; update metadata t set t.show_num=1,t.sort_sign=0 where t.table_name='DETECT_INFO_CPU' and t.filed_name='CPU_NAME'; update metadata t set t.show_num=t.show_num+1 where t.table_name='DETECT_INFO_NETDATA' and t.show_num<14; update metadata t set t.show_num=1 where t.table_name='DETECT_INFO_NETDATA' and t.filed_name='NAME'; update metadata t set t.sort_sign=0 where t.table_name='DETECT_INFO_DISK' and t.filed_name='DISK_RANGE'; commit; -- 所有详细监测信息表增加字段 DETECTION_SET_INFO_ID（已整理到存储过程中） --alter table delect_info_nmsc add DETECTION_SET_INFO_ID number; --alter table DETECT_INFO_CPU add DETECTION_SET_INFO_ID number; --alter table DETECT_INFO_DISK add DETECTION_SET_INFO_ID number; --alter table DETECT_INFO_MEMORY add DETECTION_SET_INFO_ID number; --alter table DETECT_INFO_NETDATA add DETECTION_SET_INFO_ID number; --alter table DETECT_INFO_PING add DETECTION_SET_INFO_ID number; --alter table DETECT_INFO_PROCESS add DETECTION_SET_INFO_ID number; --alter table DETECT_INFO_SNMP_TRAP add DETECTION_SET_INFO_ID number; --alter table DETECT_INFO_SYSTEM_DATE add DETECTION_SET_INFO_ID number; --alter table DI_DPSYSTEMFANENTRY add DETECTION_SET_INFO_ID number; --alter table DI_DPSYSTEMPOWERENTRY add DETECTION_SET_INFO_ID number; --alter table DI_DPSYSTEMSTATUS add DETECTION_SET_INFO_ID number; --alter table DI_IFXTABLE add DETECTION_SET_INFO_ID number; --alter table DI_MENCPU add DETECTION_SET_INFO_ID number; --alter table DI_SWITCHPORT add DETECTION_SET_INFO_ID number; --alter table DI_SYSTEM add DETECTION_SET_INFO_ID number; --alter table DI_SYSTEMINFO add DETECTION_SET_INFO_ID number; alter table DI_SYSTEMINFO_DISK add DETECTION_SET_INFO_ID number; alter table DI_SYSTEMINFO_NET add DETECTION_SET_INFO_ID number; --commit; --修改存储过程 pro_createTable create or replace procedure pro_createTable(tabName IN VARCHAR2,filedAndType IN VARCHAR2,indexfileds in varchar2) Authid Current_User----使存储过程可以使用role权限 is v_sql1 VARCHAR2(5000); v_sql2 VARCHAR2(500); C_DATE VARCHAR2(64); tempstr VARCHAR2(64); v_date VARCHAR2(64); begin --获取系统当前年 select to_char(sysdate, 'yyyy') into C_DATE from dual; --获取系统当前时间是本年第周 select to_char(sysdate, 'iw') into tempstr from dual; --获取当前日期字符串 --select to_char(sysdate, 'yyyy-MM-dd HH24:MI:SS') into v_date from dual; --获取当前日期的下周第一天 select to_char(trunc(sysdate+7,'d'),'yyyy-MM-dd HH24:MI:SS') into v_date from dual; --v_sql1 := 'create table dxytest(detection_info_id number not null primary key,DETECTIONED_STATE,SEQ_ID,data_check_time DATE,data_arrive_time DATE,data_check_time_digital number,data_arrive_time_digital number,foreign KEY(detection_info_id) REFERENCES detection_info(id))partition by range(DATA_CHECK_TIME)(partition di_2012w35 values less than(to_date(''2012-08-29 09:35:34'',''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) tablespace di_2012w35 storage(initial 10M next 10M maxextents unlimited ))'; v_sql1 :='create table '||tabName ||'(detection_info_id number not null primary key,'||filedAndType||',DETECTIONED_STATE varchar(2),SEQ_ID number,DETECTION_SET_INFO_ID number,data_check_time DATE,data_arrive_time DATE,data_check_time_digital number,data_arrive_time_digital number,foreign KEY(detection_info_id) REFERENCES detection_info(id))'|| 'partition by range(DATA_CHECK_TIME)'||'(partition DI_W'||C_DATE||''||tempstr||' values less than(to_date('''||v_date||''',''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN'')) tablespace DI_W'||C_DATE||''||tempstr||' storage(initial 10M next 10M maxextents unlimited ))'; dbms_output.put_line(v_sql1); EXECUTE IMMEDIATE v_sql1; --创建seq 使用detection_info_id为主键故不再创建Seq pro_createSeq(tabName); --创建触发器 使用detection_info_id为主键故不再创建主键触发器 pro_createTrigger(tabName); --创建索引 --indexfileds := indexfileds ||'SEQ_ID:'; --indexfileds := concat(indexfileds,'SEQ_ID'); pro_createindex(tabName,indexfileds); EXCEPTION WHEN OTHERS THEN v_sql2 :='insert into PROCEDURE_LOG(ID,PRO_NAME,PRO_SQL,ER_INFO) values(null,''pro_createTable'','||v_sql1||','||sqlerrm||')'; EXECUTE IMMEDIATE v_sql2; dbms_output.put_line(sqlerrm); ROLLBACK; end pro_createTable; --更新存储过程pro_createTrigger create or replace procedure pro_createTrigger(tabName IN VARCHAR2) is v_sql1 VARCHAR2(5000); begin v_sql1 :='Create Or Replace Trigger B_I_'||tabName ||' before insert on '||tabName||' for each row declare begin if ( :new.id is null ) --insert then select seq_'||tabName ||'.nextVal into :new.id from dual; end if; end B_I_'||tabName||';'; dbms_output.put_line(v_sql1); EXECUTE IMMEDIATE v_sql1; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(sqlerrm); ROLLBACK; end pro_createTrigger; --新增存储过程pro_updatedetectiontable （仅用一次） create or replace procedure pro_updatedetectiontable Authid Current_User----使存储过程可以使用role权限 is tablename VARCHAR2(100); hasPk NUMBER :=0; hasId NUMBER :=0; dsiId NUMBER :=0; --CURSOR CURSOR tableName_cursor is select TABLE_NAME from check_type_info cti WHERE 1=1 AND cti.crete_state=0 ; tableName_row tableName_cursor%ROWTYPE; -- 游标名的声明 与 指定游标集合结构 定义。 BEGIN --EXECUTE IMMEDIATE 'grant create sequence to '|| username; --EXECUTE IMMEDIATE 'grant create trigger to '|| username; OPEN tableName_cursor; -- 打开游标 LOOP -- 循环开始 BEGIN FETCH tableName_cursor INTO tableName_row; -- 将游标集合存到游标中 EXIT WHEN tableName_cursor%NOTFOUND; -- 循环条件 tablename := tableName_row.TABLE_NAME; EXECUTE IMMEDIATE 'truncate table '|| tablename; select nvl(count(*),0) INTO hasPk from user_cons_columns utc,user_constraints uc where utc.constraint_name = uc.constraint_name and utc.TABLE_NAME=tablename and uc.constraint_type='P'; select nvl(count(*),0) INTO hasId from user_tab_columns utc where utc.TABLE_NAME = tablename AND utc.column_name='ID'; select nvl(count(*),0) INTO dsiId from user_tab_columns utc where utc.TABLE_NAME = tablename AND utc.column_name='DETECTION_SET_INFO_ID'; IF(dsiId =0) THEN EXECUTE IMMEDIATE 'alter table '|| tablename || ' add (DETECTION_SET_INFO_ID number)'; END IF; IF(hasPk =1) THEN EXECUTE IMMEDIATE 'alter table '|| tablename || ' drop Primary key'; END IF; IF(hasId =0) THEN EXECUTE IMMEDIATE 'alter table '|| tablename || ' add (ID number Primary key)'; ELSE EXECUTE IMMEDIATE 'alter table '|| tablename || ' add Primary key(ID)'; END IF; pro_createseq(tablename); pro_createtrigger(tablename); EXCEPTION WHEN OTHERS THEN dbms_output.put_line(tableName_row.TABLE_NAME||' 表修改失败'); END; END LOOP; --循环结束 CLOSE tableName_cursor; -- 关闭游标 EXCEPTION WHEN OTHERS THEN dbms_output.put_line(tableName_row.TABLE_NAME||'表修改失败'); ROLLBACK; end pro_updatedetectiontable; -- 为当前用户新增 创建索引权限 grant create sequence to #username#; -- 为当前用户新增 创建触发器权限 grant create trigger to #username#; -- 更新所有的监测数据详细表表字段，会清除各详细信息表数据 call pro_updatedetectiontable(); --更新触发器Before_nms_error_info Create Or Replace Trigger Before_nms_error_info before insert on nms_error_info for each row declare -- local variables here begin if ( :new.id is null ) --insert then select seq_nms_error_info.nextVal into :new.id from dual ; end if; IF(:NEW.state_update_time IS NULL) THEN :NEW.state_update_time := :NEW.error_time; END IF; IF(:NEW.error_state = 2) THEN UPDATE nms_error_info nei SET nei.error_state=0,nei.state_update_time=:NEW.state_update_time WHERE nei.error_state=1 AND nei.state_update_time<:NEW.state_update_time AND nei.ERROR_CODE=:NEW.ERROR_CODE AND nei.error_des = :NEW.error_des AND nei.errort_getip = :NEW.errort_getip AND nei.errort_ip = :NEW.errort_ip; END IF; end Before_insert_nms_error_info; -- 新增METADATA 数据 insert into METADATA (ID, CHECK_TYPE_ID, TABLE_NAME, TABLE_COMMENTS, FILED_NAME, FILED_COMMENTS, FILED_TYPE, CREATE_DATE, CREATE_PERSON, STATE, UPDATE_DATE, UPDATE_PERSON, CHART_STATE, SHOW_NUM, ISNULL, OID, TYPE_ID, SORT_SIGN) values (seq_metadata.nextval/*306*/, 10, 'DI_SWITCHPORT', null, 'ifHighSpeed', '端口速率单位（Mbps）', 'NUMBER', null, 1, '0', null, null, '1', 37, null, '1.3.6.1.2.1.31.1.1.1.15.1.1000000', null, 1); insert into METADATA (ID, CHECK_TYPE_ID, TABLE_NAME, TABLE_COMMENTS, FILED_NAME, FILED_COMMENTS, FILED_TYPE, CREATE_DATE, CREATE_PERSON, STATE, UPDATE_DATE, UPDATE_PERSON, CHART_STATE, SHOW_NUM, ISNULL, OID, TYPE_ID, SORT_SIGN) values (seq_metadata.nextval/*300*/, 10, 'DI_SWITCHPORT', null, 'ifInOctets_t1', '接收的字节', 'NUMBER', null, 1, '0', null, null, '0', 31, null, '1.3.6.1.2.1.2.2.1.10', null, 1); insert into METADATA (ID, CHECK_TYPE_ID, TABLE_NAME, TABLE_COMMENTS, FILED_NAME, FILED_COMMENTS, FILED_TYPE, CREATE_DATE, CREATE_PERSON, STATE, UPDATE_DATE, UPDATE_PERSON, CHART_STATE, SHOW_NUM, ISNULL, OID, TYPE_ID, SORT_SIGN) values (seq_metadata.nextval/*301*/, 10, 'DI_SWITCHPORT', null, 'ifInUcastPkts_t1', '被承认的单向传输数据包', 'NUMBER', null, 1, '0', null, null, '1', 32, null, '1.3.6.1.2.1.2.2.1.11', null, 1); insert into METADATA (ID, CHECK_TYPE_ID, TABLE_NAME, TABLE_COMMENTS, FILED_NAME, FILED_COMMENTS, FILED_TYPE, CREATE_DATE, CREATE_PERSON, STATE, UPDATE_DATE, UPDATE_PERSON, CHART_STATE, SHOW_NUM, ISNULL, OID, TYPE_ID, SORT_SIGN) values (seq_metadata.nextval/*302*/, 10, 'DI_SWITCHPORT', null, 'ifInNUcastPkts_t1', '被承认的非单向传输数据包', 'NUMBER', null, 1, '0', null, null, '1', 33, null, '1.3.6.1.2.1.2.2.1.12', null, 1); insert into METADATA (ID, CHECK_TYPE_ID, TABLE_NAME, TABLE_COMMENTS, FILED_NAME, FILED_COMMENTS, FILED_TYPE, CREATE_DATE, CREATE_PERSON, STATE, UPDATE_DATE, UPDATE_PERSON, CHART_STATE, SHOW_NUM, ISNULL, OID, TYPE_ID, SORT_SIGN) values (seq_metadata.nextval/*303*/, 10, 'DI_SWITCHPORT', null, 'ifOutOctets_t1', '输出字节数', 'NUMBER', null, 1, '0', null, null, '0', 34, null, '1.3.6.1.2.1.2.2.1.16', null, 1); insert into METADATA (ID, CHECK_TYPE_ID, TABLE_NAME, TABLE_COMMENTS, FILED_NAME, FILED_COMMENTS, FILED_TYPE, CREATE_DATE, CREATE_PERSON, STATE, UPDATE_DATE, UPDATE_PERSON, CHART_STATE, SHOW_NUM, ISNULL, OID, TYPE_ID, SORT_SIGN) values (seq_metadata.nextval/*304*/, 10, 'DI_SWITCHPORT', null, 'ifOutUcastPkts_t1', '单向传输数据包', 'NUMBER', null, 1, '0', null, null, '1', 35, null, '1.3.6.1.2.1.2.2.1.17', null, 1); insert into METADATA (ID, CHECK_TYPE_ID, TABLE_NAME, TABLE_COMMENTS, FILED_NAME, FILED_COMMENTS, FILED_TYPE, CREATE_DATE, CREATE_PERSON, STATE, UPDATE_DATE, UPDATE_PERSON, CHART_STATE, SHOW_NUM, ISNULL, OID, TYPE_ID, SORT_SIGN) values (seq_metadata.nextval/*305*/, 10, 'DI_SWITCHPORT', null, 'ifOutNUcastPkts_t1', '非单向传输数据包', 'NUMBER', null, 1, '0', null, null, '1', 36, null, '1.3.6.1.2.1.2.2.1.18', null, 1); insert into METADATA (ID, CHECK_TYPE_ID, TABLE_NAME, TABLE_COMMENTS, FILED_NAME, FILED_COMMENTS, FILED_TYPE, CREATE_DATE, CREATE_PERSON, STATE, UPDATE_DATE, UPDATE_PERSON, CHART_STATE, SHOW_NUM, ISNULL, OID, TYPE_ID, SORT_SIGN) values (seq_metadata.nextval/*307*/, 10, 'DI_SWITCHPORT', null, 'Data_64_FLag', '64位数据标识', 'NUMBER', null, 1, '0', null, null, '1', 38, null, '.0', null, 1); commit; -- di_switch表新增字段 alter table di_switchport add (IFINOCTETS_T1 NUMBER); comment on column di_switchport.IFINOCTETS_T1 is '接口收到的总字节数'; alter table di_switchport add (IFINUCASTPKTS_T1 NUMBER); comment on column di_switchport.IFINUCASTPKTS_T1 is '单点发送到一种高层协议上的包的总数目'; alter table di_switchport add (IFINNUCASTPKTS_T1 NUMBER); comment on column di_switchport.IFINNUCASTPKTS_T1 is '发往高层协议的非单播包数（广播和多播）'; alter table di_switchport add (IFOUTOCTETS_T1 NUMBER); comment on column di_switchport.IFOUTOCTETS_T1 is '接口发送的总字节数'; alter table di_switchport add (IFOUTUCASTPKTS_T1 NUMBER); comment on column di_switchport.IFOUTUCASTPKTS_T1 is '高层协议请求传输的单播包数'; alter table di_switchport add (IFOUTNUCASTPKTS_T1 NUMBER); comment on column di_switchport.IFOUTNUCASTPKTS_T1 is '高层协议请求的非单播（广播和多播）包数'; alter table di_switchport add (IFHIGHSPEED NUMBER); comment on column di_switchport.IFHIGHSPEED is '端口带宽（Mbps）'; alter table di_switchport add (DATA_64_FLAG NUMBER); comment on column di_switchport.DATA_64_FLAG is '64位数据标识 0否 1是';