Cisco Transport Manager データベース スキーマ Release7.1追補
パッケージ、プロシージャ、関数、 およびトリガー
パッケージ、プロシージャ、関数、およびトリガー
発行日;2012/02/07 | ドキュメントご利用ガイド | ダウンロード ; この章pdf , ドキュメント全体pdf (PDF - 630KB) | フィードバック

目次

パッケージ、プロシージャ、関数、およびトリガー

パッケージ、プロシージャ、関数、およびトリガー

CTM 7.0 の機能(『 Cisco Transport Manager Release 7.0 Database Schema 』に記載)の変更点は、次の項で説明します。

「変更点」

3.1 変更点

この項に記載された変更は、すべて『 Cisco Transport Manager Release 7.0 Database Schema 』の対応する項に適用されます。


) この文書の項の表題は『Cisco Transport Manager Release 7.0 Database Schema』の項の名前と対応していますが、項の番号は異なる場合があります。この文書の各項にあるリンク テキストをクリックすると、『Cisco Transport Manager Release 7.0 Database Schema』の対応する項に直接移動できます。


この項は、次の項目で構成されています。

「CTMAlarm」

「BulkNEAddition」

「add_log_partition」

「小規模構成用の prune_package」

「中規模、大規模、およびハイエンド構成用の prune_package」

3.1.1 CTMAlarm

CTM Packages 」の項にある項目「 CTMAlarm 」は、次の情報で置き換えます。

Create or Replace package CTMAlarm is
 
--TYPE link_id_tabtype is Table of number INDEX by BINARY_INTEGER;
 
INVALID_ALARM_STATUS EXCEPTION;
PRAGMA EXCEPTION_INIT (INVALID_ALARM_STATUS, -20002);
 
ALARM_NOT_FOUND EXCEPTION;
PRAGMA EXCEPTION_INIT (ALARM_NOT_FOUND, -20000);
 
Procedure alarm_handle_impl
(p_nedbaccessid IN number,
p_Alarmseqnum IN number default 0,
p_ModuleOrIfIndex IN number,
p_ActiveAlarmTimeStamp IN date,
p_ActiveAlarmType IN number,
p_ActiveAlarmSeverity IN number,
p_ActiveAlarmServEff IN number,
p_ActiveAlarmAdditionalInfo IN varchar2,
p_AckIndex IN number,
p_ActiveAlarmFlag IN number,
p_ActiveAlarmComment IN varchar2 default null,
p_NEAlarmTimeStamp IN date,
p_ModuleType IN number,
p_Physicalloc IN number,
p_alarmstatus IN number,
p_linkid IN link_id_tabtype,
p_actiontype IN number,
p_externalcondition IN varchar2,
p_ModelType IN number,
p_ObjectType IN number,
p_StrObjInstance IN varchar2
);
 
Procedure clear_alarms_impl
(p_nedbaccessid IN number,
p_ModuleOrIfIndex IN number,
p_ActiveAlarmType IN number,
p_ActiveAlarmSeverity IN number,
p_clearalarmtimestamp IN date,
p_NEAlarmClearTimeStamp IN date,
p_Physicalloc IN number,
p_alarmstatus IN number,
p_linkid IN link_id_tabtype,
p_actiontype IN number,
p_ModelType IN number,
p_ObjectType IN number,
p_StrObjInstance IN varchar2
);
 
Procedure add_clear_alarms_impl
(p_nedbaccessid IN number,
p_Alarmseqnum IN number default 0,
p_ModuleOrIfIndex IN number,
p_ActiveAlarmTimeStamp IN date,
p_ActiveAlarmType IN number,
p_ActiveAlarmSeverity IN number,
p_ActiveAlarmServEff IN number,
p_ActiveAlarmAdditionalInfo IN varchar2,
p_AckIndex IN number,
p_ActiveAlarmFlag IN number,
p_ClearAlarmTimeStamp IN date,
p_NEAlarmTimeStamp IN date,
p_NEAlarmClearTimeStamp IN date,
p_ModuleType IN number,
p_Physicalloc IN number,
p_alarmstatus IN number,
p_linkid IN link_id_tabtype,
p_actiontype IN number,
p_externalcondition IN varchar2,
p_ModelType IN number,
p_ObjectType IN number,
p_StrObjInstance IN varchar2
);
 
Procedure alarm_handle
(p_nedbaccessid IN number,
p_Alarmseqnum IN number default 0,
p_ModuleOrIfIndex IN number,
p_ActiveAlarmTimeStamp IN date,
p_ActiveAlarmType IN number,
p_ActiveAlarmSeverity IN number,
p_ActiveAlarmServEff IN number,
p_ActiveAlarmAdditionalInfo IN varchar2,
p_AckIndex IN number,
p_ActiveAlarmFlag IN number,
p_ActiveAlarmComment IN varchar2 default null,
p_NEAlarmTimeStamp IN date,
p_ModuleType IN number,
p_Physicalloc IN number,
p_alarmstatus IN number,
p_linkid IN link_id_tabtype,
p_actiontype IN number,
p_externalcondition IN varchar2,
p_ModelType IN number,
p_ObjectType IN number,
p_StrObjInstance IN varchar2
);
 
Procedure clear_alarms
(p_nedbaccessid IN number,
p_ModuleOrIfIndex IN number,
p_ActiveAlarmType IN number,
p_ActiveAlarmSeverity IN number,
p_clearalarmtimestamp IN date,
p_NEAlarmClearTimeStamp IN date,
p_Physicalloc IN number,
p_alarmstatus IN number,
p_linkid IN link_id_tabtype,
p_actiontype IN number,
p_ModelType IN number,
p_ObjectType IN number,
p_StrObjInstance IN varchar2
);
 
Procedure add_clear_alarms
(p_nedbaccessid IN number,
p_Alarmseqnum IN number default 0,
p_ModuleOrIfIndex IN number,
p_ActiveAlarmTimeStamp IN date,
p_ActiveAlarmType IN number,
p_ActiveAlarmSeverity IN number,
p_ActiveAlarmServEff IN number,
p_ActiveAlarmAdditionalInfo IN varchar2,
p_AckIndex IN number,
p_ActiveAlarmFlag IN number,
p_ClearAlarmTimeStamp IN date,
p_NEAlarmTimeStamp IN date,
p_NEAlarmClearTimeStamp IN date,
p_ModuleType IN number,
p_Physicalloc IN number,
p_alarmstatus IN number,
p_linkid IN link_id_tabtype,
p_actiontype IN number,
p_externalcondition IN varchar2,
p_ModelType IN number,
p_ObjectType IN number,
p_StrObjInstance IN varchar2
);
 
Procedure bulk_alarm_handle
(
p_AlarmInfoList IN AlarmInfoListType
);
 
Procedure bulk_clear_alarms
(
p_AlarmInfoList IN AlarmInfoListType
);
 
Procedure bulk_add_clear_alarms
(
p_AlarmInfoList IN AlarmInfoListType
);
 
END CTMAlarm;
/
 
create or replace package body CTMAlarm is
 
Procedure alarm_handle_impl
(p_nedbaccessid IN number,
p_Alarmseqnum IN number default 0,
p_ModuleOrIfIndex IN number,
p_ActiveAlarmTimeStamp IN date,
p_ActiveAlarmType IN number,
p_ActiveAlarmSeverity IN number,
p_ActiveAlarmServEff IN number,
p_ActiveAlarmAdditionalInfo IN varchar2,
p_AckIndex IN number,
p_ActiveAlarmFlag IN number,
p_ActiveAlarmComment IN varchar2 default null,
p_NEAlarmTimeStamp IN date,
p_ModuleType IN number,
p_Physicalloc IN number,
p_alarmstatus IN number,
p_linkid IN link_id_tabtype,
p_actiontype IN number,
p_externalcondition IN varchar2,
p_ModelType IN number,
p_ObjectType IN number,
p_StrObjInstance IN varchar2)
IS
--INVALID_ALARM_STATUS EXCEPTION;
--PRAGMA EXCEPTION_INIT (INVALID_ALARM_STATUS, -20002);
counter number:=0;
total_num_linkid number:=0;
BEGIN
 
IF (p_alarmstatus = 0) THEN
-- this is the new alarm
begin
INSERT INTO active_alarm_table (
NEDBACCESSID,
ALARMSEQNUM,
MODULEORIFINDEX,
ACTIVEALARMTIMESTAMP,
ACTIVEALARMTYPE,
ACTIVEALARMSEVERITY,
ACTIVEALARMSERVEFF,
ACTIVEALARMADDITIONALINFO,
ACKINDEX,
ACTIVEALARMFLAG,
NEALARMTIMESTAMP,
ACTIVEALARMINDEX,
MODULETYPE,
PHYSICALLOC,
ALARMSTATUS,
EXTERNALCONDITION,
MODELTYPE,
OBJECTTYPE,
STROBJINSTANCE)
Values (
p_nedbaccessid,
p_alarmseqnum,
p_moduleorifindex,
p_activealarmtimestamp,
p_activealarmtype,
p_activealarmseverity,
p_activealarmserveff,
p_activealarmadditionalinfo,
p_ackindex,
p_activealarmflag,
p_nealarmtimestamp,
activealarmindex_seq.nextval,
p_moduletype,
p_physicalloc,
p_alarmstatus,
p_externalcondition,
p_ModelType,
p_ObjectType,
p_StrObjInstance);
exception
when DUP_VAL_ON_INDEX then
dbms_output.put_line('Duplicate alarm');
create_file('/tmp','alarm_handling.log','Duplicated alarms');
 
