In my previous articles I have given the way to find cumulative sum and queries like that. In this article I would like to give you the straightforward steps to find the Username who executed query with example. This is key DBA task. There are sometime DBA will face scenario where some people are running queries wrongly and due to that the DB will get locked. This article will give 3 simple queries with using which you can find out the username who executed query.
Scenarios Why we need to find Username who executed Query?
The question in mind that why we require to find the username for executed query. There are so many real world scenarios where you require to find out the username. The most common scenario is when application was facing the performance issue due to some queries. As in industry we are working with distributed environment and there are so many user sessions running parallelly. In this kind of situation you require to find out the username who executed the query so that we can find the root cause of the problem. Sometimes in reporting also we require usernames who are executing specific SQL statements.
How to Find out Username who executed Query?
We require to use the Oracle System tables to find out the Username who executed query. We commonly need to use the following 2 system tables and one system view which will give you the username.
1.DBA_HIST_ACTIVE_SESS_HISTORY
2.DBA_USERS
3.V$ACTIVE_SESSION_HISTORY
Scenario 1 : When you know the SQL query ID.
If you know the SQL query id you can use the System view directly to find out the User who has executed the query. Lets say the SQL id is “am66usf0p71f”
Query :
Select USER_ID from V$ACTIVE_SESSION_HISTORY where SQL_ID = ‘am66usf0p71f’;
Output:
3430
Scenario 2 : When you know the SQL query ID but don’t have access to system views
Sometimes you may not have access for system views. You require to use system tables.
Query :
select USER_ID from DBA_HIST_ACTIVE_SESS_HISTORY where SQL_ID = ‘da66usnr0p71f’;
Scenario 3 : You need to find out the Username by User_id
You can find out the user_id by above two scenarios. But if you want to find out username use below query.
Query :
select USER_ID,USERNAME from DBA_USERS where USER_ID = ‘3430’;
These are few queries with using which you can find out the Username for particular user. I hope you like this article. If you like this article or if you have any issues with same kindly comment in comments section.