Navaneeth's Blog: February 2014

Tuesday, February 25, 2014

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

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

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.