select /*+ INDEX_FFS(ACTIVE_ALARM_PK) */ count(*) into counter from active_alarm_table
where nedbaccessid=p_nedbaccessid
AND moduleorifindex=p_ModuleOrIfIndex
AND ActiveAlarmType=p_ActiveAlarmType
AND NEAlarmTimeStamp=p_NEAlarmTimeStamp
AND Physicalloc=p_Physicalloc
AND ObjectType = p_ObjectType
AND alarmstatus=0;
 
UPDATE active_alarm_table
SET activealarmflag=0,
alarmstatus=0,
clearalarmtimestamp=to_date(''),
nealarmcleartimestamp=to_date(''),
ActiveAlarmSeverity=p_ActiveAlarmSeverity,
ActiveAlarmServEff=p_ActiveAlarmServEff,
ActiveAlarmAdditionalInfo=p_ActiveAlarmAdditionalInfo,
ModuleType=p_ModuleType,
StrObjInstance = p_StrObjInstance
WHERE nedbaccessid=p_nedbaccessid
AND moduleorifindex=p_ModuleOrIfIndex
AND ActiveAlarmType=p_ActiveAlarmType
AND NEAlarmTimeStamp=p_NEAlarmTimeStamp
AND Physicalloc=p_Physicalloc
AND ObjectType = p_ObjectType
AND ModelType = p_ModelType;
--commit;
IF ( counter = 1) THEN
return;
END IF;
--commit;
end;
 
total_num_linkid := p_linkid.count;
FOR table_row IN 1 .. total_num_linkid LOOP
--dbms_output.put_line('p_linkid=' || p_linkid(table_row));
IF (p_linkid(table_row) != -1) THEN
IF (p_activealarmseverity = 4) THEN
UPDATE link_table
SET NumWarningAlarms=NumWarningAlarms+1
WHERE linkid=p_linkid(table_row);
--commit;
END IF;
 
IF (p_activealarmseverity = 5) Then
UPDATE link_table
SET NumMinorAlarms=NumMinorAlarms+1
WHERE linkid = p_linkid(table_row);
--commit;
END IF;
 
IF (p_activealarmseverity = 6) THEN
UPDATE link_table
SET NumMajorAlarms=NumMajorAlarms+1
WHERE linkid = p_linkid(table_row);
--commit;
END IF;
 
IF (p_activealarmseverity = 7) THEN
UPDATE link_table
SET NumCriticalAlarms=NumCriticalAlarms+1
WHERE linkid= p_linkid(table_row);
--commit;
END IF;
END IF;
END LOOP;
ELSE
raise INVALID_ALARM_STATUS;
END IF;
 
END;
 
Procedure clear_alarms_impl
(p_nedbaccessid IN number,
p_ModuleOrIfIndex IN number,
p_ActiveAlarmType IN number,
p_ActiveAlarmSeverity IN number,
p_clearalarmtimestamp IN date,
p_NEAlarmClearTimeStamp IN date,
p_Physicalloc IN number,
p_alarmstatus IN number,
p_linkid IN link_id_tabtype,
p_actiontype IN number,
p_ModelType IN number,
p_ObjectType IN number,
p_StrObjInstance IN varchar2)
IS
rows_processed number :=0;
cursor_handle INTEGER;
statement_txt varchar2(4000):= null;
total_num_linkid number:=0;
 
--ALARM_NOT_FOUND EXCEPTION;
--PRAGMA EXCEPTION_INIT (ALARM_NOT_FOUND, -20000);
--INVALID_ALARM_STATUS EXCEPTION;
--PRAGMA EXCEPTION_INIT (INVALID_ALARM_STATUS, -20002);
BEGIN
---- alarm resync
IF (p_alarmstatus = 1 ) THEN
IF (p_actiontype=1) THEN
cursor_handle := DBMS_SQL.OPEN_CURSOR;
dbms_output.put_line(p_nedbaccessid);
 
statement_txt:='Update active_alarm_table set alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||'), activealarmflag=0, NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||')';
statement_txt := statement_txt||' Where alarmstatus=0 AND activealarmflag =1 AND nedbaccessid= '||p_nedbaccessid ;
statement_txt := statement_txt||' and moduleorifindex='||p_moduleorifindex;
statement_txt := statement_txt||' and activealarmtype='||p_activealarmtype;
-- statement_txt := statement_txt||' and activealarmseverity='||p_activealarmseverity;
statement_txt := statement_txt||' and physicalloc='||p_physicalloc;
statement_txt := statement_txt||' and modeltype='||p_ModelType;
statement_txt := statement_txt||' and objecttype='||p_ObjectType;
statement_txt := statement_txt||' and strobjinstance='||''''||p_StrObjInstance||'''';
 
--dbms_output.put_line(statement_txt);
DBMS_SQL.PARSE(cursor_handle,statement_txt, DBMS_SQL.NATIVE);
rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);
--commit;
IF (rows_processed=0) THEN
dbms_output.put_line('Alarm_not_found');
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
raise ALARM_NOT_FOUND;
END IF;
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
END IF;
IF (p_actiontype=2) THEN
-- this when in service ne put out of service, keep the activealarmflag=2
cursor_handle := DBMS_SQL.OPEN_CURSOR;
statement_txt:='Update active_alarm_table set alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||'), activealarmflag =2, NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||')';
statement_txt := statement_txt||' Where alarmstatus=0 AND nedbaccessid= '||p_nedbaccessid ;
statement_txt := statement_txt||' and moduleorifindex='||p_moduleorifindex;
statement_txt := statement_txt||' and activealarmtype='||p_activealarmtype;
statement_txt := statement_txt||' and physicalloc='||p_physicalloc;
statement_txt := statement_txt||' and modeltype='||p_ModelType;
statement_txt := statement_txt||' and objecttype='||p_ObjectType;
statement_txt := statement_txt||' and strobjinstance='||''''||p_StrObjInstance||'''';
DBMS_SQL.PARSE(cursor_handle,statement_txt, DBMS_SQL.NATIVE);
rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);
--commit;
IF (rows_processed=0) THEN
dbms_output.put_line('Alarm_not_found');
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
raise ALARM_NOT_FOUND;
END IF;
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
END IF;
 
------ Normal clear alarms
IF (p_actiontype = 0) THEN
cursor_handle := DBMS_SQL.OPEN_CURSOR;
statement_txt:='Update active_alarm_table set alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||'), NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||')' ;
statement_txt := statement_txt||' Where alarmstatus=0 AND nedbaccessid= '||p_nedbaccessid ;
statement_txt := statement_txt||' and moduleorifindex='||p_moduleorifindex;
statement_txt := statement_txt||' and activealarmtype='||p_activealarmtype;
-- statement_txt := statement_txt||' and activealarmseverity='||p_activealarmseverity;
statement_txt := statement_txt||' and physicalloc='||p_physicalloc;
statement_txt := statement_txt||' and modeltype='||p_ModelType;
statement_txt := statement_txt||' and objecttype='||p_ObjectType;
statement_txt := statement_txt||' and strobjinstance='||''''||p_StrObjInstance||'''';
-- dbms_output.put_line(statement_txt);
DBMS_SQL.PARSE(cursor_handle,statement_txt, DBMS_SQL.NATIVE);
rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);
--commit;
IF (rows_processed=0) THEN
dbms_output.put_line('Alarm_not_found');
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
raise ALARM_NOT_FOUND;
END IF;
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
END IF;
 
total_num_linkid := p_linkid.count;
FOR table_row IN 1 .. total_num_linkid LOOP
IF (p_linkid(table_row) != -1) THEN
IF (p_activealarmseverity = 4) THEN
UPDATE link_table
SET NumWarningAlarms=NumWarningAlarms-1
WHERE linkid=p_linkid(table_row)
AND NumWarningAlarms > 0;
--commit;
END IF;
 
IF (p_activealarmseverity = 5) Then
UPDATE link_table
SET NumMinorAlarms=NumMinorAlarms-1
WHERE linkid = p_linkid(table_row)
AND NumMinorAlarms > 0;
--commit;
END IF;
 
IF (p_activealarmseverity = 6) THEN
UPDATE link_table
SET NumMajorAlarms=NumMajorAlarms-1
WHERE linkid = p_linkid(table_row)
AND NumMajorAlarms > 0;
--commit;
END IF;
 
IF (p_activealarmseverity = 7) THEN
UPDATE link_table
SET NumCriticalAlarms=NumCriticalAlarms-1
WHERE linkid= p_linkid(table_row)
AND NumCriticalAlarms > 0;
--commit;
END IF;
 
END IF;
END LOOP;
ELSE
raise INVALID_ALARM_STATUS;
END IF;
END;
 
