创建目录：

CREATE OR REPLACE DIRECTORY
ALERT_LOG AS
'D:oracleproduct10.2.0adminorclbdump';


GRANT READ, WRITE ON DIRECTORY ALERT_LOG TO PLOG WITH GRANT OPTION;

创建外部表：

CREATE TABLE PLOG.ALERT_PTS
(
TEXT VARCHAR2(1000 BYTE) NULL
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY ALERT_LOG
ACCESS PARAMETERS
( records delimited by newline )
LOCATION (ALERT_LOG:'alert_orcl.log')
)
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

创建关机 开机字符时间信息表：数据通过外部表取得
CREATE TABLE PLOG.RESULT_ALERT_PTS
(
STARTUP_TIME DATE NULL,
TEXT VARCHAR2(1000 BYTE) NULL,
OPEN_FLAG NUMBER NULL
)

程序：

/* Formatted on 2009/10/21 14:04 (Formatter Plus v4.8.6) */
CREATE OR REPLACE PROCEDURE alert_pro_dy(p_starttime date,p_endtime date)
AS
v_open_num NUMBER; --开机次数
v_count_order NUMBER; --每次循环 计数
v_maxtime_r1 DATE; ---父 开机时间(前一次)
v_maxtime_r2 DATE; --子开机时间（后一次）
v_shutdown_time_r1 DATE; --2次开机间隔的最开始停机时间
v_date_time NUMBER; --单次数据库不可用时间
v_downtime_all NUMBER; --总数据库不可用时间
v_first_shutdown_num NUMBER;
v_first_max_downtime DATE;
v_sql1 VARCHAR2 (200);
v_sql_create_sq VARCHAR2 (200);
v_sql_drop_sq VARCHAR2 (200);
v_sql_create_table VARCHAR2 (2000);
v_sql_drop_table VARCHAR2 (200);
v_sql_alter_table_column VARCHAR2 (200);
BEGIN
EXECUTE IMMEDIATE 'alter session set nls_language=''AMERICAN''';

/*删除表：保存关机和开机字符及时间*/
BEGIN
v_sql_drop_table := 'drop table PLOG.result_alert_pts';

EXECUTE IMMEDIATE v_sql_drop_table;
EXCEPTION
WHEN OTHERS
THEN
NULL;
DBMS_OUTPUT.put_line ('drop table execption');
END;

/*创建表：保存关机和开机字符及时间*/
BEGIN
v_sql_create_table :=
'create table result_alert_pts as select '
|| 'to_date(start_time,''yyyy-mm-dd hh24:mi:ss'') startup_time,text from ( '
|| 'select r,text,'
|| 'to_date(start_time,''Dy Mon DD HH24:MI:SS YYYY'') start_time from (select r,text, '
|| 'lag(text,(select case when text=''Completed: ALTER DATABASE OPEN'' then 1 '
|| 'when text like ''Shutting down instance%'' then 1 end from dual)) over (order by r) start_time from ( '
|| 'select rownum r, text from alert_pts '
|| 'where text like ''___ ___ __ __:__:__ 20__'' '
|| 'or text like ''Completed: ALTER DATABASE OPEN%'' or text like ''Shutting down instance%'')) '
|| 'where text like ''Completed: ALTER DATABASE OPEN%'' or text like ''Shutting down instance%'') where startup_time>=p_starttime and tartup_time DBMS_OUTPUT.put_line (v_sql_create_table);

EXECUTE IMMEDIATE v_sql_create_table;
EXCEPTION
WHEN OTHERS
THEN
NULL;
DBMS_OUTPUT.put_line ('create table execption');
END;

/*增加表列：保存关机和开机字符及时间-------给每次开机一个序列号*/
BEGIN
v_sql_alter_table_column :=
'alter table result_alert_pts add(open_flag number)';

EXECUTE IMMEDIATE v_sql_alter_table_column;
EXCEPTION
WHEN OTHERS
THEN
NULL;
DBMS_OUTPUT.put_line ('alter table execption');
END;

/*每次跑程序都先删除序列（ 初始化序列号的值）*/
BEGIN
v_sql_drop_sq := 'DROP SEQUENCE PLOG.ALERT_PTS_S';

