How to Check Users and Database Usage in Oracle 12c

By Chris Ruel, Michael Wessler

Knowing what is going on within the Oracle 12c database is part of your job as a database administrator (DBA). You should regularly log in to your database to understand who is using it, what normal usage looks like, and whether anything unusual is occurring. Developing a baseline understanding of your database usage greatly helps you in your other DBA responsibilities.

If you check on your users on a regular basis, you’ll be able to recognize the most active users. You can also find the following issues:

  • Stale and abnormally long sessions

  • Login abuse, such as people sharing accounts and unauthorized logins

How to find stale sessions in Oracle 12c

Stale sessions have been logged in to the system for a long time, and no one has done anything with them. Getting rid of stale sessions can help control resource usage.

Everyone’s system is different, so you have to rely on some of your knowledge to decide whether the absence of activity is abnormal. To get a list of logged-in users and the last time they issued a command, connect to the database as a DBA user and type the following:

<select sid, serial#, username, last_call_et
from v$session
where username is not null;>

You might see something like this:

--- ------- ---------------- ------------
 12  9853 SYSMAN           3
 14  6552 HR             0
 56   42 DBSNMP           6
112  59271 SYSTEM          160
 65  23451 MPYLE         743160
 98  8752 CRM             1
 32  4551 CRM             3
 45  16554 HR             36
119  9812 KHANR          36522

MPYLE and KHANR are the two potentially bothersome accounts in this example. The LAST_CALL_ET column output data has the number of seconds since there was any activity. MPYLE has been inactive for over eight days! KHANR is a little less alarming at about 10 hours, but nonetheless, that would most likely require some explanation.

Note that the command uses the WHERE clause USERNAME IS NOT NULL. Oracle internal processes show up in this list as unnamed users, and you don’t want those getting in the way of evaluation. Also, this example includes identification columns SID and SERIAL#. Together, these two columns uniquely identify a session within the database.

If you see a session you want to get rid of, type the following:

<alter system kill session '65,23451';>

You should see this:

System altered.

Before you go killing sessions that have long periods of inactivity, check with application folks to determine whether a connection is part of a connection pool that just hasn’t been used for a while. Usually, removal is safe if it isn’t an application ID or if you can identify the user. Do your research!

How to police for login abuse and unauthorized logins in Oracle 12c

People everywhere are abusing their login privileges, and such abuse is a significant security problem. Unfortunately, it’s usually people higher up in the application chain of command who tend to have more privileges giving out their login ID to subordinates to help with work. In other cases, co-workers decide to share a login ID because an account became locked or someone forgot his or her password.

Track this abuse down by comparing the database login ID with the OS login ID. Oracle tracks both. Type the following to see all the users connected with both IDs:

<select sid, serial#, username, osuser, program
from v$session
where username is not null;>

You might see something like this:

---------- ---------- ---------- ---------- -----------------
    112    3741 MPYLE   MPYLE   sqlplusw.exe
    122    3763 MPYLE   RKHAN   sqlplusw.exe
    115    9853 SYSMAN   oracle   OMS
    122   35878 HR     HRAPP   sqlplus@classroom
    124     4 DBSNMP   oracle   emagent@classroom

MPYLE has given his login ID to RKHAN (or at least the evidence suggests that). Evaluate this information carefully and do a thorough investigation. Take appropriate measures if there’s been a violation of your security policy.