Procedure add_clear_alarms_impl
(p_nedbaccessid IN number,
p_Alarmseqnum IN number default 0,
p_ModuleOrIfIndex IN number,
p_ActiveAlarmTimeStamp IN date,
p_ActiveAlarmType IN number,
p_ActiveAlarmSeverity IN number,
p_ActiveAlarmServEff IN number,
p_ActiveAlarmAdditionalInfo IN varchar2,
p_AckIndex IN number,
p_ActiveAlarmFlag IN number,
p_ClearAlarmTimeStamp IN date,
p_NEAlarmTimeStamp IN date,
p_NEAlarmClearTimeStamp IN date,
p_ModuleType IN number,
p_Physicalloc IN number,
p_alarmstatus IN number,
p_linkid IN link_id_tabtype,
p_actiontype IN number,
p_externalcondition IN varchar2,
p_ModelType IN number,
p_ObjectType IN number,
p_StrObjInstance IN varchar2)
IS
rows_processed number :=0;
cursor_handle INTEGER;
statement_txt varchar2(4000):= null;
total_num_linkid number:=0;
 
--ALARM_NOT_FOUND EXCEPTION;
--PRAGMA EXCEPTION_INIT (ALARM_NOT_FOUND, -20000);
--INVALID_ALARM_STATUS EXCEPTION;
--PRAGMA EXCEPTION_INIT (INVALID_ALARM_STATUS, -20002);
BEGIN
---- alarm resync
IF (p_alarmstatus = 1 ) THEN
IF (p_actiontype=1) THEN
cursor_handle := DBMS_SQL.OPEN_CURSOR;
dbms_output.put_line(p_nedbaccessid);
 
statement_txt:='Update active_alarm_table set alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||'), activealarmflag=0, NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||')';
statement_txt := statement_txt||' Where alarmstatus=0 AND activealarmflag =1 AND nedbaccessid= '||p_nedbaccessid ;
statement_txt := statement_txt||' and moduleorifindex='||p_moduleorifindex;
statement_txt := statement_txt||' and activealarmtype='||p_activealarmtype;
-- statement_txt := statement_txt||' and activealarmseverity='||p_activealarmseverity;
statement_txt := statement_txt||' and physicalloc='||p_physicalloc;
statement_txt := statement_txt||' and modeltype='||p_ModelType;
statement_txt := statement_txt||' and objecttype='||p_ObjectType;
statement_txt := statement_txt||' and strobjinstance='||''''||p_StrObjInstance||'''';
 
dbms_output.put_line(statement_txt);
DBMS_SQL.PARSE(cursor_handle,statement_txt, DBMS_SQL.NATIVE);
rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);
--commit;
IF (rows_processed=0) THEN
dbms_output.put_line('Alarm_not_found');
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
raise ALARM_NOT_FOUND;
END IF;
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
END IF;
 
IF (p_actiontype=2) THEN
-- this when in service ne put out of service, keep the activealarmflag=2
cursor_handle := DBMS_SQL.OPEN_CURSOR;
statement_txt:='Update active_alarm_table set alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||'), activealarmflag =2, NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||')';
statement_txt := statement_txt||' Where alarmstatus=0 AND nedbaccessid= '||p_nedbaccessid ;
statement_txt := statement_txt||' and moduleorifindex='||p_moduleorifindex;
statement_txt := statement_txt||' and activealarmtype='||p_activealarmtype;
statement_txt := statement_txt||' and physicalloc='||p_physicalloc;
statement_txt := statement_txt||' and modeltype='||p_ModelType;
statement_txt := statement_txt||' and objecttype='||p_ObjectType;
statement_txt := statement_txt||' and strobjinstance='||''''||p_StrObjInstance||'''';
 
DBMS_SQL.PARSE(cursor_handle,statement_txt, DBMS_SQL.NATIVE);
rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);
--commit;
IF (rows_processed=0) THEN
dbms_output.put_line('Alarm_not_found');
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
raise ALARM_NOT_FOUND;
END IF;
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
END IF;
 
------ Normal clear alarms
IF (p_actiontype = 0) THEN
cursor_handle := DBMS_SQL.OPEN_CURSOR;
statement_txt:='Update active_alarm_table set alarmstatus=1,clearalarmtimestamp=to_date('||''''||p_clearalarmtimestamp||''''||'), NEAlarmClearTimestamp=to_date('||''''||p_NEAlarmClearTimestamp||''''||')' ;
statement_txt := statement_txt||' Where alarmstatus=0 AND nedbaccessid= '||p_nedbaccessid ;
statement_txt := statement_txt||' and moduleorifindex='||p_moduleorifindex;
statement_txt := statement_txt||' and activealarmtype='||p_activealarmtype;
-- statement_txt := statement_txt||' and activealarmseverity='||p_activealarmseverity;
statement_txt := statement_txt||' and physicalloc='||p_physicalloc;
statement_txt := statement_txt||' and modeltype='||p_ModelType;
statement_txt := statement_txt||' and objecttype='||p_ObjectType;
statement_txt := statement_txt||' and strobjinstance='||''''||p_StrObjInstance||'''';
-- dbms_output.put_line(statement_txt);
DBMS_SQL.PARSE(cursor_handle,statement_txt, DBMS_SQL.NATIVE);
rows_processed:=DBMS_SQL.EXECUTE(cursor_handle);
--commit;
IF (rows_processed=0) THEN
dbms_output.put_line('Alarm_not_found');
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
raise ALARM_NOT_FOUND;
END IF;
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
END IF;
total_num_linkid := p_linkid.count;
FOR table_row IN 1 .. total_num_linkid LOOP
IF (p_linkid(table_row) != -1) THEN
IF (p_activealarmseverity = 4) THEN
UPDATE link_table
SET NumWarningAlarms=NumWarningAlarms-1
WHERE linkid=p_linkid(table_row)
AND NumWarningAlarms > 0;
--commit;
END IF;
 
IF (p_activealarmseverity = 5) Then
UPDATE link_table
SET NumMinorAlarms=NumMinorAlarms-1
WHERE linkid = p_linkid(table_row)
AND NumMinorAlarms > 0;
--commit;
END IF;
 
IF (p_activealarmseverity = 6) THEN
UPDATE link_table
SET NumMajorAlarms=NumMajorAlarms-1
WHERE linkid = p_linkid(table_row)
AND NumMajorAlarms > 0;
--commit;
END IF;
 
IF (p_activealarmseverity = 7) THEN
UPDATE link_table
SET NumCriticalAlarms=NumCriticalAlarms-1
WHERE linkid= p_linkid(table_row)
AND NumCriticalAlarms > 0;
--commit;
END IF;
 
END IF;
END LOOP;
ELSE
raise INVALID_ALARM_STATUS;
END IF;
EXCEPTION
when ALARM_NOT_FOUND then
dbms_output.put_line('New insertion of cleared alarm');
IF (p_actiontype = 1 OR p_actiontype = 2) THEN
INSERT INTO active_alarm_table (
NEDBACCESSID,
ALARMSEQNUM,
MODULEORIFINDEX,
ACTIVEALARMTIMESTAMP,
ACTIVEALARMTYPE,
ACTIVEALARMSEVERITY,
ACTIVEALARMSERVEFF,
ACTIVEALARMADDITIONALINFO,
ACKINDEX,
ACTIVEALARMFLAG,
CLEARALARMTIMESTAMP,
NEALARMTIMESTAMP,
NEALARMCLEARTIMESTAMP,
ACTIVEALARMINDEX,
MODULETYPE,
PHYSICALLOC,
ALARMSTATUS,
EXTERNALCONDITION,
MODELTYPE,
OBJECTTYPE,
STROBJINSTANCE)
Values (
p_nedbaccessid,
p_alarmseqnum,
p_moduleorifindex,
p_activealarmtimestamp,
p_activealarmtype,
p_activealarmseverity,
p_activealarmserveff,
p_activealarmadditionalinfo,
p_ackindex,
p_activealarmflag,
p_clearalarmtimestamp,
p_nealarmtimestamp,
p_nealarmcleartimestamp,
activealarmindex_seq.nextval,
p_moduletype,
p_physicalloc,
p_alarmstatus,
p_externalcondition,
p_ModelType,
p_ObjectType,
p_StrObjInstance);
--commit;
END IF;
 
END;
 
Procedure alarm_handle
(
p_nedbaccessid IN number,
p_Alarmseqnum IN number default 0,
p_ModuleOrIfIndex IN number,
p_ActiveAlarmTimeStamp IN date,
p_ActiveAlarmType IN number,
p_ActiveAlarmSeverity IN number,
p_ActiveAlarmServEff IN number,
p_ActiveAlarmAdditionalInfo IN varchar2,
p_AckIndex IN number,
p_ActiveAlarmFlag IN number,
p_ActiveAlarmComment IN varchar2 default null,
p_NEAlarmTimeStamp IN date,
p_ModuleType IN number,
p_Physicalloc IN number,
p_alarmstatus IN number,
p_linkid IN link_id_tabtype,
p_actiontype IN number,
p_externalcondition IN varchar2,
p_ModelType IN number,
p_ObjectType IN number,
p_StrObjInstance IN varchar2
)
IS
BEGIN
--dbms_output.put_line('alarm_handle: calling alarm_handle_impl: nedbaccessid=' || p_nedbaccessid || ', ActiveAlarmComment=' || p_ActiveAlarmComment || ', alarmstatus=' || p_alarmstatus);
 
alarm_handle_impl
(
p_nedbaccessid,
p_Alarmseqnum,
p_ModuleOrIfIndex,
p_ActiveAlarmTimeStamp,
p_ActiveAlarmType,
p_ActiveAlarmSeverity,
p_ActiveAlarmServEff,
p_ActiveAlarmAdditionalInfo,
p_AckIndex,
p_ActiveAlarmFlag,
p_ActiveAlarmComment,
p_NEAlarmTimeStamp,
p_ModuleType,
p_Physicalloc,
p_alarmstatus,
p_linkid,
p_actiontype,
p_externalcondition,
p_ModelType,
p_ObjectType,
p_StrObjInstance
);
 
