ORACLE 查看系统运行情况

曾经终败给现在 2022-07-14 21:14 304阅读 0赞

1，查看系统当前连接会话数

select s.value,s.sid,a.username  
from   
v$sesstat S,v$statname N,v$session A  
where   
n.statistic\#=s.statistic\# and  
name='session pga memory'  
and s.sid=a.sid  
order by s.value;

2，等待最多的用户

SELECT   s.SID, s.username, SUM (a.wait\_time + a.time\_waited) total\_wait\_time  
    FROM v$active\_session\_history a, v$session s  
   WHERE a.sample\_time BETWEEN SYSDATE - 30 / 2880 AND SYSDATE  
GROUP BY s.SID, s.username  
ORDER BY total\_wait\_time DESC;

3，查看死锁信息

SELECT (SELECT username  
          FROM v$session  
         WHERE SID = a.SID) blocker, a.SID, 'is blocking',  
       (SELECT username  
          FROM v$session  
         WHERE SID = b.SID) blockee, b.SID  
  FROM v$lock a, v$lock b  
 WHERE a.BLOCK = 1 AND b.request > 0 AND a.id1 = b.id1 AND a.id2 = b.id2;

4，找使用CPU多的用户session

select a.sid,spid,status,substr(a.program,1,40) prog, a.terminal,osuser,value/60/100 value   
from  v$session a,v$process b,v$sesstat c   
where  c.statistic\#=12 and    
       c.sid=a.sid and    
       a.paddr=b.addr    
       order by value desc;

5，监控当前数据库谁在运行什么SQL语句

select osuser, username, sql\_text    
from  v$session a, v$sqltext b   
where  a.sql\_address =b.address order by address, piece;

6，查询表空间的使用情况

SELECT UPPER(F.TABLESPACE\_NAME) "表空间名",  
　　D.TOT\_GROOTTE\_MB "表空间大小(M)",  
　　D.TOT\_GROOTTE\_MB - F.TOTAL\_BYTES "已使用空间(M)",  
　　TO\_CHAR(ROUND((D.TOT\_GROOTTE\_MB - F.TOTAL\_BYTES) / D.TOT\_GROOTTE\_MB \* 100,2),'990.99') || '%' "使用比",  
　　F.TOTAL\_BYTES "空闲空间(M)",  
　　F.MAX\_BYTES "最大块(M)"  
　　FROM (SELECT TABLESPACE\_NAME,  
　　ROUND(SUM(BYTES) / (1024 \* 1024), 2) TOTAL\_BYTES,  
　　ROUND(MAX(BYTES) / (1024 \* 1024), 2) MAX\_BYTES  
　　FROM SYS.DBA\_FREE\_SPACE  
　　GROUP BY TABLESPACE\_NAME) F,  
　　(SELECT DD.TABLESPACE\_NAME,  
　　 ROUND(SUM(DD.BYTES) / (1024 \* 1024), 2) TOT\_GROOTTE\_MB  
　　FROM SYS.DBA\_DATA\_FILES DD  
　　GROUP BY DD.TABLESPACE\_NAME) D  
　　WHERE D.TABLESPACE\_NAME = F.TABLESPACE\_NAME  
　　ORDER BY 1;

7，查询表空间总容量

select tablespace\_name, sum(bytes) / 1024 / 1024 as MB  
　　from dba\_data\_files  
　　group by tablespace\_name;

8，查看表空间下的所有表

select table\_name from all\_tables where TABLESPACE\_NAME='TS\_SJXT'    //表空间要大写

9，查看表所在的表空间

select \* from user\_tables where table\_name= 'ORBIT\_IMAGE'    / /表名要大写