一、设置回话和连接时间
Oracle服务器连接数过多会当掉,把连接数过多的客户机网线拔出后,在远程Oracle上依然还会保留此用户的连接数,久久不能释放,上网查了下可以以下面方法解决。
通过profile可以对用户会话进行一定的限制,比如IDLE时间。
将IDLE超过一定时间的会话断开,可以减少数据库端的会话数量,减少资源耗用。
使用这些资源限制特性,需要设置resource_limit为TRUE:
[oracle@test126 udump]$ sqlplus "/ as sysdba" SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 13 07:58:21 2006 Copyright (c) 1982,2005,Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning and Data Mining options SQL> show parameter resource NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ resource_limit boolean TRUE resource_manager_plan string 该参数可以动态修改: SQL> alter system set resource_limit=true; System altered. 数据库缺省的PROFILE设置为: SQL> SELECT * FROM DBA_PROFILES; PROFILE RESOURCE_NAME RESOURCE LIMIT -------------------- -------------------------------- -------- --------------- DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED DEFAULT CPU_PER_SESSION KERNEL UNLIMITED DEFAULT CPU_PER_CALL KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED DEFAULT IDLE_TIME KERNEL UNLIMITED DEFAULT CONNECT_TIME KERNEL UNLIMITED DEFAULT PRIVATE_SGA KERNEL UNLIMITED DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 DEFAULT PASSWORD_LIFE_TIME PASSWORD UNLIMITED PROFILE RESOURCE_NAME RESOURCE LIMIT -------------------- -------------------------------- -------- --------------- DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL DEFAULT PASSWORD_LOCK_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_GRACE_TIME PASSWORD UNLIMITED 16 rows selected. 创建一个允许3分钟IDLE时间的PROFILE: SQL> CREATE PROFILE KILLIDLE LIMIT IDLE_TIME 3; Profile created. 新创建PROFILE的内容: SQL> col limit for a10 SQL> select * from dba_profiles where profile='KILLIDLE'; PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- ---------- KILLIDLE COMPOSITE_LIMIT KERNEL DEFAULT KILLIDLE SESSIONS_PER_USER KERNEL DEFAULT KILLIDLE CPU_PER_SESSION KERNEL DEFAULT KILLIDLE CPU_PER_CALL KERNEL DEFAULT KILLIDLE LOGICAL_READS_PER_SESSION KERNEL DEFAULT KILLIDLE LOGICAL_READS_PER_CALL KERNEL DEFAULT KILLIDLE IDLE_TIME KERNEL 3 KILLIDLE CONNECT_TIME KERNEL DEFAULT KILLIDLE PRIVATE_SGA KERNEL DEFAULT KILLIDLE FAILED_LOGIN_ATTEMPTS PASSWORD DEFAULT KILLIDLE PASSWORD_LIFE_TIME PASSWORD DEFAULT PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- ---------- KILLIDLE PASSWORD_REUSE_TIME PASSWORD DEFAULT KILLIDLE PASSWORD_REUSE_MAX PASSWORD DEFAULT KILLIDLE PASSWORD_VERIFY_FUNCTION PASSWORD DEFAULT KILLIDLE PASSWORD_LOCK_TIME PASSWORD DEFAULT KILLIDLE PASSWORD_GRACE_TIME PASSWORD DEFAULT 16 rows selected. 测试用户: SQL> select username,profile from dba_users where username='EYGLE'; USERNAME PROFILE ------------------------------ -------------------- EYGLE DEFAULT 修改eygle用户的PROFILE使用新建的PROFILE: SQL> alter user eygle profile killidle; User altered. SQL> select username,profile from dba_users where username='EYGLE'; USERNAME PROFILE ------------------------------ -------------------- EYGLE KILLIDLE 进行连接测试: [oracle@test126 admin]$ sqlpluseygle/eygle@eygle SQL*Plus: Release 10.2.0.1.0 - Production on Fri Oct 13 08:07:13 2006 Copyright (c) 1982,Oracle. All rights reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning and Data Mining options SQL> select username,profile from dba_users where username='EYGLE'; USERNAME PROFILE ------------------------------ ------------------------------ EYGLE KILLIDLE 当IDLE超过限制时间时,连接会被断开: SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; TO_CHAR(SYSDATE,'YY ------------------- 2006-10-13 08:08:41 SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual * ERROR at line 1: ORA-02396: exceeded maximum idle time,please connect again
二、创建新的profile
sqlplus /nolog 打开sqlplus
conn sys/orcl@orcl as sysdba 使用具有dba权限得用户登陆oracle show parameter resource_limit 显示资源限定是否开启,value为true是开启,为false是关闭
alter system set resource_limit=true 如果未开启,则使用此命令开启资源限定功能
create profile profileName limit connect_time 60 idle_time 30 创建profile文件,profileName任意起,connect_time设置连接超过多少分钟后强制释放,idle_time设置连续不活动的会话超过多少分钟后强制释放
alter user oracleUser profile profileName 将profile文件作用于指定用户
Oracle session连接数和inactive的问题记录oracle学习 2009-03-10 15:42:37 阅读317 评论0 字号:大中小 订阅 .
从上周起,服务器Oracle数据库出现问题,用不到半天,就会报maxsession(150)的问题,肯定是数据库的会话超过最大数了。
由于服务器跑的是文件传输应用,占用的请求和会话肯定很大,因此用户数不大就已经让oracle的会话数达到最大值。
处理方式不外乎两种:扩大oracle最大session数以及清除inactive会话,当然还有,就是从数据库连接池和程序bug上面下手。
从各处收集了一些查看当前会话的语句,记录一下:
1.select count(*) from v$session;
select count(*) from v$process;
查看当前总会话数和进程数,这两个视图就是跟会话及进程有关的重要视图啦,信息都是从这里面取的。
2.查询那些应用的连接数此时是多少
select b.MACHINE,b.PROGRAM,count(*) from v$process a,v$session b where a.ADDR = b.PADDR and b.USERNAME is not null group by b.MACHINE,b.PROGRAM order by count(*) desc;
3.查询是否有死锁
select * from v$locked_object;
如果查询结果为no rows selected,说明数据库中没有死锁。否则说明数据库中存在死锁。
接下来说明一下会话的状态:
1.active 处于此状态的会话,表示正在执行,处于活动状态。
2.killed 处于此状态的会话,表示出现了错误,正在回滚,当然,也是占用系统资源的。还有一点就是,killed的状态一般会持续较长时间,而且用windows下的工具pl/sql developer来kill掉,是不管用的,要用命令:alter system kill session 'sid,serial#' ;
3.inactive 处于此状态的会话表示不是正在执行的,比如select语句已经完成。我一开始以为,只要是inactive状态的会话,就是该杀,为什么不释放呢。其实,inactive对数据库本身没有什么影响,但是如果程序没有及时commit,那么就会造成占用过多会话。解决inactive的方法最好的就是在oracle中直接设置超时时间,也是有两种方法,区别暂时还不清楚:
1.修改sqlnet.ora文件,新增expire_time=x(单位是分钟)
我的sqlnet.ora位置在D:/oracle/ora92/network/admin
2.通过ALTER PROFILE DEFAULT LIMIT IDLE_TIME 10; 命令修改,记得重启下oracle。
三、修改ORACLE 中的SESSION和PROCESS
会话sessions和进程pocesses的关系 一个process可以有0个、1个或者多个session,一个session也可以存在若干个process中,并行同样是一个session对应一个process,主session是coordinator session,每个parallel process同样会对应数据库里一个单独的session。可以从v$px_session和v$session中验证这点。 连接connects,会话sessions和进程pocesses的关系
每个sql login称为一个连接(connection),而每个连接,可以产生一个或多个会话,如果数据库运行在专用服务器方式,一个会话对应一个服务器进程(process),如果数据库运行在共享服务器方式,一个服务器进程可以为多个会话服务。
Oracle的sessions和processes的数量关系是:sessions=1.1 * processes + 5
下面我们用两种方法修改PROCESS的最大值 一、通过Oracle Enterprise Manager Console在图形化管理器中修改 以系统管理员的身份登入,进入界面 数据库的例程 - 配置 - 一般信息 - 所有初始化参数,修改processes的值
二、在SQLPLUS中修改 以DBA权限登录,修改PROCESS的值(SESSION的值会跟着改);创建pfile;重新启动数据库。输入的SQL命令如下,回显信息省略了 SQL> connect sys/sys as sysdba SQL> alter system set processes=400 scope = spfile; SQL> create pfile from spfile; SQL> shutdown immediate; SQL> startup
四、kill session的方式来终止一个进程
我们知道,在Oracle数据库中,可以通过kill session的方式来终止一个进程,其基本语法结构为:
alter system kill session 'sid,SimSun;font-size:16px;"> 被kill掉的session,状态会被标记为killed,Oracle会在该用户下一次touch时清除该进程.
我们发现当一个session被kill掉以后,该session的paddr被修改,如果有多个session被kill,那么多个session 的paddr都被更改为相同的进程地址:
SQL> select saddr,sid,serial#,paddr,username,status from v$session where username is not null;
SADDR SID SERIAL# PADDR USERNAME STATUS -------- ---------- ---------- -------- ------------------------------ -------- 542E0E6C 11 314 542B70E8 EYGLE INACTIVE 542E5044 18 662 542B6D38 SYS ACTIVE
SQL> alter system kill session '11,314';
System altered.
SADDR SID SERIAL# PADDR USERNAME STATUS -------- ---------- ---------- -------- ------------------------------ -------- 542E0E6C 11 314 542D6BD4 EYGLE KILLED 542E5044 18 662 542B6D38 SYS ACTIVE
SQL> select saddr,SimSun;font-size:16px;"> SADDR SID SERIAL# PADDR USERNAME STATUS -------- ---------- ---------- -------- ------------------------------ -------- 542E0E6C 11 314 542D6BD4 EYGLE KILLED 542E2AA4 14 397 542B7498 EQSP INACTIVE 542E5044 18 662 542B6D38 SYS ACTIVE
SQL> alter system kill session '14,397';
SADDR SID SERIAL# PADDR USERNAME STATUS -------- ---------- ---------- -------- ------------------------------ -------- 542E0E6C 11 314 542D6BD4 EYGLE KILLED 542E2AA4 14 397 542D6BD4 EQSP KILLED 542E5044 18 662 542B6D38 SYS ACTIVE
在这种情况下,很多时候,资源是无法释放的,我们需要查询spid,在操作系统级来kill这些进程.
但是由于此时v$session.paddr已经改变,我们无法通过v$session和v$process关联来获得spid
那还可以怎么办呢?
我们来看一下下面的查询:
SQL> SELECT s.username,s.status, 2 x.ADDR,x.KSLLAPSC,x.KSLLAPSN,x.KSLLASPO,x.KSLLID1R,x.KSLLRTYP, 3 decode(bitand (x.ksuprflg,2),null,1) 4 FROM x$ksupr x,v$session s 5 WHERE s.paddr(+)=x.addr 6 and bitand(ksspaflg,1)!=0;
USERNAME STATUS ADDR KSLLAPSC KSLLAPSN KSLLASPO KSLLID1R KS D ------------------------------ -------- -------- ---------- ---------- ------------ ---------- -- - 542B44A8 0 0 0 ACTIVE 542B4858 1 14 24069 0 1 ACTIVE 542B4C08 26 16 15901 0 1 ACTIVE 542B4FB8 7 46 24083 0 1 ACTIVE 542B5368 12 15 24081 0 1 ACTIVE 542B5718 15 46 24083 0 1 ACTIVE 542B5AC8 79 4 15923 0 1 ACTIVE 542B5E78 50 16 24085 0 1 ACTIVE 542B6228 754 15 24081 0 1 ACTIVE 542B65D8 1 14 24069 0 1 ACTIVE 542B6988 2 30 14571 0 1
USERNAME STATUS ADDR KSLLAPSC KSLLAPSN KSLLASPO KSLLID1R KS D ------------------------------ -------- -------- ---------- ---------- ------------ ---------- -- - SYS ACTIVE 542B6D38 2 8 24071 0 542B70E8 1 15 24081 195 EV 542B7498 1 15 24081 195 EV SYS INACTIVE 542B7848 0 0 0 SYS INACTIVE 542B7BF8 1 15 24081 195 EV
16 rows selected. 我们注意,红字标出的部分就是被Kill掉的进程的进程地址.
简化一点,其实就是如下概念:
SQL> select p.addr from v$process p where pid <> 1 2 minus 3 select s.paddr from v$session s;ADDR -------- 542B70E8 542B7498
Ok,现在我们获得了进程地址,就可以在v$process中找到spid,然后可以使用Kill或者orakill在系统级来杀掉这些进程.
实际上,我猜测:
当在Oracle中kill session以后,Oracle只是简单的把相关session的paddr 指向同一个虚拟地址.
此时v$process和v$session失去关联,进程就此中断.
然后Oracle就等待PMON去清除这些Session.所以通常等待一个被标记为Killed的Session退出需要花费很长的时间.
如果此时被Kill的process,重新尝试执行任务,那么马上会收到进程中断的提示,process退出,此时Oracle会立即启动PMON 来清除该session.这被作为一次异常中断处理
五、oracle profile详解
数据库创建后,系统则存在名为DEFAULT的默认PROFILE,若不做特殊指定,创建用户时用户默认使用的PROFILE就是DEFAULT。
使用profile
一:创建profile
create profile文件名limit failed_login_attempts 3 password_lock_time 2 password_life_time 10 password_grace _time 2; alter user用户名 profile 文件名;
注意:
用户所有拥有的PROFILE中有关资源的限制与resource_limit参数的设置有关,当为TRUE时生效,当为FALSE时(默认值)设置任何值都无效。
resource_limit默认为false.创建profile需要相应的权限,show parameter resource_limit同样需要权限.
SQL> show parameter resource_limit NAME TYPE VALUE -------------------- ----------- ------- resource_limit boolean FALSE
SQL> alter system set resource_limit=true;
二:修改profile
修改profile:alter profile [资源文件名] limit [资源名] unlimited; 如:alter profile default limit failed_login_attempts 100;
三:删除profile
删除PROFILE:drop profile [资源文件名] [CASCADE] ; 若创建的PROFILE已经授权给了某个用户,使用CASCADE级联收回相应的限制,收回限制信息后将以系统默认的PROFILE对该用户进行限制。
已分配的profile,删除时必须加cascade选项。
如果不删除profile,只是取消单个用户的profile:
SQL>alter user dinya profile default;
查询profile
一:查看视图dba_profiles可找出数据库中有哪些PROFILE。
SQL> select distinct profile from dba_profiles; PROFILE -------------------- MONITORING_PROFILE DEFAULT
二:查看所有的PROFILE
SQL> select * from dba_profiles order by PROFILE; PROFILE RESOURCE_NAME RESOURCE LIMIT -------------------- ------------------------------ -------- ---------- DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD UNLIMITED DEFAULT PRIVATE_SGA KERNEL UNLIMITED DEFAULT CONNECT_TIME KERNEL UNLIMITED DEFAULT IDLE_TIME KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED DEFAULT CPU_PER_CALL KERNEL UNLIMITED DEFAULT CPU_PER_SESSION KERNEL UNLIMITED DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED DEFAULT PASSWORD_GRACE_TIME PASSWORD 7
三.参数解释
1、对数据库资源做限制 { { SESSIONS_PER_USER 每个用户名所允许的并行会话数 | CPU_PER_SESSION 一个会话一共可以使用的CPU时间,单位是百分之一秒 | CPU_PER_CALL 一次SQL调用(解析、执行和获取)允许使用的CPU时间 | CONNECT_TIME 限制会话连接时间,单位是分钟 | IDLE_TIME 允许空闲会话的时间,单位是分钟 | LOGICAL_READS_PER_SESSION 限制会话对数据块的读取,单位是块 | LOGICAL_READS_PER_CALL 限制SQL调用对数据块的读取,单位是块 | COMPOSITE_LIMIT “组合打法” } { integer | UNLIMITED | DEFAULT } | PRIVATE_SGA 限制会话在SGA中Shared Pool中私有空间的分配 { size_clause | UNLIMITED | DEFAULT} } 2、对密码做限制 { { FAILED_LOGIN_ATTEMPTS 帐户被锁定之前可以错误尝试的次数 | PASSWORD_LIFE_TIME 密码可以被使用的天数,单位是天,默认值180天 | PASSWORD_REUSE_TIME 密码可重用的间隔时间(结合PASSWORD_REUSE_MAX) | PASSWORD_REUSE_MAX 密码的最大改变次数(结合PASSWORD_REUSE_TIME) | PASSWORD_LOCK_TIME 超过错误尝试次数后,用户被锁定的天数,默认1天 | PASSWORD_GRACE_TIME 当密码过期之后还有多少天可以使用原密码 } { expr | UNLIMITED | DEFAULT } | PASSWORD_VERIFY_FUNCTION { function | NULL | DEFAULT } }
1.如果用户超过了connect_time或idle_time的会话资源限制,数据库就回滚当前事务,并结束会话。用户再次执行命令,数据库则返回一个错误, 2.如果用户试图执行超过其他的会话资源限制的操作,数据库放弃操作,回滚当前事务并立即返回错误。用户之后可以提交或回滚当前事务,必须结束会话。 提示:可以将一条分成多个段,如1小时(1/24天)来限制时间,可以为用户指定资源限制,但是数据库只有在参数生效后才会执行限制。
六、常用命令
sqlplus /nolog conn sys/oracle@orcl as sysdba
(su - oracle -c "sqlplus / as sysdba")
show parameter processes;
alter system set processes = 1000 scope=spfile;
select username,PROFILE FROM dba_users;
create profile DEFAULT limit connect_time 60 idle_time 30
alter profile DEFAULT limit idle_time 30;
alter profile DEFAULT limit connect_time 60;
select count(*) from v$process;
select count(*) from v$session;
select *from dba_profiles
select * from dba_profiles s where s.profile='DEFAULT' and resource_name='PASSWORD_LIFE_TIME';
alter profile DEFAULT limit PASSWORD_LIFE_TIME unlimited; shutdown immediate; startup (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|