commit;
 
END;
 
Procedure clear_alarms
(
p_nedbaccessid IN number,
p_ModuleOrIfIndex IN number,
p_ActiveAlarmType IN number,
p_ActiveAlarmSeverity IN number,
p_clearalarmtimestamp IN date,
p_NEAlarmClearTimeStamp IN date,
p_Physicalloc IN number,
p_alarmstatus IN number,
p_linkid IN link_id_tabtype,
p_actiontype IN number,
p_ModelType IN number,
p_ObjectType IN number,
p_StrObjInstance IN varchar2
)
IS
BEGIN
--dbms_output.put_line('clear_alarms: calling clear_alarms_impl: nedbaccessid=' || p_nedbaccessid || ', alarmstatus=' || p_alarmstatus);
 
clear_alarms_impl
(
p_nedbaccessid,
p_ModuleOrIfIndex,
p_ActiveAlarmType,
p_ActiveAlarmSeverity,
p_clearalarmtimestamp,
p_NEAlarmClearTimeStamp,
p_Physicalloc,
p_alarmstatus,
p_linkid,
p_actiontype,
p_ModelType,
p_ObjectType,
p_StrObjInstance
);
 
commit;
END;
 
Procedure add_clear_alarms
(
p_nedbaccessid IN number,
p_Alarmseqnum IN number default 0,
p_ModuleOrIfIndex IN number,
p_ActiveAlarmTimeStamp IN date,
p_ActiveAlarmType IN number,
p_ActiveAlarmSeverity IN number,
p_ActiveAlarmServEff IN number,
p_ActiveAlarmAdditionalInfo IN varchar2,
p_AckIndex IN number,
p_ActiveAlarmFlag IN number,
p_ClearAlarmTimeStamp IN date,
p_NEAlarmTimeStamp IN date,
p_NEAlarmClearTimeStamp IN date,
p_ModuleType IN number,
p_Physicalloc IN number,
p_alarmstatus IN number,
p_linkid IN link_id_tabtype,
p_actiontype IN number,
p_externalcondition IN varchar2,
p_ModelType IN number,
p_ObjectType IN number,
p_StrObjInstance IN VARCHAR2
)
IS
BEGIN
--dbms_output.put_line('add_clear_alarms: calling add_clear_alarms_impl: nedbaccessid=' || p_nedbaccessid || ', alarmstatus=' || p_alarmstatus);
 
add_clear_alarms_impl
(
p_nedbaccessid,
p_Alarmseqnum,
p_ModuleOrIfIndex,
p_ActiveAlarmTimeStamp,
p_ActiveAlarmType,
p_ActiveAlarmSeverity,
p_ActiveAlarmServEff,
p_ActiveAlarmAdditionalInfo,
p_AckIndex,
p_ActiveAlarmFlag,
p_ClearAlarmTimeStamp,
p_NEAlarmTimeStamp,
p_NEAlarmClearTimeStamp,
p_ModuleType,
p_Physicalloc,
p_alarmstatus,
p_linkid,
p_actiontype,
p_externalcondition,
p_ModelType,
p_ObjectType,
p_StrObjInstance
);
 
commit;
END;
 
Procedure bulk_alarm_handle
(
p_AlarmInfoList IN AlarmInfoListType
)
IS
i number;
BEGIN
For I IN p_AlarmInfoList.FIRST .. p_AlarmInfoList.LAST
LOOP
--dbms_output.put_line('bulk_alarm_handle: calling alarm_handle_impl: i=' || i || ':, nedbaccessid=' || p_AlarmInfoList(i).nedbaccessid || ', ActiveAlarmComment=' || p_AlarmInfoList(i).ActiveAlarmComment || ', alarmstatus=' || p_AlarmInfoList(i).alarmstatus);
 
alarm_handle_impl (
p_AlarmInfoList(i).nedbaccessid,
p_AlarmInfoList(i).Alarmseqnum,
p_AlarmInfoList(i).ModuleOrIfIndex,
p_AlarmInfoList(i).ActiveAlarmTimeStamp,
p_AlarmInfoList(i).ActiveAlarmType,
p_AlarmInfoList(i).ActiveAlarmSeverity,
p_AlarmInfoList(i).ActiveAlarmServEff,
p_AlarmInfoList(i).ActiveAlarmAdditionalInfo,
p_AlarmInfoList(i).AckIndex,
p_AlarmInfoList(i).ActiveAlarmFlag,
p_AlarmInfoList(i).ActiveAlarmComment,
p_AlarmInfoList(i).NEAlarmTimeStamp,
p_AlarmInfoList(i).ModuleType,
p_AlarmInfoList(i).Physicalloc,
p_AlarmInfoList(i).alarmstatus,
p_AlarmInfoList(i).linkid,
p_AlarmInfoList(i).actiontype,
p_AlarmInfoList(i).externalcondition,
p_AlarmInfoList(i).ModelType,
p_AlarmInfoList(i).ObjectType,
p_AlarmInfoList(i).StrObjInstance
);
 
END LOOP;
 
commit;
END;
 
Procedure bulk_clear_alarms
(
p_AlarmInfoList IN AlarmInfoListType
)
IS
i number;
BEGIN
For I IN p_AlarmInfoList.FIRST .. p_AlarmInfoList.LAST
LOOP
dbms_output.put_line('bulk_clear_alarms: calling clear_alarms_impl: i=' || i || ':, nedbaccessid=' || p_AlarmInfoList(i).nedbaccessid || ', ActiveAlarmComment=' || p_AlarmInfoList(i).ActiveAlarmComment || ', alarmstatus=' || p_AlarmInfoList(i).alarmstatus);
 
clear_alarms_impl
(
p_AlarmInfoList(i).nedbaccessid,
p_AlarmInfoList(i).ModuleOrIfIndex,
p_AlarmInfoList(i).ActiveAlarmType,
p_AlarmInfoList(i).ActiveAlarmSeverity,
p_AlarmInfoList(i).clearalarmtimestamp,
p_AlarmInfoList(i).NEAlarmClearTimeStamp,
p_AlarmInfoList(i).Physicalloc,
p_AlarmInfoList(i).alarmstatus,
p_AlarmInfoList(i).linkid,
p_AlarmInfoList(i).actiontype,
p_AlarmInfoList(i).ModelType,
p_AlarmInfoList(i).ObjectType,
p_AlarmInfoList(i).StrObjInstance
);
 
END LOOP;
 
commit;
END;
 
Procedure bulk_add_clear_alarms
(
p_AlarmInfoList IN AlarmInfoListType
)
IS
i number;
BEGIN
For I IN p_AlarmInfoList.FIRST .. p_AlarmInfoList.LAST
LOOP
--dbms_output.put_line('bulk_add_clear_alarms: calling add_clear_alarms_impl: i=' || i || ':, nedbaccessid=' || p_AlarmInfoList(i).nedbaccessid || ', ActiveAlarmComment=' || p_AlarmInfoList(i).ActiveAlarmComment || ', alarmstatus=' || p_AlarmInfoList(i).alarmstatus);
 
add_clear_alarms_impl
(
p_AlarmInfoList(i).nedbaccessid,
p_AlarmInfoList(i).Alarmseqnum,
p_AlarmInfoList(i).ModuleOrIfIndex,
p_AlarmInfoList(i).ActiveAlarmTimeStamp,
p_AlarmInfoList(i).ActiveAlarmType,
p_AlarmInfoList(i).ActiveAlarmSeverity,
p_AlarmInfoList(i).ActiveAlarmServEff,
p_AlarmInfoList(i).ActiveAlarmAdditionalInfo,
p_AlarmInfoList(i).AckIndex,
p_AlarmInfoList(i).ActiveAlarmFlag,
p_AlarmInfoList(i).ClearAlarmTimeStamp,
p_AlarmInfoList(i).NEAlarmTimeStamp,
p_AlarmInfoList(i).NEAlarmClearTimeStamp,
p_AlarmInfoList(i).ModuleType,
p_AlarmInfoList(i).Physicalloc,
p_AlarmInfoList(i).alarmstatus,
p_AlarmInfoList(i).linkid,
p_AlarmInfoList(i).actiontype,
p_AlarmInfoList(i).externalcondition,
p_AlarmInfoList(i).ModelType,
p_AlarmInfoList(i).ObjectType,
p_AlarmInfoList(i).StrObjInstance
);
 
END LOOP;
 
commit;
 
END;
 
END CTMAlarm;
/

3.1.2 BulkNEAddition

CTM Stored Procedures 」の項にある項目「 BulkNEAddition 」は、次の情報で置き換えます。

