注意,下面两个查询中的values原始值为微秒,即1/1000000秒。

查看当前时间点 DB TIME 值:

select value/1000000/60 "DBTIME_NOW(Min)"
    from v$SYS_TIME_MODEL 
    where STAT_NAME = 'DB time';

查看历史 DB TIME 列表:

set pages 800;
alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
SELECT *
  FROM ( SELECT A.INSTANCE_NUMBER,
               A.SNAP_ID,
               B.BEGIN_INTERVAL_TIME + 0 BEGIN_TIME,
               B.END_INTERVAL_TIME + 0 END_TIME,
               ROUND(VALUE - LAG( VALUE, 1 , '0')
                     OVER(ORDER BY A.INSTANCE_NUMBER, A.SNAP_ID)) "DB TIME(Min)"
          FROM (SELECT B.SNAP_ID,
                       INSTANCE_NUMBER,
                       SUM(VALUE ) / 1000000 / 60 VALUE
                  FROM DBA_HIST_SYS_TIME_MODEL B
                 WHERE B.DBID = (SELECT DBID FROM V$DATABASE)
                   AND UPPER (B.STAT_NAME) IN UPPER(('DB TIME' ))
                 GROUP BY B.SNAP_ID, INSTANCE_NUMBER) A,
               DBA_HIST_SNAPSHOT B
         WHERE A.SNAP_ID = B.SNAP_ID
           AND B.DBID = (SELECT DBID FROM V$DATABASE)
           AND B.INSTANCE_NUMBER = A.INSTANCE_NUMBER)
 WHERE TO_CHAR(BEGIN_TIME, 'YYYY-MM-DD') = TO_CHAR(SYSDATE , 'YYYY-MM-DD')
 ORDER BY BEGIN_TIME;
-- By 许望(RHCA、OCM、VCP)
最后修改:2020 年 01 月 16 日 10 : 25 AM
如果觉得我的文章对你有用,请随意赞赏