Oracle檢視每小時日誌切換量指令碼

--
-- Show the Number of Redo Log Switches Per Hour
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
SELECT to_char(first_time, 'yyyy - mm - dd') aday,
           to_char(first_time, 'hh24') hour,
           count(*) total
FROM   v$log_history
WHERE  thread#=&EnterThreadId
GROUP BY to_char(first_time, 'yyyy - mm - dd'),
              to_char(first_time, 'hh24')
ORDER BY to_char(first_time, 'yyyy - mm - dd'),
              to_char(first_time, 'hh24') asc
/





這裡有很多有意思的指令碼。

PS.檢視日誌切換時間的指令碼：

方法一

alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS";

select * from v$log　a　where a.THREAD# = 1 ;

set line 200

select b.SEQUENCE#,

       b.FIRST_TIME,

       a.SEQUENCE#,

       a.FIRST_TIME,

       round(((a.FIRST_TIME - b.FIRST_TIME) * 24) * 60, 2)

  from v$log_history a, v$log_history b

 where a.SEQUENCE# = b.SEQUENCE# + 1

   and b.THREAD# = 1

 order by a.SEQUENCE# desc;

方法二

alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS";

set line 200

select sequence#,

       first_time,

       nexttime,

       round(((first_time - nexttime) * 24) * 60, 2) diff

  from (select sequence#,

               first_time,

               lag(first_time) over(order by sequence#) nexttime

          from v$log_history

         where thread# = 1)

 order by sequence# desc;