create or replace procedure BulkNEAddition
(OperationType IN number,
GroupToBeInserted IN number,
GroupName IN varchar2,
GroupDescription IN varchar2,
GroupLocationName IN varchar2,
GneToBeInserted IN number,
GneSysId IN varchar2,
NextHopIpAddr IN number,
GroupingOption IN number,
GroupPrefix IN varchar2,
inUserId IN number,
SubNWToBeInserted IN number,
SubNwName IN OUT varchar2,
SubnetType IN number,
SubnetTopology IN number,
NWPartitionId IN number,
Is216NE IN number,
InNeSysId IN varchar2,
Ipaddr IN number,
OperationalState IN number,
IsConnected IN number,
GneId IN OUT number,
NeDescription IN varchar2,
NeConfigMode IN number,
SnmpCommunityString IN varchar2,
ClliCode IN varchar2,
NeModelType IN number,
NeModelIndex IN number,
SubNwId IN OUT number,
SystemTitle IN varchar2,
UserLabel IN varchar2,
VendorName IN varchar2,
VersionName IN varchar2,
NePmEnabled IN number,
DisplayModelName IN varchar2,
GwTL1UserName IN varchar2,
GwTL1Password IN varchar2,
NeDiscState IN number,
pmCollectionFlag IN number,
PmTicket IN varchar2,
LaunchContext IN number,
ParentGroupId IN number,
NewGroupId OUT number,
NeId OUT number,
error_code OUT number,
error_msg OUT varchar2,
inNEAliasID IN varchar2,
inRoleType IN varchar2)
IS
i_groupid number;
i_gneid number;
i_subNWName varchar2(64);
i_subnetid number;
i_Neid number;
i_parenttype number;
assigned_group number;
UserProperty number;
counter number:=0;
i_addnewsubnet number:=0;
 
BulkNeAdd_FAILED EXCEPTION;
BEGIN
IF (GroupToBeInserted = 1) THEN
SELECT groupId_seq.nextval INTO i_groupId from dual;
INSERT INTO group_info_table
VALUES(i_groupid, GroupName,GroupDescription,'',GroupLocationName);
END IF;
 
IF (GneToBeInserted = 1) THEN
SELECT GNEID_SEQ.nextval INTO i_gneid from dual;
INSERT INTO gne_table
VALUES(i_gneid, 0,1, GneSysid,NextHopIpaddr,'',GroupingOption,GroupPrefix, inUserId);
ELSE
i_gneid:=GneId;
END IF;
 
i_addnewsubnet:= SubNWToBeInserted;
IF (i_addnewsubnet = 0) THEN
SELECT count(*) INTO counter from subnetwork_table WHERE subnetid=SubNwId;
IF (counter = 0) THEN
i_addnewsubnet:=1;
ELSE
i_addnewsubnet:=0;
END IF;
END IF;
 
IF (i_addnewsubnet = 1) THEN
SELECT SUBNETID_SEQ.nextval INTO i_subnetid FROM dual;
IF (subNwName is null) THEN
SELECT 'Subnetwork-'||lpad(i_subnetid,8,'0')
INTO i_subNwName from dual;
SELECT count(*) INTO counter from subnetwork_table
WHERE subnetname=i_subNwName;
IF (counter>0) THEN
i_subNwName:=i_subNwName||'-1';
END IF;
ELSE
i_subNWName:=subNwName;
END IF;
INSERT INTO subnetwork_table
VALUES (i_subnetid, i_subNWName,'','',SubnetType, SubnetTopology,-1,NWPartitionId);
ELSE
i_subnetid :=SubNwId;
END IF;
 
SELECT NEDBACCESSID_SEQ.nextval INTO i_neid FROM dual;
 
INSERT INTO ne_info_table
VALUES (i_neid,
InNeSysID,
IpAddr,
'',
OperationalState,
isConnected,
i_gneid,
NeDescription,
neConfigMode,
SnmpCommunityString,
ClliCode,
'',
NeModelType,
NeModelIndex,
0,
i_subnetid,
0,
'',
systemTitle,
userLabel,
vendorName,
versionName,
0,
0,
0,
nepmenabled,
0,
0,
0,
0,
0,
0,
0,
0,
DisplayModelName,
GwTL1UserName,
GwTL1Password,
0,
0,
0,
null,
null,
NeDiscState,
pmCollectionFlag,
PmTicket,
'',
-1,
'',
0,
0,
0,
decode(inNEAliasID, null, InNESysid, inNEAliasID),
0,
inRoleType
);
 
IF( Is216NE = 1 ) THEN
UPDATE NE_INFO_TABLE set NEIPADDR=''
WHERE NESYSID=InNeSysId;
END IF;
BEGIN
SELECT Properties INTO userProperty
FROM user_type_table
WHERE usertypeid = ( select subtypeofuser from user_table where userid = InUserId);
 
IF (userProperty =2) THEN
SELECT count(*) INTO assigned_group
FROM user_map_table
WHERE userid=inUserid
AND (GroupOrNEID IN ( SELECT parentid FROM domain_table
WHERE childType=2
START WITH parentid=parentGroupid
CONNECT by prior parentid=childid)
OR GroupOrNEID =parentGroupid);
IF (assigned_group = 0) THEN
IF (GroupToBeInserted =1) THEN
INSERT INTO user_map_table
VALUES (inUserId, 2, i_groupid);
ELSE
INSERT INTO user_map_table
VALUES(inUserId, 3, i_neid);
END IF;
END IF;
END IF;
Exception
WHEN NO_DATA_FOUND
THEN null;
END;
 
IF (parentGroupId = 0) THEN
i_parenttype:=1;
ELSE
i_parenttype:=2;
END IF;
 
IF (GroupToBeInserted = 1 ) THEN
insert into domain_table
values(DT_TREENODEID_SEQ.nextval, i_parenttype, parentGroupid, 2, i_groupid);
INSERT INTO domain_table
VALUES (DT_TREENODEID_SEQ.nextval, 2, i_groupid, 3,i_neid);
ELSE
INSERT INTO domain_table
VALUES (DT_TREENODEID_SEQ.nextval, i_parenttype, parentGroupid, 3,i_neid);
END IF;
 
INSERT INTO Cerent_Ne_Group_Table VALUES (i_neid, GroupingOption);
 
commit;
 
neId:= i_neid;
newGroupId:=i_groupid;
subNwname:=i_subNwName;
subNwId:=i_subnetId;
GneId:=i_GneId;
--create_file('/oraclesw9i','bulkneadd.log','neid:'||neId||' newGroupId:'||newGroupId||' subNwname:'||subNwname||' subNwId:'||subNwId||' GneId:'||GneId);
 
EXCEPTION
WHEN OTHERS THEN
error_code := SQLCODE;
error_msg:= SQLERRM;
raise BulkNeAdd_Failed;
rollback;
END;
/
 

3.1.3 add_log_partition

CTM Stored Procedures 」の項にある項目「 add_log_partition 」は、次の情報で置き換えます。


) add_log_partition プロシージャは、中規模、大規模およびハイエンド用構成だけを対象にします。


create or replace procedure add_log_partition AS
partition_to_be_added varchar2(9);
cursor_handle integer;
statement_txt varchar2(500);
rows_processed number := 0;
 
p_tablespace_name varchar2(25);
 
new_partition_date date;
new_partition_name varchar2(9);
ORACLE_SID varchar2(20);
add_flag number;
 
BEGIN
SELECT activevalue INTO ORACLE_SID
FROM ctm_config_table
WHERE sectionname='database' and propertyname='dbname';
 
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','add_log_partition.log','Starting adding partition at '||sysdate);
FOR rec IN ( SELECT table_name FROM user_tables
WHERE table_name in (
'ERROR_LOG_TABLE', 'SERVER_MONITOR_TABLE',
'TRANSACTION_LOG_TABLE','SYSLOG_MESSAGE_TABLE',
'NE_AUDIT_TRAIL_TABLE', 'ONS158XX_LAC_LOG_TABLE'
))
 
LOOP
add_flag:=1;
/* define date range for the partition to be added */
Select max(to_date( substr(partition_name,2,9))),tablespace_name
INTO new_partition_date, p_tablespace_name
From user_segments
Where segment_name =rec.table_name
group by tablespace_name;
 
IF (sysdate >= new_partition_date) THEN
new_partition_date:=sysdate+1;
ELSIF (new_partition_date >= sysdate+3) THEN
add_flag:=0;
Else
new_partition_date:= new_partition_date+1;
END IF;
IF (add_flag=1) THEN
partition_to_be_added:= 'p'||to_char(new_partition_date,'mmddyyyy');
/* add the new partition */
cursor_handle := DBMS_SQL.OPEN_CURSOR;
statement_txt := 'alter table '||rec.table_name ||' add partition '||partition_to_be_added||
' values less than ('||''''||new_partition_date||''''||')
TABLESPACE '||p_tablespace_name;
DBMS_OUTPUT.PUT_LINE(statement_txt);
DBMS_SQL.PARSE(cursor_handle, statement_txt, DBMS_SQL.NATIVE);
rows_processed := DBMS_SQL.EXECUTE(cursor_handle);
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','add_log_partition.log','added partition '||partition_to_be_added||' to '||rec.table_name);
ELSE
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','add_log_partition.log',rec.table_name||' already has log partitions for the next 3 days');
END IF;
 
END LOOP;
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','add_log_partition.log','Adding partition successfully finished at '||sysdate);
EXCEPTION
WHEN OTHERS THEN
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','add_log_partition.err','Error in adding pm partition '||SQLERRM||', '||SQLCODE);
END;
/
 

3.1.4 小規模構成用の prune_package

prune_package 」の項にある項目「prune_package for Small Configurations」は、次の情報で置き換えます。

rem Pruning Package and change prune schedule package
CREATE OR REPLACE PACKAGE Prune AS
 
row_count NUMBER := 0 ;
rows_to_be_deleted NUMBER := 100000 ;
ORACLE_SID varchar2(40);
 
