Home » RDBMS Server » Server Administration » v$session - username, user#
| v$session - username, user# [message #231099] |
Sat, 14 April 2007 02:17  |
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  |
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 #231102 is a reply to message #231101] |
Sat, 14 April 2007 02:53 |
Dipali Vithalani
Messages: 278
Registered: March 2007
Location: India
|
Senior Member |
|
|
Great..
Thanks a lot, taj and michel....
So nice of you.. 
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..
[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 |
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..
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 00:39:08 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
