oracle 监控

sqlplus "/as sysdba"  

1、监控当前数据库谁在运行什么SQL语句
SELECT osuser, username, sql_text from v$session a, v$sqltext b     
where a.sql_address =b.address order by address, piece; 

2、当前 sql 语句
select sql_text, users_executing, executions, loads from v$sqlarea;

3、回滚段的争用情况
select name, waits, gets, waits/gets "Ratio" from v$rollstat a, v$rollname b where a.usn = b.usn;

4、监控表空间的 I/O 比例
 select df.tablespace_name name,df.file_name "file",f.phyrds pyr, f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw from v$filestat f, dba_data_files df where f.file# = df.file_id order by df.tablespace_name;

5、监控文件系统的 I/O 比例
 select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name", a.status, a.bytes, b.phyrds, b.phywrts from v$datafile a, v$filestat b where a.file# = b.file#;

6、找使用 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;

7、显示所有数据库对象的类别和大小
select count(name) num_instances ,type ,sum(source_size) source_size,sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size)size_required from dba_object_size group by type order by 2; 

 

检查状态
select saddr,sid,serial#,paddr,username,status from v$session where username is not null
查询用户的连接状态
Select username,sid,serial# from v$session where username='';
2.逐个删除
Alter system kill session'22,1';
3.删除用户
drop user xy1027 cascade;