PROCEDURE CheckGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE );
PROCEDURE PruneGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE );
PROCEDURE prune_auditlog( intervaldays IN Number);
PROCEDURE prune_errorlog(intervaldays IN number) ;
PROCEDURE prune_fm( intervaldays IN number);
PROCEDURE prune_server_monitor( intervaldays IN number);
PROCEDURE prune_admin_job_table( intervaldays IN number);
 
END Prune ;
/
show errors;
 
CREATE OR REPLACE PACKAGE BODY Prune AS
 
PROCEDURE CheckGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE ) AS
CURSOR IsThisAGNE IS
SELECT gne.gneid
FROM gne_table gne, ne_info_Table ne
WHERE gne.gnesysid = ne.nesysid AND
ne.nesysid = iv_NESysId ;
 
CURSOR GneChildren ( iv_GNEId ne_info_table.gneid%TYPE ) IS
SELECT ne.nesysid
FROM ne_info_table ne
WHERE ne.gneid = iv_gneId AND
ne.nesysid <> iv_NESysId ;
 
lv_GNEId ne_info_table.GNEId%TYPE := NULL ;
BEGIN
OPEN IsThisAGNE ;
FETCH IsThisAGNE INTO lv_GNEId ;
IF IsThisAGNE%FOUND THEN
FOR rec IN GneChildren(lv_GNEId) LOOP
dbms_output.put_line('NE : '||rec.nesysid);
END LOOP ;
END IF ;
CLOSE IsThisAGNE ;
END CheckGne ;
 
PROCEDURE PruneGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE ) AS
CURSOR IsThisAGNE IS
SELECT gne.gneid
FROM gne_table gne
WHERE gne.gnesysid = iv_NESysid ;
 
CURSOR GneChildren ( iv_GNEId ne_info_table.gneid%TYPE ) IS
SELECT ne.nesysid
FROM ne_info_table ne
WHERE ne.gneid = iv_gneId AND
ne.nesysid <> iv_NESysid ;
 
lv_GNEId ne_info_table.GNEId%TYPE := NULL ;
lv_NESysId ne_info_table.NESysId%TYPE := NULL ;
BEGIN
OPEN IsThisAGNE ;
FETCH IsThisAGNE INTO lv_GNEId ;
IF IsThisAGNE%FOUND THEN
OPEN GneChildren (lv_GNEId ) ;
FETCH GneChildren INTO lv_NESysId ;
IF GNEChildren%NOTFOUND THEN
DELETE gne_table
WHERE GNEId = lv_GNEId ;
COMMIT ;
END IF ;
END IF ;
CLOSE IsThisAGNE ;
END PruneGNE ;
 
PROCEDURE prune_auditlog( intervaldays IN number ) AS
prunedate date;
counter number:=0;
rows_processed number:=0;
TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;
rowid_tab_v rowid_tab;
BEGIN
SELECT activevalue INTO ORACLE_SID
FROM ctm_config_table
WHERE sectionname='database' and propertyname='dbname';
 
IF ( intervaldays != -1 ) THEN
prunedate:=sysdate-intervaldays;
dbms_output.put_line('Prune data before '||prunedate);
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_auditlog.log','Starting prune auditlog at '||sysdate);
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_auditlog.log','Prune transaction log table before '||prunedate);
 
FOR i IN (select /*+ INDEX_FFS(TRANSACTION_LOG_TS_I) */ rowid from transaction_log_table
where timestamp < prunedate ) LOOP
rowid_tab_v(counter) := i.rowid;
counter := counter + 1;
 
IF counter = rows_to_be_deleted THEN
FORALL rid IN rowid_tab_v.FIRST .. rowid_tab_v.LAST
DELETE FROM transaction_log_table
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
counter := 0;
END IF;
END LOOP;
IF counter < rows_to_be_deleted AND counter > 0 THEN
FORALL rid IN rowid_tab_v.FIRST .. rowid_tab_v.LAST
DELETE FROM transaction_log_table
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
counter := 0;
END IF;
 
dbms_output.put_line('Deleted '||rows_processed||' rows from transaction_log_table');
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_auditlog.log','Deleted '||rows_processed||' rows from transaction_log_table');
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_auditlog.log','Finish prune transaction log table at '||sysdate);
COMMIT ;
ELSE
dbms_output.put_line('user choose to keep all audit log data, so no prune at this time');
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_auditlog.log','User choose to keep all data, no prune');
END IF;
EXCEPTION
WHEN OTHERS THEN
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_auditlog.err','Error in pruning audit log '||SQLERRM||', '||SQLCODE);
END;
 
PROCEDURE prune_errorlog(intervaldays IN number) AS
prunedate date;
counter number:=0;
TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;
rowid_tab_v rowid_tab;
rows_processed number :=0;
BEGIN
SELECT activevalue INTO ORACLE_SID
FROM ctm_config_table
WHERE sectionname='database' and propertyname='dbname';
IF ( intervaldays != -1) THEN
prunedate:= sysdate-intervaldays;
dbms_output.put_line('Prune data before '||prunedate);
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_errorlog.log','Starting prune errorlog at '||sysdate);
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_errorlog.log','Prune error log table data before '||prunedate);
 
FOR i IN (select /*+ INDEX_FFS(ERROR_LOG_TIME_INDEX) */ rowid from ERROR_LOG_TABLE
where timestamp < prunedate ) LOOP
rowid_tab_v(counter) := i.rowid;
counter := counter + 1;
 
IF counter = rows_to_be_deleted THEN
FORALL rid IN rowid_tab_v.FIRST .. rowid_tab_v.LAST
DELETE FROM error_log_table
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
counter := 0;
END IF;
END LOOP;
 
IF counter < rows_to_be_deleted AND counter > 0 THEN
FORALL rid IN rowid_tab_v.FIRST .. rowid_tab_v.LAST
DELETE FROM error_log_table
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
counter := 0;
END IF;
 
 
dbms_output.put_line('Deleted '||rows_processed||' rows from error_log_table ');
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_errorlog.log','Deleted '||rows_processed||' rows from error_log_table ');
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_errorlog.log','Finish prune error log table at '||to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
ELSE
dbms_output.put_line('User choose to keep all data, no prune');
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_errorlog.log','User choose to keep all data, no prune');
END IF;
EXCEPTION
WHEN OTHERS THEN
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_errorlog.err','Error in pruning error log '||SQLERRM||', '||SQLCODE);
END;
 
PROCEDURE prune_fm(intervaldays IN number ) AS
prunedate date;
counter number :=0;
TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;
rowid_tab_v rowid_tab;
rows_processed number :=0;
BEGIN
SELECT activevalue INTO ORACLE_SID
FROM ctm_config_table
WHERE sectionname='database' and propertyname='dbname';
IF ( intervaldays != -1) THEN
prunedate:= sysdate-intervaldays;
dbms_output.put_line('Prune data before '||prunedate);
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.log','Starting prune fm at '||sysdate);
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.log','Prune FM data before '||prunedate);
counter:=0;
rows_processed :=0;
LOOP
counter:=0;
FOR i IN (select /*+ INDEX_FFS(ALARM_EVENT_TIME_INDEX) */ rowid from alarm_event_table
where AlarmEventTimeStamp < prunedate and rownum <= rows_to_be_deleted) LOOP
counter := counter + 1;
rowid_tab_v(counter) := i.rowid;
 
IF counter = rows_to_be_deleted THEN
FORALL rid IN 1 .. counter
DELETE FROM alarm_event_table
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
END IF;
END LOOP ;
IF counter < rows_to_be_deleted THEN
IF counter > 0 THEN
FORALL rid IN 1 .. counter
DELETE FROM alarm_event_table
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
counter := 0;
END IF;
exit;
END IF;
END LOOP;
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.log','Deleted '||rows_processed||' rows from alarm_event_table at '|| sysdate);
 
counter:=0;
rows_processed:=0;
LOOP
counter:=0;
FOR i IN (select /*+ INDEX_FFS(ACTIVE_ALARM_TIME_INDEX) */ rowid from ACTIVE_ALARM_TABLE
where ActiveAlarmTimeStamp < prunedate
and alarmstatus = 1
and ackindex <> 0
and rownum <= rows_to_be_deleted) LOOP
counter := counter + 1;
rowid_tab_v(counter) := i.rowid;
 
IF counter = rows_to_be_deleted THEN
FORALL rid IN 1 .. counter
DELETE FROM ACTIVE_ALARM_TABLE
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
END IF;
END LOOP ;
IF counter < rows_to_be_deleted THEN
IF counter > 0 THEN
FORALL rid IN 1 .. counter
DELETE FROM ACTIVE_ALARM_TABLE
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
counter := 0;
END IF;
exit;
END IF;
END LOOP;
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.log','Deleted '||rows_processed||' rows from active_alarm_table at '||sysdate);
 
counter:=0;
rows_processed :=0;
LOOP
counter:=0;
FOR i IN (select /*+ INDEX_FFS(SYSLOG_MESSAGE_TS_I) */ rowid from SYSLOG_MESSAGE_TABLE
where TimeStamp < prunedate and rownum <= rows_to_be_deleted) LOOP
counter := counter + 1;
rowid_tab_v(counter) := i.rowid;
 
