OraFAQ Forum: Server Administration » Identify sessions connected with Oracle connection Magager

Home » RDBMS Server » Server Administration » Identify sessions connected with Oracle connection Magager

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Identify sessions connected with Oracle connection Magager [message #609948]

Fri, 14 March 2014 08:57

Malakay79
Messages: 41
Registered: September 2007

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?

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #609964 is a reply to message #609948]

Fri, 14 March 2014 12:10

Michel Cadot
Messages: 67667
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Oracle version?

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #610043 is a reply to message #609964]

Mon, 17 March 2014 03:31

Malakay79
Messages: 41
Registered: September 2007

Member

11.2.0.4 on Linux.

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #610049 is a reply to message #610043]

Mon, 17 March 2014 03:42

Michel Cadot
Messages: 67667
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Try V$SESSION_CONNECT_INFO.

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #610055 is a reply to message #609948]

Mon, 17 March 2014 03:59

pavanintouch
Messages: 6
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

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #610058 is a reply to message #610055]

Mon, 17 March 2014 04:06

Michel Cadot
Messages: 67667
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.

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #610061 is a reply to message #610055]

Mon, 17 March 2014 04:24

Malakay79
Messages: 41
Registered: September 2007

Member

No information about Oracle connection manager in V$SESSION_CONNECT_INFO or in v$process/v$session

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #610065 is a reply to message #610061]

Mon, 17 March 2014 05:27

Malakay79
Messages: 41
Registered: September 2007

Member

Oracle traces everything, how can be possibile that it does not map this information?

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #610066 is a reply to message #610065]

Mon, 17 March 2014 05:33

Michel Cadot
Messages: 67667
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?

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #610068 is a reply to message #610066]

Mon, 17 March 2014 05:36

Malakay79
Messages: 41
Registered: September 2007

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.

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #610069 is a reply to message #610068]

Mon, 17 March 2014 05:52

Michel Cadot
Messages: 67667
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.

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #610071 is a reply to message #610069]

Mon, 17 March 2014 06:37

Malakay79
Messages: 41
Registered: September 2007

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.

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #610074 is a reply to message #610071]

Mon, 17 March 2014 06:50

Michel Cadot
Messages: 67667
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #610075 is a reply to message #610071]

Mon, 17 March 2014 06:51

Malakay79
Messages: 41
Registered: September 2007

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.

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #610080 is a reply to message #610075]

Mon, 17 March 2014 08:03

Michel Cadot
Messages: 67667
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

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

Report message to a moderator

Re: Identify sessions connected with Oracle connection Magager [message #610203 is a reply to message #610075]

Tue, 18 March 2014 07:54

EdStevens
Messages: 1293
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.

Report message to a moderator

Previous Topic:	changing awr retention (space used)
Next Topic:	ORA-01034: ORACLE not available (some users only)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 27 01:54:20 CST 2021