1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
|
-- 有关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是';
|