IF counter = rows_to_be_deleted THEN
FORALL rid IN 1 .. counter
DELETE FROM SYSLOG_MESSAGE_TABLE
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
END IF;
END LOOP ;
IF counter < rows_to_be_deleted THEN
IF counter > 0 THEN
FORALL rid IN 1 .. counter
DELETE FROM SYSLOG_MESSAGE_TABLE
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
counter := 0;
END IF;
exit;
END IF;
END LOOP;
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.log','Deleted '||rows_processed||' rows from SYSLOG_MESSAGE_TABLE at '|| sysdate);
 
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.log','Finish prune FM at '||sysdate);
ELSE
dbms_output.put_line('User choose to keep all data, no prune at this time');
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.log','User choose to keep all data, no prune');
END IF;
EXCEPTION
WHEN OTHERS THEN
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.err','Error in pruning fm log '||SQLERRM||', '||SQLCODE);
END;
 
PROCEDURE prune_server_monitor( intervaldays IN number ) AS
prunedate date;
counter number:=0;
TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;
rowid_tab_v rowid_tab;
rows_processed number :=0;
BEGIN
SELECT activevalue INTO ORACLE_SID
FROM ctm_config_table
WHERE sectionname='database' and propertyname='dbname';
IF ( intervaldays != -1 ) THEN
prunedate:=to_char(sysdate-intervaldays,'MM/DD/YYYY HH24:MI:SS');
dbms_output.put_line('Prune data before '||prunedate);
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_servermonitor.log','Starting prune server monitor table at '||sysdate);
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_servermonitor.log','Prune server monitor table before '||prunedate);
 
counter:=0;
rows_processed :=0;
LOOP
counter:=0;
FOR i IN (select /*+ INDEX_FFS(SERVER_MONITOR_TIME_I) */ rowid from SERVER_MONITOR_TABLE
where CollectionTime < prunedate and rownum <= rows_to_be_deleted) LOOP
counter := counter + 1;
rowid_tab_v(counter) := i.rowid;
 
IF counter = rows_to_be_deleted THEN
commit;
FORALL rid IN 1 .. counter
DELETE FROM SERVER_MONITOR_TABLE
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
END IF;
END LOOP ;
IF counter < rows_to_be_deleted THEN
IF counter > 0 THEN
FORALL rid IN 1 .. counter
DELETE FROM SERVER_MONITOR_TABLE
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
counter := 0;
END IF;
exit;
END IF;
END LOOP;
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_servermonitor.log','Deleted '||rows_processed||' rows from SERVER_MONITOR_TABLE at '|| sysdate);
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_servermonitor.log','Finish prune server monitor table at '||sysdate);
ELSE
dbms_output.put_line('User choose to keep all data, no prune at this time');
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_servermonitor.log','User choose to keep all data, no prune');
END IF;
EXCEPTION
WHEN OTHERS THEN
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_servermonitor.err','Error in prune server monitor table '||SQLERRM||', '||SQLCODE);
END;
 
 
PROCEDURE prune_admin_job_table( intervaldays IN number ) AS
prunedate date;
counter number:=0;
TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;
rowid_tab_v rowid_tab;
rows_processed number :=0;
BEGIN
SELECT activevalue INTO ORACLE_SID
FROM ctm_config_table
WHERE sectionname='database' and propertyname='dbname';
IF ( intervaldays != -1 ) THEN
prunedate:=to_char(sysdate-intervaldays,'MM/DD/YYYY HH24:MI:SS');
dbms_output.put_line('Prune data before '||prunedate);
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Starting prune admin job table at '||sysdate);
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Prune admin job table before '||prunedate);
 
counter:=0;
rows_processed :=0;
LOOP
counter:=0;
FOR i IN (select /*+ INDEX_FFS(admin_job_ScheduledTIme_index) */ rowid from ADMIN_JOB_TABLE
where ScheduledTIme < prunedate and rownum <= rows_to_be_deleted) LOOP
counter := counter + 1;
rowid_tab_v(counter) := i.rowid;
 
IF counter = rows_to_be_deleted THEN
commit;
FORALL rid IN 1 .. counter
DELETE FROM ADMIN_JOB_TABLE
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
END IF;
END LOOP ;
IF counter < rows_to_be_deleted THEN
IF counter > 0 THEN
FORALL rid IN 1 .. counter
DELETE FROM ADMIN_JOB_TABLE
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
counter := 0;
END IF;
exit;
END IF;
END LOOP;
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Deleted '||rows_processed||' rows from ADMIN_JOB_TABLE at '|| sysdate);
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Finish prune admin job table at '||sysdate);
ELSE
dbms_output.put_line('User choose to keep all data, no prune at this time');
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','User choose to keep all data, no prune');
END IF;
EXCEPTION
WHEN OTHERS THEN
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.err','Error in prune admin job table '||SQLERRM||', '||SQLCODE);
END;
 
 
 
END prune;
/
 

3.1.5 中規模、大規模、およびハイエンド構成用の prune_package

prune_package 」の項にある項目「prune_package for Medium, Large, and High-End Configurations」は、次の情報で置き換えます。

rem Pruning Package and change prune schedule package
CREATE OR REPLACE PACKAGE Prune AS
 
row_count NUMBER := 0 ;
rows_to_be_deleted NUMBER := 100000 ;
ORACLE_SID varchar2(40);
 
PROCEDURE Prune_log(p_table_name in varchar2, intervaldays in number, log_file in varchar2);
PROCEDURE CheckGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE );
PROCEDURE PruneGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE );
PROCEDURE prune_auditlog( intervaldays IN Number);
PROCEDURE prune_errorlog(intervaldays IN number) ;
PROCEDURE prune_fm( intervaldays IN number);
PROCEDURE prune_server_monitor( intervaldays IN number);
PROCEDURE prune_admin_job_table( intervaldays IN number);
 
END Prune ;
/
show errors;
 
CREATE OR REPLACE PACKAGE BODY Prune AS
PROCEDURE Prune_log(p_table_name in varchar2, intervaldays in number, log_file in varchar2)
AS
partition_to_be_pruned varchar2(9);
cursor_handle integer;
cursor_handle_1 integer;
statement_txt varchar2(500);
statement_txt_1 varchar2(500);
rows_processed number := 0;
rows_processed_1 number :=0;
 
prunedate date;
deleted_rows number;
ORACLE_SID varchar2(20);
err_file varchar2(500);
 
BEGIN
err_file := 'prune_'||p_table_name||'.err';
SELECT activevalue INTO ORACLE_SID
FROM ctm_config_table
WHERE sectionname='database' and propertyname='dbname';
IF ( intervaldays != -1) THEN
prunedate:= sysdate-intervaldays;
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump',log_file,'prune data in '||p_table_name||' before '||prunedate);
prunedate:= trunc(sysdate-intervaldays);
 
FOR rec IN ( SELECT table_name, index_name,column_name FROM user_ind_columns
WHERE table_name = p_table_name
AND column_name like '%TIME%'
AND column_position=1)
LOOP
/* get the counter of rows to be pruned */
 
cursor_handle_1 := DBMS_SQL.OPEN_CURSOR;
statement_txt_1 := 'SELECT /*+ INDEX_FFS('||rec.index_name||') */ count(rowid) FROM '||rec.table_name||' where '||rec.column_name ||'<= '||''''||prunedate||'''';
--DBMS_OUTPUT.PUT_LINE(statement_txt_1);
DBMS_SQL.PARSE(cursor_handle_1, statement_txt_1, DBMS_SQL.NATIVE);
 
DBMS_SQL.DEFINE_COLUMN ( cursor_handle_1,1, deleted_rows);
rows_processed := DBMS_SQL.EXECUTE(cursor_handle_1);
IF DBMS_SQL.FETCH_ROWS(cursor_handle_1)=0 then
EXIT;
ELSE
DBMS_SQL.COLUMN_VALUE (cursor_handle_1,1, deleted_rows);
deleted_rows :=nvl(deleted_rows,0);
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump',log_file,'pruned '||deleted_rows||' from '||rec.table_name);
END IF;
DBMS_SQL.CLOSE_CURSOR (cursor_handle_1);
 
/* prune partitions before the prune intervaldays */
FOR i IN ( SELECT partition_name FROM user_segments
Where to_date(substr(partition_name,2,9)) <= prunedate
AND segment_name=rec.table_name)
LOOP
/* drop all the old partition */
--dbms_output.put_line(i.partition_name);
 
cursor_handle := DBMS_SQL.OPEN_CURSOR;
statement_txt := 'alter table '||rec.table_name ||' drop partition '||i.partition_name;
DBMS_SQL.PARSE(cursor_handle, statement_txt, DBMS_SQL.NATIVE);
rows_processed := DBMS_SQL.EXECUTE(cursor_handle);
DBMS_SQL.CLOSE_CURSOR (cursor_handle);
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump',log_file,'dropped partition '||i.partition_name||' from '||rec.table_name);
END LOOP;
END LOOP;
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump',log_file,'prune '||p_table_name||' successfully finished at '||sysdate);
END IF;
EXCEPTION
WHEN OTHERS THEN
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump',err_file,'Error in pruning '||p_table_name||': '||SQLERRM||', '||SQLCODE);
END prune_log;
 
PROCEDURE CheckGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE ) AS
CURSOR IsThisAGNE IS
SELECT gne.gneid
FROM gne_table gne, ne_info_Table ne
WHERE gne.gnesysid = ne.nesysid AND
ne.nesysid = iv_NESysId ;
 
