系统监测情况

--*cpu*使用情况 SELECT /*cpu*/ u.SID, u.serial#, s.VALUE cpu, u.username, u.machine,u.program, u.osuser FROM v$session u, v$sesstat s WHERE s.SID = u.SID AND statistic# IN (12) ORDER BY cpu DESC --内存使用情况 SELECT u.SID, u.serial#, s.VALUE mem, u.username, u.machine,u.program, u.osuser FROM v$session u, (SELECT /*mem*/ SID, SUM (VALUE) value FROM v$sesstat WHERE statistic# IN (20,15) GROUP BY SID) s WHERE s.SID = u.SID ORDER BY mem DESC --服务器I/O使用状况 SELECT u.SID, u.serial#, s.VALUE io, u.username, u.machine,u.program, u.osuser FROM v$session u, (SELECT /*io*/ SID, SUM (VALUE) value FROM v$sesstat WHERE statistic# IN (40, 44, 9) GROUP BY SID) s WHERE s.SID = u.SID ORDER BY io DESC) x WHERE ROWNUM < 21 --表空间使用情况 select b.file_id　　文件ID,b.tablespace_name　　表空间,b.file_name　物理文件名,round(b.bytes/(1024*1024)) 总M数,round((b.bytes-sum(nvl(a.bytes,0)))/(1024*1024))　已使用,round(sum(nvl(a.bytes,0))/(1024*1024))　剩余,round(sum(nvl(a.bytes,0))/(b.bytes)*100,2)　剩余百分比 from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.file_id,b.bytes order by b.tablespace_name --表空间碎片情况 select b.file_id　　文件ID,b.tablespace_name　　表空间,b.file_name　物理文件名,a.block_id 块ID,a.blocks 块大小,a.bytes 可用字节数 from dba_free_space a,dba_data_files b where a.file_id=b.file_id order by b.file_id --数据库锁表情况 SELECT OBJECT_ID 目标ID,SESSION_ID 会话ID,SERIAL# 序列号,ORACLE_USERNAME 锁表数据库用户,OS_USER_NAME 锁表系统用户,S.PROCESS 进程号 FROM V$LOCKED_OBJECT A,V$SESSION S WHERE A.SESSION_ID=S.SID --数据库session锁表情况 select sys.v_$session.osuser,sys.v_$session.machine,v$lock.sid, sys.v_$session.serial#, decode(v$lock.type, 'MR', 'Media Recovery', 'RT','Redo Thread', 'UN','User Name', 'TX', 'Transaction', 'TM', 'DML', 'UL', 'PL/SQL User Lock', 'DX', 'Distributed Xaction', 'CF', 'Control File', 'IS', 'Instance State', 'FS', 'File Set', 'IR', 'Instance Recovery', 'ST', 'Disk Space Transaction', 'TS', 'Temp Segment', 'IV', 'Library Cache Invalida-tion', 'LS', 'Log Start or Switch', 'RW', 'Row Wait', 'SQ', 'Sequence Number', 'TE', 'Extend Table', 'TT', 'Temp Table', 'Unknown') LockType, rtrim(object_type) || ' ' || rtrim(owner) || '.' || object_name object_name, decode(lmode, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', 'Unknown') LockMode, decode(request, 0, 'None', 1, 'Null', 2, 'Row-S', 3, 'Row-X', 4, 'Share', 5, 'S/Row-X', 6, 'Exclusive', 'Unknown') RequestMode, ctime, block b from v$lock, all_objects, sys.v_$session where v$Lock.sid > 6 and sys.v_$session.sid = v$lock.sid and v$lock.id1 = all_objects.object_id