Home » RDBMS Server » Server Administration » Identify sessions connected with Oracle connection Magager
Identify sessions connected with Oracle connection Magager [message #609948] Fri, 14 March 2014 08:57 Go to next message
Malakay79
Messages: 29
Registered: September 2007
Junior Member
Hi,
I have some users that use Oracle connection manager (CMAN) to connect to the database.
Is there a way to identify those sessions from v$session or V$process?
Re: Identify sessions connected with Oracle connection Magager [message #609964 is a reply to message #609948] Fri, 14 March 2014 12:10 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Oracle version?

Re: Identify sessions connected with Oracle connection Magager [message #610043 is a reply to message #609964] Mon, 17 March 2014 03:31 Go to previous messageGo to next message
Malakay79
Messages: 29
Registered: September 2007
Junior Member
11.2.0.4 on Linux.
Re: Identify sessions connected with Oracle connection Magager [message #610049 is a reply to message #610043] Mon, 17 March 2014 03:42 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Try V$SESSION_CONNECT_INFO.

Re: Identify sessions connected with Oracle connection Magager [message #610055 is a reply to message #609948] Mon, 17 March 2014 03:59 Go to previous messageGo to next message
pavanintouch
Messages: 5
Registered: March 2014
Location: hyderabad
Junior Member

Hi,
To identify the session try the below links. these helped me lot.

http://orcl11gdba.blogspot.in/2014/03/getting-oracle-sidsession-identifier.html

Regards
Pavan
Re: Identify sessions connected with Oracle connection Magager [message #610058 is a reply to message #610055] Mon, 17 March 2014 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

How does this answer the issue addressed here which is about connection manager?
STOP spamming us with your blog.

Re: Identify sessions connected with Oracle connection Magager [message #610061 is a reply to message #610055] Mon, 17 March 2014 04:24 Go to previous messageGo to next message
Malakay79
Messages: 29
Registered: September 2007
Junior Member
No information about Oracle connection manager in V$SESSION_CONNECT_INFO or in v$process/v$session
Re: Identify sessions connected with Oracle connection Magager [message #610065 is a reply to message #610061] Mon, 17 March 2014 05:27 Go to previous messageGo to next message
Malakay79
Messages: 29
Registered: September 2007
Junior Member
Oracle traces everything, how can be possibile that it does not map this information?
Re: Identify sessions connected with Oracle connection Magager [message #610066 is a reply to message #610065] Mon, 17 March 2014 05:33 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

It may not be in the database server but the connection manager one.
Oracle Db knows only what the opening connect string (including its associated parameter like IP, machine...) contains. What is the machine name from v$session for these sessions, client or connection manager server?

Re: Identify sessions connected with Oracle connection Magager [message #610068 is a reply to message #610066] Mon, 17 March 2014 05:36 Go to previous messageGo to next message
Malakay79
Messages: 29
Registered: September 2007
Junior Member
Michel Cadot wrote on Mon, 17 March 2014 05:33

It may not be in the database server but the connection manager one.
Oracle Db knows only what the opening connect string (including its associated parameter like IP, machine...) contains. What is the machine name from v$session for these sessions, client or connection manager server?



In machine field on v$session I see the machine name of the user session, not connection manager.
Re: Identify sessions connected with Oracle connection Magager [message #610069 is a reply to message #610068] Mon, 17 March 2014 05:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

So Oracle correctly passed the information (in my opinion) and it seems it does not want the DB knows it comes through the connection manager.

Why do you need this information, maybe we can achieve your final goal in another way.

Re: Identify sessions connected with Oracle connection Magager [message #610071 is a reply to message #610069] Mon, 17 March 2014 06:37 Go to previous messageGo to next message
Malakay79
Messages: 29
Registered: September 2007
Junior Member
I think I can add a rule to oracle connection manager.
Now I do not use rule:
(rule_list=
(rule=
(src=*)(dst=*)(srv=*)(act=accept)
(action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
)
)
)

I can add a rule like this:

(rule_list=
(rule=
(src=*)(dst=dbxx-int.dbc.cineca.it)(srv=service_name1_ext)(act=accept)
(src=*)(dst=dbxx-int.dbc.cineca.it)(srv=service_name2_ext)(act=accept)
(action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
)
)
)

If I do this, and in v$session I see a session that uses the service_name1_ext, I know it is using connection manager.

Re: Identify sessions connected with Oracle connection Magager [message #610074 is a reply to message #610071] Mon, 17 March 2014 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Yes this is a good way to achieve this, thanks for the feedback.

Re: Identify sessions connected with Oracle connection Magager [message #610075 is a reply to message #610071] Mon, 17 March 2014 06:51 Go to previous messageGo to next message
Malakay79
Messages: 29
Registered: September 2007
Junior Member
Since my two service_name end with ext can I use a single rule like this?


I can add a rule like this:
(rule_list=
(rule=
(src=*)(dst=dbxx-int.dbc.cineca.it)(srv=*_ext)(act=accept)
(action_list=(aut=off)(moct=0)(mct=0)(mit=0)(conn_stats=on))
)
)
)



Oracle 11.2.0.4 on Linux.
Re: Identify sessions connected with Oracle connection Magager [message #610080 is a reply to message #610075] Mon, 17 March 2014 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Sorry, it is beyond my knowledge, maybe someone else can answer.

Re: Identify sessions connected with Oracle connection Magager [message #610203 is a reply to message #610075] Tue, 18 March 2014 07:54 Go to previous message
EdStevens
Messages: 282
Registered: September 2013
Senior Member
It would still be interesting to get an answer to Michael's earlier question ... WHY do you want to know if a connection came via connection manager?

What decision depends on knowing if CM was used by a particular session?

Quite often people spend inordinate amounts of time chasing a technical solution to answer the wrong question.
Previous Topic: changing awr retention (space used)
Next Topic: ORA-01034: ORACLE not available (some users only)
Goto Forum:
  


Current Time: Sun Sep 21 21:54:06 CDT 2014

Total time taken to generate the page: 0.08159 seconds