Re: shared server or dedicated server -- How to find ?

From: Mladen Gogala <mgogala_at_adelphia.net>
Date: Mon, 01 Dec 2003 21:29:25 -0800
Message-ID: <F001.005D8590.20031201212925@fatcity.com>

Your database is not running on either share or dedicated session mode. Your sessions are connecting to either a dedicated server or a shared server. You can have a mix as well. Some sessions may be connecting to dedicated servers while others may be utilizing shared servers. It doesn't sound very logical, but if you want to find out about your sessions, you should go and consult V$SESSION. There is, believe or not, a column called "SERVER" Here is an example of a query that should tell you everything:
SQL> select username,server,module from v$session 2 where type='USER';

USERNAME SERVER MODULE
------------ --------- ---------------
OPS$MGOGALA SHARED SQL*Plus

SYSTEM       DEDICATED TOra
SCOTT        SHARED    SQL*Plus
SYSTEM       DEDICATED TOra
SCOTT        DEDICATED SQL*Plus

So, I have a user called OPS$MGOGALA (I know, I'm a lazy bum, but I hate typing. I really do) logged in using shared server connection from SQL*Plus, user SYSTEM logged in using dedicated server connection using tora (2 connections, schema browser and sessions monitor) , then SCOTT logged in using shared server connection and another SCOTT using a dedicated server connection. Such a mix is typical. For some things I used shared servers, for others I utilize dedicated ones. V$SESSION tells me what is what. Database is said to be in the shared server mode as soon as you have dispatchers and shared servers configured and are able to connect in the shared server mode. Listener should look something like this:

SQL> $ lsnrctl stat

LSNRCTL for Linux: Version 9.2.0.4.0 - Production on 02-DEC-2003 00:19:25

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=medo)(PORT=1521))) STATUS of the LISTENER

Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 9.2.0.4.0 - Production
Start Date                02-DEC-2003 00:02:02
Uptime                    0 days 0 hr. 17 min. 22 sec
Trace Level               off
Security                  OFF
SNMP                      OFF
Listener Parameter File   /oracle/product/9.2.0/network/admin/listener.ora
Listener Log File         /oracle/product/9.2.0/network/log/listener.log

Listening Endpoints Summary...

  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=medo)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=o9i)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=medo)(PORT=2100))(Presentation=FTP)(Session=RAW))

Services Summary...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Service "o9i.adelphia.net" has 2 instance(s). Instance "o9i", status UNKNOWN, has 1 handler(s) for this service... Instance "o9i", status READY, has 3 handler(s) for this service... The command completed successfully
$

Here are 3 dispatchers:

1) Protocol=TCP, host=medo, port=1521
2) Protocol=IPC key=$ORACLE_SID
3) EXTPROC, for procedural extensions.

XDM was commented out. This database is in the MTS mode, but I can still connect using dedicated server if I want to. Did I tell you that I hate typing? Well, I'm on strike until tomorrow morning.

On 2003.12.01 23:19, Prem Khanna J wrote:
> List ,
>
> I would like to know whether my database is running on
> shared server (MTS) or dedicated server mode ?
>
> How do i find it out ?
>
> it's oracle 9.2.0.3 on Win2k.
>
> Regards,
> Jp.
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Prem Khanna J
> INET: jprem_at_kssnet.co.jp
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Mladen Gogala
Oracle DBA
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mgogala_at_adelphia.net

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Mon Dec 01 2003 - 23:29:25 CST