q_scn_by_hour

admin

查询每个小时内scn的最大值和增长量

1. WITH log_data
   
2. AS (
   
3.         SELECT TO_CHAR(FIRST_TIME, 'yyyymmdd hh24') AS day_time
   
4.                 ,MAX(FIRST_CHANGE#) AS max_scn#
   
5.         FROM v$log_history
   
6. --        WHERE  FIRST_TIME > TRUNC(SYSDATE - 7)
   
7.         GROUP BY TO_CHAR(FIRST_TIME, 'yyyymmdd hh24')
   
8.         )
   
9. SELECT day_time
   
10.         ,max_scn#
    
11.         ,max_scn# - LAG(max_scn#, 1, 0) OVER (
    
12.                 ORDER BY day_time
    
13.                 ) AS scn_change
    
14. FROM log_data
    
15. ORDER BY day_time;

复制代码