--查看最大游标数
SQL>show parameter open_cursor;
--修改游标数
SQL>alter system set open_cursors=5000;
--查看总进程信息
select count(*) from v$process;
--各库当前session总个数
select s.STATUS, count(*) from v$session s group by s.STATUS;
--各用户当前session活动与非活动数量
select s.USERNAME , s.STATUS , count(*)
from v$session s where machine = 'TOSHIBA-PC'
group by s.USERNAME, s.STATUS order by s.USERNAME, s.STATUS;
--各库当前session执行的sql语句(这个好像是不太对,先留在这里备忘了)
select s.STATUS,s.SQL_ID,s.program, sq.SQL_TEXT
from v$session s, v$sql sq
where s.SQL_ID = sq.SQL_ID
order by s.USERNAME, s.STATUS, sq.SQL_ID;
--指定用户在oracle中的session信息及进程id and s.osuser=''(这个好像是不太对,先留在这里备忘了)
select s.OSUSER, pid, spid, s.SERIAL#,s.SQL_ID from v$session s, v$process p
where machine = 'TOSHIBA-PC'
and p.ADDR = s.PADDR
order by s.USERNAME, s.STATUS, s.SQL_ID;
--根据sql内容查看当前指定sql 执行信息
SELECT sql_id, SQL_TEXT FROM v$sql where SQL_text like '%bm_project_member%';
--根据sql的id查看当前指定sql 内容
SELECT * FROM v$sql where SQL_id = '3azdcqtzm8d29';
其他
--SELECT SQ.SQL_ID, sq.OPTIMIZER_MODE, sq.FIRST_LOAD_TIME,sq.LAST_ACTIVE_TIME,sq.SQL_TEXT
--FROM v$sql sq, v$session s where s.SQL_ID = sq.SQL_ID
--select sid,serial#,username,program,machine,status from v$session where username is not null order by status, username;