Can an Oracle user get a list of its own running sessions without access to v$session?
- by Nick Pierpoint
I have an application that runs a process and I only want one process to run at a time. Some options are:
Use an object lock to prevent subsequent processes running.
This would be fine, but I want the calling session to return immediately and not wait for the running session to complete.
Use a custom Y/N to set whether a process is running or not.
I set a "Y" flag at the start of the process and set it to "N" when it finishes or fails. Also fine but feels like I'm re-inventing the wheel and doesn't feel like the way to go. It also falls short if the running session is killed as the flag stays at "Y".
Use dbms_application_info.set_module
This approach seems the most robust, but if I'm to know there's an existing running process I think I need to be able to query v$session and I don't want this application to have such wide access.
Any ideas?