CURSOR GneChildren ( iv_GNEId ne_info_table.gneid%TYPE ) IS
SELECT ne.nesysid
FROM ne_info_table ne
WHERE ne.gneid = iv_gneId AND
ne.nesysid <> iv_NESysId ;
 
lv_GNEId ne_info_table.GNEId%TYPE := NULL ;
BEGIN
OPEN IsThisAGNE ;
FETCH IsThisAGNE INTO lv_GNEId ;
IF IsThisAGNE%FOUND THEN
FOR rec IN GneChildren(lv_GNEId) LOOP
dbms_output.put_line('NE : '||rec.nesysid);
END LOOP ;
END IF ;
CLOSE IsThisAGNE ;
END CheckGne ;
 
PROCEDURE PruneGNE ( iv_NESysId IN ne_info_table.NESysId%TYPE ) AS
CURSOR IsThisAGNE IS
SELECT gne.gneid
FROM gne_table gne
WHERE gne.gnesysid = iv_NESysid ;
 
CURSOR GneChildren ( iv_GNEId ne_info_table.gneid%TYPE ) IS
SELECT ne.nesysid
FROM ne_info_table ne
WHERE ne.gneid = iv_gneId AND
ne.nesysid <> iv_NESysid ;
 
lv_GNEId ne_info_table.GNEId%TYPE := NULL ;
lv_NESysId ne_info_table.NESysId%TYPE := NULL ;
BEGIN
OPEN IsThisAGNE ;
FETCH IsThisAGNE INTO lv_GNEId ;
IF IsThisAGNE%FOUND THEN
OPEN GneChildren (lv_GNEId ) ;
FETCH GneChildren INTO lv_NESysId ;
IF GNEChildren%NOTFOUND THEN
DELETE gne_table
WHERE GNEId = lv_GNEId ;
COMMIT ;
END IF ;
END IF ;
CLOSE IsThisAGNE ;
END PruneGNE ;
 
PROCEDURE prune_auditlog( intervaldays IN number ) AS
prunedate date;
counter number:=0;
rows_processed number:=0;
TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;
rowid_tab_v rowid_tab;
BEGIN
SELECT activevalue INTO ORACLE_SID
FROM ctm_config_table
WHERE sectionname='database' and propertyname='dbname';
 
IF ( intervaldays != -1 ) THEN
prunedate:=sysdate-intervaldays;
dbms_output.put_line('Prune data before '||prunedate);
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_auditlog.log','Starting prune auditlog at '||sysdate);
prune_log ('TRANSACTION_LOG_TABLE', intervaldays, 'prune_auditlog.log');
ELSE
dbms_output.put_line('user choose to keep all audit log data, so no prune at this time');
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_auditlog.log','User choose to keep all data, no prune');
END IF;
EXCEPTION
WHEN OTHERS THEN
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_auditlog.err','Error in pruning audit log '||SQLERRM||', '||SQLCODE);
END;
 
PROCEDURE prune_errorlog(intervaldays IN number) AS
BEGIN
SELECT activevalue INTO ORACLE_SID
FROM ctm_config_table
WHERE sectionname='database' and propertyname='dbname';
 
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_errorlog.log','Starting prune errorlog at '||sysdate);
prune_log('ERROR_LOG_TABLE', intervaldays, 'prune_errorlog.log');
END;
PROCEDURE prune_fm(intervaldays IN number ) AS
prunedate date;
counter number :=0;
TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;
rowid_tab_v rowid_tab;
rows_processed number :=0;
BEGIN
SELECT activevalue INTO ORACLE_SID
FROM ctm_config_table
WHERE sectionname='database' and propertyname='dbname';
IF ( intervaldays != -1) THEN
prunedate:= sysdate-intervaldays;
dbms_output.put_line('Prune data before '||prunedate);
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.log','Starting prune fm at '||sysdate);
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.log','Prune FM data before '||prunedate);
counter:=0;
rows_processed :=0;
LOOP
counter:=0;
FOR i IN (select /*+ INDEX_FFS(ALARM_EVENT_TIME_INDEX) */ rowid from alarm_event_table
where AlarmEventTimeStamp < prunedate and rownum <= rows_to_be_deleted) LOOP
counter := counter + 1;
rowid_tab_v(counter) := i.rowid;
 
IF counter = rows_to_be_deleted THEN
FORALL rid IN 1 .. counter
DELETE FROM alarm_event_table
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
END IF;
END LOOP ;
IF counter < rows_to_be_deleted THEN
IF counter > 0 THEN
FORALL rid IN 1 .. counter
DELETE FROM alarm_event_table
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
counter := 0;
END IF;
exit;
END IF;
END LOOP;
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.log','Deleted '||rows_processed||' rows from alarm_event_table at '|| sysdate);
 
counter:=0;
rows_processed:=0;
LOOP
counter:=0;
FOR i IN (select /*+ INDEX_FFS(ACTIVE_ALARM_TIME_INDEX) */ rowid from ACTIVE_ALARM_TABLE
where ActiveAlarmTimeStamp < prunedate
and alarmstatus = 1
and ackindex <> 0
and rownum <= rows_to_be_deleted) LOOP
counter := counter + 1;
rowid_tab_v(counter) := i.rowid;
IF counter = rows_to_be_deleted THEN
FORALL rid IN 1 .. counter
DELETE FROM ACTIVE_ALARM_TABLE
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
END IF;
END LOOP ;
IF counter < rows_to_be_deleted THEN
IF counter > 0 THEN
FORALL rid IN 1 .. counter
DELETE FROM ACTIVE_ALARM_TABLE
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
counter := 0;
END IF;
exit;
END IF;
END LOOP;
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.log','Deleted '||rows_processed||' rows from active_alarm_table at '||sysdate);
prune_log ('SYSLOG_MESSAGE_TABLE', intervaldays, 'prune_auditlog.log');
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.log','Finish prune FM at '||sysdate);
ELSE
dbms_output.put_line('User choose to keep all data, no prune at this time');
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.log','User choose to keep all data, no prune');
END IF;
EXCEPTION
WHEN OTHERS THEN
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_fm.err','Error in pruning fm log '||SQLERRM||', '||SQLCODE);
END;
 
PROCEDURE prune_server_monitor( intervaldays IN number ) AS
prunedate date;
counter number:=0;
TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;
rowid_tab_v rowid_tab;
rows_processed number :=0;
BEGIN
SELECT activevalue INTO ORACLE_SID
FROM ctm_config_table
WHERE sectionname='database' and propertyname='dbname';
IF ( intervaldays != -1 ) THEN
prunedate:=to_char(sysdate-intervaldays,'MM/DD/YYYY HH24:MI:SS');
dbms_output.put_line('Prune data before '||prunedate);
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_servermonitor.log','Starting prune server monitor table at '||sysdate);
prune_log('SERVER_MONITOR_TABLE', intervaldays, 'prune_servermonitor.log');
 
ELSE
dbms_output.put_line('User choose to keep all data, no prune at this time');
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_servermonitor.log','User choose to keep all data, no prune');
END IF;
EXCEPTION
WHEN OTHERS THEN
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_servermonitor.err','Error in prune server monitor table '||SQLERRM||', '||SQLCODE);
END;
 
 
PROCEDURE prune_admin_job_table( intervaldays IN number ) AS
prunedate date;
counter number:=0;
TYPE rowid_tab is TABLE OF ROWID INDEX BY BINARY_INTEGER;
rowid_tab_v rowid_tab;
rows_processed number :=0;
BEGIN
SELECT activevalue INTO ORACLE_SID
FROM ctm_config_table
WHERE sectionname='database' and propertyname='dbname';
IF ( intervaldays != -1 ) THEN
prunedate:=to_char(sysdate-intervaldays,'MM/DD/YYYY HH24:MI:SS');
dbms_output.put_line('Prune data before '||prunedate);
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Starting prune admin job table at '||sysdate);
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Prune admin job table before '||prunedate) ;
counter:=0;
rows_processed :=0;
LOOP
counter:=0;
FOR i IN (select /*+ INDEX_FFS(admin_job_ScheduledTIme_index) */ rowid from ADMIN_JOB_TABLE
where ScheduledTIme < prunedate and rownum <= rows_to_be_deleted) LOOP
counter := counter + 1;
rowid_tab_v(counter) := i.rowid;
 
IF counter = rows_to_be_deleted THEN
commit;
FORALL rid IN 1 .. counter
DELETE FROM ADMIN_JOB_TABLE
 
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
END IF;
END LOOP ;
IF counter < rows_to_be_deleted THEN
IF counter > 0 THEN
FORALL rid IN 1 .. counter
DELETE FROM ADMIN_JOB_TABLE
WHERE rowid = rowid_tab_v(rid);
COMMIT;
rows_processed:=rows_processed+counter;
counter := 0;
END IF;
exit;
END IF;
END LOOP;
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Deleted '||rows_processed||' rows from ADMIN_JOB_TABLE at '|| sysdate);
append_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','Finish prune admin job table at '||sysdate);
 
ELSE
dbms_output.put_line('User choose to keep all data, no prune at this time');
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.log','User choose to keep all data, no prune');
END IF;
EXCEPTION
WHEN OTHERS THEN
create_file('/oraclesw9i/admin/'||ORACLE_SID||'/udump','prune_adminjobtable.err','Error in prune admin job table '||SQLERRM||', '||SQLCODE);
END;
 
 
 
END prune;
/