1、修改Oracle最大连接数的方法
杀掉会话:
方法一: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';
---实在无奈时启动数据库