Saturday, April 09, 2011

ORACLE : who is connected to My Oracle Instance

You have a DB you call your own, but you cant seem to drop some users eventhough you have stopped all services you were running which might have connected to your Oracle Instnace,

Well they (Apps or users) can’t hide any longer … The Oracle’s best Spy is on your side – here is the SQL which can bring them to the open. ( you need to be the SysDBA to do this though )

1. set linesize 30000 – to make sure everything appears in a single line

2. SQL> select username, sid, serial#, status, osuser, process, machine, terminal, program, logon_time from v$session where username is not null and username not in (‘SYSTEM’,'SYS’,'DBSNMP’);

- All the agents are displayed in a table with their USERNAME, SID,SERIAL#,STATUS,OSUSER,PROCESS,MACHINE,TERMINAL,PROGRAM
you have to decide if you will shoot (force kill ) or negotiate ( stop the services in the machines they are running in )

No comments: