oracle 会话 死锁 执行sql 执行job的方法

//根据用户分组会话
select t.username,count(*) from v$session t group by t.username
//查找某一用户正在执行的sql_id值
select * from v$session t where t.username = 'budget' and t.sql_id is not null
//查找对应sql_id的对应sql语句
select m.sql_text from v$session t , v$sqlarea m where t.username ='budget' and t.sql_id = m.sql_id
//
select * from v$sqlarea t where t.sql_id = 'g92sy7kwt6mrq'
//查询死锁对象
select * from v$locked_object

1、修改oracle最大连接数的方法

a、以sysdba身份登陆pl/sql 或者 worksheet
b、查询目前连接数
show parameter processes;
c、更改系统连接数
alter system set processes=1000 scope=spfile;
d、创建pfile
create pfile from spfile;
e、重启oracle服务或重启oracle服务器
2、查询oracle游标使用情况的方法
select * from v$open_cursor where user_name = 'traffic'；
3、查询oracle会话的方法
select * from v$session

杀掉会话：

方法一：sql> select pid,spid from v$process where addr in (select paddr from v$session where username =’sys’);
pid spid
———- ————
17 13657

ps -ef |grep 13657
kill -9 13657

方法二：
sql> select sid,serial# from v$session where sid in (select distinct sid from v$mystat);
sid serial#

———- ———-
1637 13894

alter system disconnect session ‘1637,13894’ immediate;
执行这句会提示：ora-00031：标记要终止的会话。。。但是其实执行成功了。
如果用pl/sql登陆客户端那边会提示：ora-00028:您的会话已经被终止。

查看正在执行sql进度：
select se.sid,opname,trunc(sofar / totalwork * 100, 2) || ‘%’ as pct_work,elapsed_seconds elapsed,round(elapsed_seconds * (totalwork – sofar) / sofar) remain_time,sql_text
from v$session_longops sl, v$sqlarea sa, v$session se
where sl.sql_hash_value = sa.hash_value and sl.sid = se.sid and sofar != totalwork
order by start_time

select a.tablespace_name “表空间名”,round(total/1024/1024/1024,4) 表空间大小gb,round(free/1024/1024/1024,4) 表空间剩余大小gb,
round((total-free)/1024/1024/1024,4) 表空间使用大小gb,round((total-free)/total,4)*100 “使用率 %”
from (select tablespace_name,sum(bytes) free from dba_free_space
group by tablespace_name ) a,
(select tablespace_name,sum(bytes) total from dba_data_files group by tablespace_name) b
where a.tablespace_name=b.tablespace_name order by 5 desc
 
强制停止oracle正在执行的job：
 
1、查看所有job;
select * from dba_jobs;
2、查看正在运行的job;
select * from dba_jobs_running;
3、根据sid查出对应的session;
select sid,serial# from v$session where sid=’&sid’;
4、kill对应的session;
alter system kill session ‘&sid,&serial’;
5、将job置为broken;
exec dbms_job.broken(‘&job’,true);
6、sysdba用户权限删除job;
delete from dba_jobs where job=’&job’;
—实在无奈时启动数据库