oracle 會話 死鎖 執(zhí)行sql 執(zhí)行job的方法

//根據(jù)用戶分組會話
select t.USERNAME,count(*) from v$session t group by t.USERNAME
//查找某一用戶正在執(zhí)行的sql_id值
select * from v$session t where t.USERNAME = 'BUDGET' and t.SQL_ID is not null
//查找對應(yīng)sql_id的對應(yīng)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最大連接數(shù)的方法

a、以sysdba身份登陸PL/SQL 或者 Worksheet
b、查詢目前連接數(shù)
show parameter processes;
c、更改系統(tǒng)連接數(shù)
alter system set processes=1000 scope=spfile;
d、創(chuàng)建pfile
create pfile from spfile;
e、重啟Oracle服務(wù)或重啟Oracle服務(wù)器
2、查詢Oracle游標(biāo)使用情況的方法
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;
執(zhí)行這句會提示：ora-00031：標(biāo)記要終止的會話。。。但是其實(shí)執(zhí)行成功了。
如果用pl/sql登陸客戶端那邊會提示：ora-00028:您的會話已經(jīng)被終止。

查看正在執(zhí)行sql進(jìn)度：
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
 
強(qiáng)制停止oracle正在執(zhí)行的job：
 
1、查看所有job;
select * from dba_jobs;
2、查看正在運(yùn)行的job;
select * from dba_jobs_running;
3、根據(jù)sid查出對應(yīng)的session;
select SID,SERIAL# from V$Session where SID='SID';
4、kill對應(yīng)的session;
alter system kill session 'SID,SERIAL';
5、將job置為broken;
exec dbms_job.broken('JOB',true);
6、sysdba用戶權(quán)限刪除job;
delete from dba_jobs where JOB='JOB';
---實(shí)在無奈時啟動數(shù)據(jù)庫