有用的视图查询搜集

查询锁表人：

select b.OS_USER_NAME,c.object_name
from v$session a, v$locked_object b, dba_objects c
where a.SID = b.session_id and b.OBJECT_ID = c.object_id;

   

select trunc(e.begin_interval_time, 'dd') "date",
       sum(decode(d.instance_number, 1, d.DB_time, 0))/1000000 db1_time,
       sum(decode(d.instance_number, 2, d.DB_time, 0))/1000000 db2_time,
       sum(decode(d.instance_number, 3, d.DB_time, 0))/1000000 db3_time,
       sum(decode(d.instance_number, 4, d.DB_time, 0))/1000000 db4_time,
       sum(decode(d.instance_number, 1, d.DB_CPU, 0))/1000000 db1_cpu,
       sum(decode(d.instance_number, 2, d.DB_CPU, 0))/1000000 db2_cpu,
       sum(decode(d.instance_number, 3, d.DB_CPU, 0))/1000000 db3_cpu,
       sum(decode(d.instance_number, 4, d.DB_CPU, 0))/1000000 db4_cpu
  from (select c.SNAP_ID as begin_snapid,
               --c.SNAP_ID as end_snapid,
               b.INSTANCE_NUMBER,
               (c.DB_time - b.DB_time) as DB_time,
               (c.DB_CPU - b.DB_CPU) as DB_CPU
          from (select A.SNAP_ID,
                       A.INSTANCE_NUMBER,
                       SUM(decode(a.stat_id, 3649082374, a.value, 0)) DB_time,
                       SUM(decode(a.stat_id, 2748282437, a.value, 0)) DB_CPU
                  from sys.wrh$_sys_time_model a
                 GROUP BY A.SNAP_ID, A.INSTANCE_NUMBER) b,
               (select A.SNAP_ID,
                       A.INSTANCE_NUMBER,
                       SUM(decode(a.stat_id, 3649082374, a.value, 0)) DB_time,
                       SUM(decode(a.stat_id, 2748282437, a.value, 0)) DB_CPU
                  from sys.wrh$_sys_time_model a
                 GROUP BY A.SNAP_ID, A.INSTANCE_NUMBER) c
         where b.snap_id  = c.snap_id -1
           and b.INSTANCE_NUMBER = c.INSTANCE_NUMBER) d,
       sys.wrm$_snapshot e
 where d.begin_snapid = e.snap_id
   and d.instance_number = e.instance_number
 group by trunc(e.begin_interval_time, 'dd')
 order by trunc(e.begin_interval_time, 'dd')