Oracle Session的几个知识点

关于Oracle Session的几个知识点

[收藏此页] []

作者：tomszrp  2006-06-26

内容导航：

    【IT168 技术文档】

    1)强行终止一个session

    在Oracle中，我们可以通过发出 alter system kill session statement.这样的命令来强行停掉一个session.需要清楚的是：alter system操作并不能立即清除该session直到time out或者用户尝试通过该session再次执行其他的statement的时候，服务进程才会发出ORA-03113 EOF on communication channel or "packet write failure". 这也是为什么当我们kill一个session，长时间在v$session还能看到该session[一般status为KILLED]的信息的缘故.

    如果我们设置了DCD检测，那么系统将会在expiry time 到来的时间就清除该session,同时释放该session占用的locks资源.(immediatly meaning: after the session's rollback)

    当然了，大家也可以通过在OS一级发出kill命令来停止一个session.

    比如在aix下，我们通过v$session，v$process两个视图关联得到某个session对应的服务端的process ID,然后在 CMD下，执行 kill -9 processid

    在windows下，可以通过orakill命令在停止一个process

    备注：如果数据库上有logoff trigger之类的系统trigger，it won't be executed for the killed session.

    2)session对应的进程  

If a session belongs to a foreground process, it has v$session.type = 'USER'.
If it belongs to a background process, it has v$session.type = 'BACKGROUND'. 

    常见的后台进程

    background processes
    SMON
    The System Monitor carries out a crash recovery when a crashed insance is started up again. It also cleans temporary segments.
    PMON
    The Process Monitor checks if a user process fails and if so, does all cleaning up of resources that the user process has aquired.
    DBWR
    The Database Writer writes dirty blocks from the database buffer to the datafiles. How many DBWn Processes are started is determined by the initialization parameter DB_WRITER_PROCESSES. DBWR also writes the actual SCN with the Block.
    LGWR
    The Log Writer writes the redo log buffer from the SGA to the online redo log file.
    MMAN
    The memory manager
    MRP
    Managed recovery process: the process that applies archived redo log to the standby database.
    RFS
    The remote file server process on the standby database receives archived redo logs from the primary database.
    RECO
    The Distributed Transaction Recovery Process finds pending (distributed) transaction and resolves them.
    CKPT
    The Checkpoint Process reqularly initiates a checkpoint which uses DBWR to write all dirty blocks back to the datafiles, thus synchronizing the database. Since a Checkpoint records the current SCN, in a recovery only redo records with a SCN higher than that of the last checkpoint need to be applied.
    ARCH
    The Archiver Process archives redo log files if ARCHIVELOG is enabled.
    Dnnn
    The Dispatcher Process is used in a shared server environment.
    Snnn
    The Shared Server Process is used in a shared server environment.
    LMON
    The lock manager.
    LMD0
    QMNn
    AQ Time Manager
    TRWR
    Trace writer
    WMON
    The wakeup monitor process.
    LCKnnn
    Inter-instance locking process.
    SNPnnn
    The snapshot process.
    MMON
    New background process in Oracle 10g.
    DMON
    The Data Guard Broker process.
    SNP
    The snapshot process.

    3)通过profile来控制session对资源的使用

    在Oracle中，我们通过profile，可以实现对session的部分资源的使用限制。例如：

sessions per user
cpu per session
maximum connect time
maximum idle time
maximum block reads per session
maximum amount of private SGA.
.....

    比如我们要实现：
    对一个学习用户study，当他的空闲时间超过2分钟后，系统自动断开连接. 那么我们就可以通过使用profiles来完成

SQL> conn /as sysdba
已连接

SQL> show parameter resource_limit;
NAME TYPE VALUE
------------------------------------ --------
resource_limit boolean TRUE

SQL> create profile study_profile limit
2 idle_time 2;
配置文件已创建

SQL> alter user study profile study_profile;
用户已更改。

    下面我们测试一下：

E:>sqlplus /nolog

SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 6月 24 19:01:43 2006
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn study/study
已连接。

SQL> select sysdate from dual;
SYSDATE
----------
24-6月 -06

SQL>

    过2分钟我们再尝试执行一个SQL 

SQL> select sysdate from dual;
 *
 ERROR 位于第 1 行:
ORA-02396: 超出最大空闲时间，请重新连接