关于进程、hang以及表空间的利用率的管理

1.进程管理

Step1 : 获取进程号

ps -ef |grep XXXX.sh

Step2: Kill 进程

kill －9 进程号

Step 3:  检查原始需清理的进程是否被清理干净。否，则进行以下几步：

1. ps -ef |grep xxx.sh 获得该脚本的进程号
2. ps -ef |grep sqlplus 获得该脚本的sqlplus的进程号
3. 将获取到的sqlplus进程号号代入以下脚本：

SELECT a.SID, a.SERIAL#, a.MACHINE, a.MODULE, b.SQL_TEXT
  FROM v$session a, v$sqlarea b
 WHERE b.ADDRESS = a.SQL_ADDRESS
   AND process = ’ &unix_process_id’ /*此处的unix_process_id为刚刚查到的sqlplus进程号，如112 */
 ORDER BY PROCESS, a.MACHINE, a.PROGRAM;

 4. 根据上面脚本跑出的a.SID, a.SERIAL#， 使用以下脚本进行kill

alter system kill session ‘sid,serial#’ ;

2.hang进程 的管理

从进程管理中获取已经跑出的a.SID, a.SERIAL#。代入以下脚本

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 = 13 /*sid*/
   AND SERIAL# = 15 /*serial＃*/
   AND SOFAR < TOTALWORK;

3.表空间的利用率的管理

SELECT d.status "Status",
       d.tablespace_name "Name",
       d.contents "Type",
       d.extent_management "Extent Management",
       to_char(nvl(a.bytes / 1024 / 1024, 0), ‘99, 999, 990.900’) "Size (M)",
       to_char(nvl(a.bytes – nvl(f.bytes, 0), 0) / 1024 / 1024,
               ‘99999999.999’) || ‘ / ’ ||
       to_char(nvl(a.bytes / 1024 / 1024, 0), ‘99999999.999’) "Used (M)",
       to_char(nvl((a.bytes – nvl(f.bytes, 0)) / a.bytes * 100, 0),
               ‘990.00’) "Used %"
  FROM sys.dba_tablespaces d,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_free_space
         GROUP BY tablespace_name) f
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = f.tablespace_name(+)
   AND NOT
        (d.extent_management LIKE ‘LOCAL’ AND d.contents LIKE ‘TEMPORARY’)
UNION ALL
SELECT d.status "Status",
       d.tablespace_name "Name",
       d.contents "Type",
       d.extent_management "Extent Management",
       to_char(nvl(a.bytes / 1024 / 1024, 0), ‘99, 999, 990.900’) "Size (M)",
       to_char(nvl(t.bytes, 0) / 1024 / 1024, ‘99999999.999’) || ‘ / ’ ||
       to_char(nvl(a.bytes / 1024 / 1024, 0), ‘99999999.999’) "Used (M)",
       to_char(nvl(t.bytes / a.bytes * 100, 0), ‘990.00’) "Used %"
  FROM sys.dba_tablespaces d,
       (SELECT tablespace_name, SUM(bytes) bytes
          FROM dba_temp_files
         GROUP BY tablespace_name) a,
       (SELECT tablespace_name, SUM(bytes_cached) bytes
          FROM v$temp_extent_pool
         GROUP BY tablespace_name) t
 WHERE d.tablespace_name = a.tablespace_name(+)
   AND d.tablespace_name = t.tablespace_name(+)
   AND d.extent_management LIKE ‘LOCAL’
   AND d.contents LIKE ‘TEMPORARY’
 ORDER BY "Used %" DESC;