Manage using Oracle Database Service for Segregating application users and monitoring performance

exec dbms_service.CREATE_SERVICE(SERVICE_NAME=>'test2',NETWORK_NAME=>'test2');

exec dbms_service.START_SERVICE(SERVICE_NAME=>'test2');

alter system set service_names=PSETLXMT,Ntest1,test2;

oracle@PSETLXMT> show parameter local_listener

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

local_listener                       string      (ADDRESS=(PROTOCOL=tcp)(HOST=DG1224)(PORT=1628))

Update the below in the tnsnames.ora file :

NTEST1 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = DG1224)(PORT = 1628))

    )

   (CONNECT_DATA =

       (SID_NAME=PSETLXMT)

       (SERVICE_NAME = NTEST1)

    )

  )

TEST2 =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = DG1224)(PORT = 1628))

    )

    (CONNECT_DATA =

       (SID_NAME=PSETLXMT)

       (SERVICE_NAME = test2)

    )

  )

$ sqlplus test/test123#@NTEST1

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 29 12:35:21 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing options

test@NTEST1>

$ sqlplus test/test123#@test2

SQL*Plus: Release 11.2.0.3.0 Production on Wed Jan 29 12:35:57 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,Data Mining and Real Application Testing options

test@test2>

able to see the sessions which connects using the service name in the v$session

oracle@PSETLXMT> select sid,serial#,schemaname,service_name from v$session where type !='BACKGROUND';

       SID    SERIAL# SCHEMANAME                     SERVICE_NAME

---------- ---------- ------------------------------ ----------------------------------------------------------------

        99       1021 FTP_DV_ETL                     SYS$USERS

       293       8047 SYS                            SYS$USERS

       387      44311 SYS                            SYS$USERS

       581      17421 FTP_DV_ETL                     SYS$USERS

       676      22123 FTP_DV_ETL                     SYS$USERS

       772      22383 FTP_DV_ETL                     SYS$USERS

       868      19337 TEST                           test2

       964      12479 FTP_DV_ETL                     SYS$USERS

      1059      12187 TEST                           test1

      1156       4117 FTP_DV_ETL                     SYS$USERS

      1444       2519 FTP_DV_ETL                     SYS$USERS

In the ashr report as well.