Oracle user list

[Igaduma]

Hi all,

Does anyone know of a quick & dirty way to get a list from all users connected to an oracle database from the commandline ?

I thought I read somewhere that you can enable the userlogin name in the process list, but forgot *where* I read it...
That way it would be fairly easy to find out.

Thanks!

 

[jad]

select username, sid, serial#, process, status
    from v$session
    where username is not null;

might work

 

[Igaduma]

Hi jad,

yeaup, that works from sqlplus.

Now I need to put it in a script which I can call from the cron, mimicks the oracle user & executes that statement in sqlplus & dumps that list in a file in /tmp.

I definatly need to get my boss to sign for a scripting course!

Iga

 

[Igaduma]

Almost there....

Another thing:

When I run this from the oracle crontab will it source the oracle .profile or do I have to tell it somewhere to get all oracle env variables?

Thanks!!

 

[jad]

matters ... if you run it from the oracle users crontab it doesn't source the profile, if you run it from root's crontab and 'su - oracle' then i think it does get the profile ...

however, it's probably worth copying the profile into the script, or '. .profile' in the script.

put the following into a file, chmod +x the file then set crontab to run it whenever you want.

#!/bin/sh
. /home/oracle/.profile
sqlplus /nolog << EOF
connect internal
select username, sid, serial#, process, status
    from v$session
    where username is not null;
EOF

which should work ... hopefully.

 

[Annihilannic]

You might have to escape the $ or the shell will attempt to replace $session with the contents of a variable called session.

[tt] from v\$session [/tt]

should do it. Annihilannic.