一、设置回话和连接时间

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

二:修改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