专有服务模式
*******session 16*******
SQL> select count(*) from utest.tab1;
COUNT(*)
----------
2
SQL> select * from tab1;
ID
----------
2
1
开始一个事务
SQL> insert into tab1 values(1);
1 row created.
查看当前seesion id
SQL> select sid from v$session where audsid=userenv('sessionid');
SID
----------
16
*******session sys*******
查看 sid=16 对应的进程id
SQL> select spid from v$process p, v$session s where s.sid=16 and s.paddr=p.addr;
SPID
------------------------
3640
session 16有一个事务
SQL> select a.status,b.sid from v$transaction a,v$session b where a.addr=b.taddr;
STATUS SID
---------------- ----------
ACTIVE 16
SQL> select count(*) from utest.tab1;
COUNT(*)
----------
2
杀掉3640进程
[oracle@ol354t1123 ~]$ kill -9 3640
*******session sys*******
session 16被杀掉了
SQL> select sid,status,username from v$session where sid=16 ;
no rows selected
session 16的事务被回滚了
SQL> select a.status,b.sid from v$transaction a,v$session b where a.addr=b.taddr;
no rows selected
SQL> select count(*) from utest.tab1;
COUNT(*)
----------
2
共享服务模式
配置共享服务模式
SQL> alter system set dispatchers='(protocol=tcp)(dispatchers=2)' scope=spfile;
System altered.
SQL> show parameter dispatchers;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP) (SERVICE=orclXD
B)
max_dispatchers integer
SQL> SQL> show parameter shared_ser
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers integer
shared_server_sessions integer
shared_servers integer 1
SQL> SQL>
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 1272213504 bytes
Fixed Size 1344680 bytes
Variable Size 788532056 bytes
Database Buffers 469762048 bytes
Redo Buffers 12574720 bytes
Database mounted.
Database opened.
SQL> alter system set max_dispatchers=3;
System altered.
SQL> show parameter dispatchers;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (protocol=tcp)(dispatchers=2)
max_dispatchers integer 3
SQL> SQL>
SQL> alter system set shared_servers=3;
System altered.
SQL> SQL> alter system set max_shared_servers=6;
System altered.
SQL> SQL>
SQL> show parameter shared_servers;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
max_shared_servers integer 6
shared_servers integer 3
SQL> SQL>
[oracle@ol354t1123 ~]$ ps -ef | grep ora
oracle 3465 1 0 Dec20 ? 00:00:00 ora_d000_orcl
oracle 3467 1 0 Dec20 ? 00:00:00 ora_d001_orcl
oracle 3469 1 0 Dec20 ? 00:00:00 ora_s000_orcl
SQL> create user utest identified by utest;
User created.
SQL> grant connect ,resource to utest;
Grant succeeded.
*******client session 12*******
Enter user-name: utest/utest@ora55
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table tab1(id number);
Table created.
SQL> select * from tab1;
no rows selected
SQL> insert into tab1 values(1);
1 row created.
SQL>
SQL> select sid,status,server from v$session where audsid=userenv('sessionid'
);
SID STATUS SERVER
---------- ------------------------ ---------------------------
12 ACTIVE SHARED
*******client session 15*******
Enter user-name: utest/utest@ora55
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select sid,status,server from v$session where audsid=userenv('sessionid');
SID STATUS SERVER
---------- ------------------------ ---------------------------
15 ACTIVE SHARED
SQL> insert into tab1 values(1);
1 row created.
SQL>
*******session sys*******
SQL> select circuit,dispatcher,saddr,status from v$circuit;
CIRCUIT DISPATCH SADDR STATUS
-------- -------- -------- ----------------
6760E3FC 6B4790AC 6B61AD00 NORMAL
6760EDE8 6B479BD0 6B612A80 NORMAL
SQL> select spid from v$process p, v$session s where (s.sid=12 or s.sid=15) and s.paddr=p.addr;
SPID
------------------------
3465
3467
[oracle@ol354t1123 ~]$ ps -ef | grep ora
oracle 3465 1 0 Dec20 ? 00:00:00 ora_d000_orcl
oracle 3467 1 0 Dec20 ? 00:00:00 ora_d001_orcl
*******client session 18*******
Enter user-name: utest/utest@ora55
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select sid,status,server from v$session where audsid=userenv('sessionid');
SID STATUS SERVER
---------- ------------------------ ---------------------------
18 ACTIVE SHARED
SQL> insert into tab1 values(1);
1 row created.
*******client session 20*******
Enter user-name: utest/utest@ora55
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select sid,status,server from v$session where audsid=userenv('sessionid')
SID STATUS SERVER
---------- ------------------------ ---------------------------
20 ACTIVE SHARED
*******session sys*******
SQL> select s.sid,p.spid from v$process p, v$session s where (s.sid=12 or s.sid=15 or s.sid=18 or s.sid=20) and s.paddr=p.addr;
SID SPID
---------- ------------------------
12 3465
15 3467
18 3467 20 3465
SQL> select circuit,dispatcher,saddr,status from v$circuit;
CIRCUIT DISPATCH SADDR STATUS
-------- -------- -------- ----------------
6760E3FC 6B4790AC 6B61AD00 NORMAL
6760EDE8 6B479BD0 6B612A80 NORMAL
6760F7D4 6B479BD0 6B60A800 NORMAL
676101C0 6B4790AC 6B605100 NORMAL
[oracle@ol354t1123 ~]$ ps -ef | grep oracle
root 3122 3098 0 Dec20 pts/0 00:00:00 su - oracle
oracle 3123 3122 0 Dec20 pts/0 00:00:00 -bash
oracle 3152 1 0 Dec20 ? 00:00:00 /opt/oracle/bin/tnslsnr LISTENER -inherit
oracle 3155 3123 0 Dec20 pts/0 00:00:00 sqlplus as sysdba
root 3277 3251 0 Dec20 pts/1 00:00:00 su - oracle
oracle 3278 3277 0 Dec20 pts/1 00:00:00 -bash
oracle 3433 1 0 Dec20 ? 00:00:00 ora_pmon_orcl
oracle 3435 1 0 Dec20 ? 00:00:00 ora_psp0_orcl
oracle 3437 1 0 Dec20 ? 00:00:00 ora_vktm_orcl
oracle 3441 1 0 Dec20 ? 00:00:00 ora_gen0_orcl
oracle 3443 1 0 Dec20 ? 00:00:00 ora_diag_orcl
oracle 3445 1 0 Dec20 ? 00:00:00 ora_dbrm_orcl
oracle 3447 1 0 Dec20 ? 00:00:00 ora_dia0_orcl
oracle 3449 1 0 Dec20 ? 00:00:00 ora_mman_orcl
oracle 3451 1 0 Dec20 ? 00:00:03 ora_dbw0_orcl
oracle 3453 1 0 Dec20 ? 00:00:01 ora_lgwr_orcl
oracle 3455 1 0 Dec20 ? 00:00:05 ora_ckpt_orcl
oracle 3457 1 0 Dec20 ? 00:00:02 ora_smon_orcl
oracle 3459 1 0 Dec20 ? 00:00:00 ora_reco_orcl
oracle 3461 1 0 Dec20 ? 00:00:03 ora_mmon_orcl
oracle 3463 1 0 Dec20 ? 00:00:04 ora_mmnl_orcl
oracle 3465 1 0 Dec20 ? 00:00:00 ora_d000_orcl
oracle 3467 1 0 Dec20 ? 00:00:00 ora_d001_orcl oracle 3469 1 0 Dec20 ? 00:00:00 ora_s000_orcl
oracle 3499 3155 0 Dec20 ? 00:00:01 oracleorcl (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle 3501 1 0 Dec20 ? 00:00:00 ora_qmnc_orcl
oracle 3515 1 0 Dec20 ? 00:00:01 ora_cjq0_orcl
oracle 3519 1 0 Dec20 ? 00:00:00 ora_q000_orcl
oracle 3521 1 0 Dec20 ? 00:00:00 ora_q001_orcl
oracle 3523 1 0 Dec20 ? 00:00:00 ora_s001_orcl
oracle 3525 1 0 Dec20 ? 00:00:00 ora_s002_orcl
oracle 3541 1 0 Dec20 ? 00:00:00 ora_smco_orcl
oracle 12468 1 0 10:01 ? 00:00:00 ora_w000_orcl
oracle 12476 1 0 10:05 ? 00:00:00 ora_w001_orcl
oracle 12478 1 0 10:05 ? 00:00:00 ora_w002_orcl
oracle 12499 3278 21 10:14 pts/1 00:00:00 ps -ef
oracle 12500 3278 5 10:14 pts/1 00:00:00 grep oracle
SQL> select a.status,b.sid from v$transaction a,v$session b where a.addr=b.taddr;
STATUS SID
---------------- ----------
ACTIVE 15
ACTIVE 18
SQL> select * from utest.tab1;
no rows selected
杀掉进程3467
[oracle@ol354t1123 ~]$ kill -9 3467
session 15和18的进程被回滚
SQL> select a.status,b.sid from v$transaction a,v$session b where a.addr=b.taddr;
no rows selected
SQL> select * from utest.tab1;
no rows selected
SQL>
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24756186/viewspace-751610/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/24756186/viewspace-751610/