We help IT Professionals succeed at work.

How Do I know what applications are connecting to my oracle database?

mishradba
mishradba asked
on
I have to know what different applications are using my database in oracle.
Comment
Watch Question

Top Expert 2011

Commented:
- query to v$session
Top Expert 2011

Commented:
- the following query to v$session will return the username and application currently accessing the db. you need to login using 'system' or 'sys as sysdba' to query the view:

SELECT a.username, a.program, a.logon_time
FROM v$session a
WHERE a.type = 'USER';
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
The program column in v$session may or may not contain information about what is accessing the database.

Some applications do not provide a signature.

This is also for currently connected sessions.


I'm not aware of a way to know ALL possible applications.

You can capture some host information with SYS_CONTEXT and a logon trigger but even with this I don't think you can get ALL.

Greg CloughSenior Oracle DBA
Commented:
I'd go for a logon trigger as suggested by slightvw, but if you want to go "old-school BOFH', then:

1) Check V$SESSION for all currently connected sessions, and track down who is using them.
2) Issue new passwords for these accounts, and get the application owner to update them.

...then to cleanup anyone who isn't authorized:

1) Change all schema passwords to all accounts not done in the previous block (including SYS/SYSTEM)
2) Change the "oracle" unix password, to block off that avenue of access
3) Disconnect any unauthorized accounts by killing the session (alter system kill session 'xxx,yyy';)
4) Wait for people to complain, then reset their password once they promise not to share it

Obviously, this is the nasty way to find out who's using your database, and I wouldn't recommend it in most circumstances.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
>>and I wouldn't recommend it in most circumstances.

I wouldn't recommend it at all.

and it will likley will not work that well.

Many apps 'share' accounts.  I can have a financial app, say peoplesoft.  It uses a standard schema account.

Now it I bought a reporting package, say Crystal Reports, I would likely use the schema account from Peoplesoft to run my reports.

Two apps, single user.

Also checking v$sesssion is, as you mentioned, currently connected only.  It will likely never catch the once-a-month type apps.

Another reason to not use that method going back to the Peoplesoft line, changing the password at the database level and not though the software is a bad idea.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.