配置共享模式:
找到初始化文件pfile,然后对其编辑或加入如下内容:
create pfile=/oracle/product/11g/db_1/dbs/initorcl.ora from spfile;
max_shared_servers=39 #数据库支持的最大的服务进程数
shared_servers=3 #一般应该是数据库连接数的十分之一
max_dispatchers=19 #调度进程的最大数量 = 数据库最大session数 /每个调度器服务的session数;
dispatchers="(PROTOCOL=TCP)(SERVICE=orclXDB)(list=orcl)(con=30)(dispa=5)(sess=200)"
保存退出,reboot数据库,然后生成spfile,重启数据库。
create spfile='/oracle/product/11g/db_1/dbs/spfileorcl.ora' from pfile;
查看共享服务器配置是否正确:
SQL> show parameter shared
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer 39
shared_memory_address integer 0
shared_pool_reserved_size big integer 43620761
shared_pool_size big integer 832M
shared_server_sessions integer
shared_servers integer 3
SQL> show parameter dispa
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
dispatchers string (PROTOCOL=TCP)(SERVICE=orclXDB
)(list=orcl)(con=30)(dispa=5)(
sess=200)
max_dispatchers integer 19
然后再客户端配置tnnames.ora
TEST158 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.158)(PORT = 1521))
(CONNECT_DATA =
(SERVER = SHARED)
(SID = orcl)
)
)
现在连接就是共享连接。查看连接方式:
select * from v$session t where t.server='SHARED'
select * from v$session t where t.server='DEDICATED'
如果设置为:
TEST158 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.3.158)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
再次连接时,就是专用连接了。