EXECUTE IMMEDIATE v_sql_drop_sq;
EXCEPTION
WHEN OTHERS
THEN
NULL;
DBMS_OUTPUT.put_line ('drop sequence execption');
END;

/*创建序列?/
BEGIN
v_sql_create_sq :=
'CREATE SEQUENCE PLOG.ALERT_PTS_S '
|| 'START WITH 1 '
|| 'INCREMENT BY 1 '
|| 'MAXVALUE 999999999999999999999999999 '
|| 'MINVALUE 0 '
|| 'NOCYCLE '
|| 'NOCACHE '
|| 'NOORDER ';

EXECUTE IMMEDIATE v_sql_create_sq;
EXCEPTION
WHEN OTHERS
THEN
NULL;
DBMS_OUTPUT.put_line ('create sequence execption');
END;

DBMS_OUTPUT.put_line (v_sql1);

/*给每次开机一个序列号*/
UPDATE result_alert_pts
SET open_flag = alert_pts_s.NEXTVAL
WHERE text = 'Completed: ALTER DATABASE OPEN';

COMMIT;
v_downtime_all := 0;

/*计算开机次数*/
SELECT COUNT (*)
INTO v_open_num
FROM result_alert_pts
WHERE open_flag IS NOT NULL;

DBMS_OUTPUT.put_line ('OPEN_NUM:' || v_open_num);
/*先计算 2次 开机的时间，再计算期间表示关机状态的 最早的时间点*/
v_open_num := v_open_num - 1;
v_count_order := 1;

FOR x IN 1 .. v_open_num
LOOP
/*循环计算 每次开机的时间 ，开机的唯一标记为 1到 N的 整数 可以通过该规律保证循环唯一性*/
SELECT r1.startup_time, r2.startup_time
INTO v_maxtime_r1, v_maxtime_r2
FROM result_alert_pts r1, result_alert_pts r2
WHERE r1.open_flag = r2.open_flag - 1 AND r1.open_flag = v_count_order;

--DBMS_OUTPUT.put_line ('sql time before r1' || v_count_order);
v_count_order := v_count_order + 1;

--DBMS_OUTPUT.put_line ('sql time after r1' || v_count_order);
/*先计算 2次 开机的时间，再计算期间表示关机状态的 最早的时间点*/
SELECT MIN (startup_time)
INTO v_shutdown_time_r1
FROM result_alert_pts
WHERE startup_time > v_maxtime_r1 AND startup_time < v_maxtime_r2;

SELECT ROUND ((v_maxtime_r2 - v_shutdown_time_r1) * 24 * 60, 0)
INTO v_date_time
FROM DUAL;

DBMS_OUTPUT.put_line ( 'down_time1:'
|| v_downtime_all
|| '-before'
|| v_date_time
);
v_downtime_all := v_downtime_all + v_date_time;
/*DBMS_OUTPUT.put_line ( 'loop:'
|| v_count_order
|| '-v_shutdown_time_r1-'
|| v_shutdown_time_r1
|| '-maxtime_r2-'
|| v_maxtime_r2
|| '-datetime-'
|| v_date_time
);*/
DBMS_OUTPUT.put_line ('down_time2:' || v_downtime_all);
END LOOP;

/*计算 第一次 开机前一个 关机 时间 ，以上循环没有计算到*/
SELECT COUNT (*)
INTO v_first_shutdown_num
FROM result_alert_pts
WHERE startup_time < (SELECT startup_time
FROM result_alert_pts
WHERE open_flag = 1);

IF v_first_shutdown_num > 0
THEN
SELECT MAX (startup_time)
INTO v_first_max_downtime
FROM result_alert_pts
WHERE startup_time < (SELECT startup_time
FROM result_alert_pts
WHERE open_flag = 1);

SELECT startup_time
INTO v_maxtime_r1
FROM result_alert_pts
WHERE open_flag = 1;

SELECT ROUND ((v_maxtime_r1 - v_first_max_downtime) * 24 * 60, 0)
INTO v_date_time
FROM DUAL;

v_downtime_all := v_downtime_all + v_date_time;
DBMS_OUTPUT.put_line ('v_downtime_all:---' || v_downtime_all);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
DBMS_OUTPUT.put_line ('no data');
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('exception others');
NULL;
END;
/