分享一个sql，oem里面看长时间操作的脚本_oracle oem 监控慢查询并展示-优快云博客

分享一个sql，oem里面看长时间操作的脚本：

/* OracleOEM */当前操作
SELECT DECODE(TARGET_DESC,
              NULL,
              DECODE(TARGET,
                     NULL,
                     OPNAME,
                     CONCAT(OPNAME, CONCAT(' - ', TARGET))),
              DECODE(TARGET,
                     NULL,
                     CONCAT(OPNAME, CONCAT(' : ', TARGET_DESC)),
                     CONCAT(OPNAME,
                            CONCAT(' : ',
                                   CONCAT(TARGET_DESC, CONCAT(' - ', TARGET)))))) 当前操作,
       SOFAR 已处理,
       TOTALWORK 总共需处理,
       UNITS,
       START_TIME,
       TO_CHAR(ELAPSED_SECONDS, '99999990.00') "已经耗时(秒)",
       DECODE(SOFAR,
              0,
              0,
              ROUND(ELAPSED_SECONDS * (TOTALWORK - SOFAR) / SOFAR)) "剩余时间(秒)"
FROM V$SESSION_LONGOPS
WHERE SID = &sid
   AND SERIAL# = &serial
   AND SOFAR < TOTALWORK ;

/* OracleOEM */历史操作
SELECT DECODE(TARGET_DESC,
              NULL,
              DECODE(TARGET,
                     NULL,
                     OPNAME,
                     CONCAT(OPNAME, CONCAT(' - ', TARGET))),
              DECODE(TARGET,
                     NULL,
                     CONCAT(OPNAME, CONCAT(' : ', TARGET_DESC)),
                     CONCAT(OPNAME,
                            CONCAT(' : ',
                                  CONCAT(TARGET_DESC, CONCAT(' - ', TARGET)))))),
       SOFAR,
       TOTALWORK,
       UNITS,
       START_TIME,
       TO_CHAR(ELAPSED_SECONDS,
                '99999990.00'),
       ELAPSED_SECONDS
FROM V$SESSION_LONGOPS
WHERE SID = &sid
   AND SERIAL# = &serial
   AND SOFAR >= TOTALWORK

小荷(34120993) 00:15:06
你先要找到你当前运行sql的sid和serial#
小荷(34120993) 00:15:26
把这2个号码写到脚本里面去
小荷(34120993) 00:15:37
WHERE SID = &sid
AND SERIAL# = &serial
小荷(34120993) 00:15:42
就是这里
椰子(249217475) 00:16:09
sid是数据库实例名把 serial是啥？
小荷(34120993) 00:16:18
运行这个脚本，就能看到该sql的运行剩余时间了
小荷(34120993) 00:16:32
sid不是实例名
椰子(249217475) 00:17:08

小荷(34120993) 00:16:42
是sesssion id
小荷(34120993) 00:17:00
你desc v$session就能看到它了
椰子(249217475) 00:17:36
哦
椰子(249217475) 00:17:41
那serial呢

小荷(34120993) 00:17:48
是session的序列号
椰子(249217475) 00:18:44
哦可是值从哪里看？

小荷(34120993) 00:18:57
select * from v$session;

小荷(34120993) 00:17:28
Connected to Oracle9i Enterprise Edition Release 9.2.0.1.0
Connected as hejianmin

SQL> desc v$session;
Name                    Type         Nullable Default Comments
----------------------- ------------ -------- ------- --------
SADDR                   RAW(4)       Y
SID                     NUMBER       Y
SERIAL#                 NUMBER       Y

。。。。。。。。。。。。。。