How to map a client program to a Resource Manager consumer group

G'day,

I am encountering a problem with the resource group mapping both in 
Oracle 10.2.0.1/linux and 10.1.0.4/Solaris (64 bits). I am trying to 
apply a specific resource consumer group to users who log on using 
either discoverer plus (dis51ws.exe) or discoverer desktop 
(dis51usr.exe). This can be anybody, coming from any machine. As the 
same clients can also log on with other, more important programs, 
mapping the userid's or their machine is not an option. It seems to me 
that the database does not map the user sessions correctly because of a 
capitalisation problem:

Users using Oracle Discovery Plus or Desktop should be mapped to the 
DISCO consumer group on the name of the client program. In v$session all 
users are listed as having DEFAULT_CONSUMER_GROUP rather than DISCO though:

SQL> select sid, serial#,  RESOURCE_CONSUMER_GROUP, program from v$session;

       SID    SERIAL# RESOURCE_CONSUMER_GROUP          PROGRAM
---------- ---------- -------------------------------- 
-------------------------------------
       144       4875 SYS_GROUP                        sqlplus@PC0200026 
(TNS V1-V3)
       145      11309 DEFAULT_CONSUMER_GROUP           sqlplus@PC0200026 
(TNS V1-V3)
       146       1615 DEFAULT_CONSUMER_GROUP           dis51usr.exe
       147      12607 DEFAULT_CONSUMER_GROUP           dis51usr.exe
       148          1                                  oracle@PC0200026 
(q001)
       151          6                                  oracle@PC0200026 
(q000)
       152       7708                                  oracle@PC0200026 
(J000)
       154          1                                  oracle@PC0200026 
(QMNC)
       158       9274 DEFAULT_CONSUMER_GROUP           dis51adm.exe
       159      10843 DEFAULT_CONSUMER_GROUP           sqlplus.exe
       160          1                                  oracle@PC0200026 
(MMNL)
       161          1                                  oracle@PC0200026 
(MMON)
       162          1                                  oracle@PC0200026 
(CJQ0)
       163          1                                  oracle@PC0200026 
(RECO)
       164          1                                  oracle@PC0200026 
(SMON)
       165          1                                  oracle@PC0200026 
(CKPT)
       166          1                                  oracle@PC0200026 
(LGWR)
       167          1                                  oracle@PC0200026 
(DBW0)
       168          1                                  oracle@PC0200026 
(MMAN)
       169          1                                  oracle@PC0200026 
(PSP0)
       170          1                                  oracle@PC0200026 
(PMON)

21 rows selected.

In v$rsrc_session_info the same current group is shown and the mapping 
attribute field is empty. This tells me that Oracle does not see the 
client program, right? This are the mappings:

SQL> select ATTRIBUTE, VALUE, CONSUMER_GROUP from DBA_RSRC_GROUP_MAPPINGS;

ATTRIBUTE            VALUE           CONSUMER_GROUP       STATUS
-------------------- --------------- -------------------- ----------
ORACLE_USER          SYS             SYS_GROUP            ACTIVE
ORACLE_USER          SYSTEM          SYS_GROUP            ACTIVE
CLIENT_PROGRAM       DIS51USR.EXE    DISCO                ACTIVE
CLIENT_PROGRAM       DIS51WS.EXE     DISCO                ACTIVE


I tried to enclose the program name in double quotes to see if that 
would preserve capitalisation... it did not. It just added the double 
quotes to the capitalised value. The mappings were created with the 
following statements (note that the program name is specified in the 
correct case):

begin
 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING 
(DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM, 'dis51usr.exe', 'disco'); -- 
discoverer desktop
 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING 
(DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM, 'dis51ws.exe' , 'disco'); -- 
discoverer plus
end;

Has anyone got this to work? What am I missing here? 

Database versions tried:
10.2.0.1.0 / Linux
10.1.0.4.0 - 64bit / Solaris

Both enterprise editions.

Cheers,
-- 
Tony van Lingen
Systems Administrator and DBA
Information Management
Corporate Development Division
Environmental Protection Agency

Ph:     (07) 3234 1972
Fax:    (07) 3227 6534
Mobile: 0413 701 284
E-mail: tony.vanlingen@xxxxxxxxxxxxxx

Visit us online at www.epa.qld.gov.au
--


___________________________
Disclaimer

WARNING: This e-mail (including any attachments) has originated from a 
Queensland Government department and may contain information that is 
confidential, private, or covered by legal professional privilege, and may be 
protected by copyright.  

You may use this e-mail only if you are the person(s) it was intended to be 
sent to and if you use it in an authorised way.  No one is allowed to use, 
review, alter, transmit, disclose, distribute, print or copy this e-mail 
without appropriate authority.  If you have received this e-mail in error, 
please inform the sender immediately by phone or e-mail and delete this e-mail, 
including any copies, from your computer system network and destroy any 
hardcopies.

Unless otherwise stated, this e-mail represents the views of the sender and not 
the views of the Environmental Protection Agency.

Although this e-mail has been checked for the presence of computer viruses, the 
Environmental Protection Agency provides no warranty that all viruses have been 
detected and cleaned. Any use of this e-mail could harm your computer system.  
It is your responsibility to ensure that this e-mail does not contain and is 
not affected by computer viruses, defects or interference by third parties or 
replication problems (including incompatibility with your computer system).

E-mails sent to and from the Environmental Protection Agency will be 
electronically stored, managed and may be audited, in accordance with the law 
and Queensland Government Information Standards (IS31, IS38, IS40, IS41 and 
IS42) to the extent they are consistent with the law.

___________________________

--
http://www.freelists.org/webpage/oracle-l

Other related posts: