Home » RDBMS Server » Server Administration » v$session - username, user#
v$session - username, user# [message #231099] Sat, 14 April 2007 02:17 Go to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

hi friends,
I have one confusion. Help me to solve it please.

I am using oracle 10g on RHEL AS 4.0

During test practise, i did following..
login: sys as sysdba

select username,user# from v$session;
result : 27 rows,
where 1 row for user sys with user# 0,
2 rows for user dbsnmp with user# 22,
some rows for user sysman with user# 54, and
some rows where username is not there and user# is 0.

Then i created two new users, testuser1 and testuser2.
opened new sqlplus n login as testuser1 and testuser3.
(now i have 3 sessions opened : sys as sysdba,testuser1,testuser2)
Then fired the same query as above.
result : 27 rows,
where 1 row for user sys with user# 0,
1 row for user testuser1 with user# 58,
1 row for user testuser2 with user# 59,
2 rows for user dbsnmp with user# 22,
some rows for user sysman with user# 54, and
some rows where username is not there and user# is 0.

CONFUSION :
1) My confusion is : why so many users with SYSMAN (user# 54) are listed. they are for what.
2) And what about the row where username column is blank and user# is 0. what do they represent?
3) Is there any other way, to know from the sqlpromt that, this session is opened using which username? the way which normal user can use. Means, suppose a user X has 3 accounts, but he forgot that using which username, he had opened the sql session, how can he know. Is there any view or other way to know this..?

Thanks in advance..

Regards..

DIPALI.
Re: v$session - username, user# [message #231100 is a reply to message #231099] Sat, 14 April 2007 02:23 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

Hi,

CONFUSION :
1) My confusion is : why so many users with SYSMAN (user# 54) are listed. they are for what.

SYSMAN user account use for DBCONSOLE ENTERPRISE MANAGER.

SQL> select username, status from v$session where username = 'SYSMAN';

USERNAME                       STATUS
------------------------------ --------
SYSMAN                         INACTIVE
SYSMAN                         INACTIVE
SYSMAN                         INACTIVE
SYSMAN                         ACTIVE
SYSMAN                         INACTIVE
SYSMAN                         INACTIVE
SYSMAN                         INACTIVE
SYSMAN                         INACTIVE
SYSMAN                         INACTIVE
SYSMAN                         INACTIVE

10 rows selected.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Pr
oduction
With the Partitioning, OLAP and Data Mining options

C:\Documents and Settings\m.taj.FAKHRUDDIN>set oracle_sid=orcl

C:\Documents and Settings\m.taj.FAKHRUDDIN>emctl stop dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.1.0.2.0
Copyright (c) 1996, 2004 Oracle Corporation.  All rights reserved.
http://alwarid-taj.fakhruddin.local:5500/em/console/aboutApplication
The OracleDBConsoleorcl service is stopping....
The OracleDBConsoleorcl service was stopped successfully.


C:\Documents and Settings\m.taj.FAKHRUDDIN>sqlplus "/ as sysdba"

SQL*Plus: Release 10.1.0.2.0 - Production on Sat Apr 14 11:20:09 2007

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> select username, status from v$session where username = 'SYSMAN';

no rows selected


2) And what about the row where username column is blank and
user# is 0. what do they represent?

Internal Database Users for backgroud processes.

3) Is there any other way, to know from the sqlpromt that, this session is opened using which username? the way which normal user can use. Means, suppose a user X has 3 accounts, but he forgot that using which username, he had opened the sql session, how can he know. Is there any view or other way to know this..?

Not understand.

[Updated on: Sat, 14 April 2007 02:23]

Report message to a moderator

Re: v$session - username, user# [message #231101 is a reply to message #231099] Sat, 14 April 2007 02:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
3)
SQL> show user
USER is "MICHEL"
SQL> def
DEFINE _DATE           = "14/04/2007 09:26:53" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "MIKA" (CHAR)

DEFINE _USER           = "MICHEL" (CHAR)

DEFINE _PRIVILEGE      = "" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1002000300" (CHAR)
DEFINE _EDITOR         = "Notepad" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options" (CHAR)
DEFINE _O_RELEASE      = "1002000300" (CHAR)

Regards
Michel
Re: v$session - username, user# [message #231102 is a reply to message #231101] Sat, 14 April 2007 02:53 Go to previous messageGo to next message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

Great..
Thanks a lot, taj and michel....
So nice of you.. Smile

yes, i have started emctl.
But, why so many SYSMAN sessions..?

And is there one blank user for one Background process?
It is the user used by oracle internally, to maintain the bk process, then why it is listed to user..? I mean, is there any special purpose behind it...?

I have read many books to understand the oracle architecture, but get this kind of knowledge only when i try to share my knowledge, practise or confusion in such forum...
Thanks.. Smile

[Updated on: Sat, 14 April 2007 02:59]

Report message to a moderator

Re: v$session - username, user# [message #231103 is a reply to message #231099] Sat, 14 April 2007 03:21 Go to previous message
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
Senior Member

hi friends,
I found the other way too to know the session is opened using which username. It is

SQL> Select distinct username from user_audit_trail;
More over we can also know other very userful information from this view like login and logout time, os username, userhost,terminal ect and the other auditing information..

Cheers..
Previous Topic: Diskgroup creation erro in oracle 10g
Next Topic: What´s the maximum value of the parameter buffer ( export ) ?
Goto Forum:
  


Current Time: Sat Dec 10 20:43:04 CST 2016

Total time taken to generate the page: 0.09467 seconds