cmd查看oracle表信息,总结：用命令行实现OEM的查看信息

在windows的界面下，我们可以很容易的利用图形化的界面OEM(Oracle Enterprise Manager)看查看每个登陆会话的情况，那么当离开了windows的图形界面，在unix环境下，我们不得不利用命令行来查询相关信息。

以下是我总结的几个用命令行实现oem的查看信息：

(1)kill session：

在命令行通过ps -ef |grep XXXX.sh，获得进程号后，kill －9 进程号，有的时候会kill不干净，在数据库中还存在该进程。可以试试以下的办法：

1、ps -ef |grep xxx.sh 获得该脚本的进程号(假设进程号为111)

2、ps -ef |grep sqlplus 获得该脚本的sqlplus的进程号(一般来说sqlplus的进程号比脚本的进程号大1，我们假设该脚本的sqlplus进程号为112)

3、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 */

AND A.status=’ACTIVE’

ORDER BY PROCESS,a.MACHINE,a.PROGRAM;

我们在获得该sid和serial＃的时候，还可以看看它的sql_text，验证一下是不是我们当前在执行的sql。

4、根据刚刚查到的sid和serial＃ ，执行：alter system kill session ‘sid,serial#’ ;

(2)按照CPU等资源的消耗程度排序：

SELECT aa.sid AS “sid”,

aa.serial# AS “SERIAL#”,

aa.VALUE AS “CPU”,

bb.VALUE AS “内存-PGA”,

cc.VALUE “I/O-Phy-read”

FROM (SELECT c.sid, c.serial#, a.NAME, b.VALUE

FROM v$statname a, v$sesstat b, v$session c

WHERE NAME = ‘CPU used by this session’

AND a.statistic# = b.statistic#

AND c.sid = b.sid) aa,

(SELECT c.sid, c.serial#, a.NAME, b.VALUE

FROM v$statname a, v$sesstat b, v$session c

WHERE NAME = ‘session pga memory’

AND a.statistic# = b.statistic#

AND c.sid = b.sid) bb,

(SELECT c.sid, c.serial#, a.NAME, b.VALUE

FROM v$statname a, v$sesstat b, v$session c

WHERE NAME = ‘physical reads’

AND a.statistic# = b.statistic#

AND c.sid = b.sid) cc

WHERE aa.sid = bb.sid

AND aa.sid = cc.sid

AND aa.serial# = bb.serial#

AND aa.serial# = cc.serial#

ORDER BY 3 DESC, 4 DESC, 5 DESC;

(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;

(4)长时间操作：

注：sid和serial＃可以根据kill session中介绍的方法寻找。

/* 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 = 13 /*刚刚记下的sid*/

AND SERIAL# = 15 /*刚刚记下的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 = 9

AND SERIAL# = 4

AND SOFAR >= TOTALWORK