--
-- 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
/
http://www.oracle-wiki.net/startsql
https://www.dba-scripts.com/
这里有很多有意思的脚本。
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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29135257/viewspace-2132852/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29135257/viewspace-2132852/