Problem solve
Get help with specific problems with your technologies, process and projects.
Getting SID and SERIAL# without V_$SESSION
I need to know the SID and SERIAL# when I running a stored procedure, but I can't use V$SESSION. Do you know a function or package to retrieve this information?
If you can create the following view (or have the DBA it create for you), it will serve the purpose.
create view my_session_info
as
select * from V$session where audsid = userenv('sessionid');
grant select on my_session_info to public;
This view must be created by the SYS user so everyone can see their session. You would be able to use that grant and view in your function or procedure as well.
So in your procedure, you'd simply SELECT sid, serial# FROM my_session_info; to get what you need.
There are other ways to gather this info but all will require either granting access to v_$session (not the view v$session!) or having a DBA (logged in as SYS) write you a procedure or view to access the info you want and then granting privileges to PUBLIC or named users that you want to be able to execute either that procedure or view (just like this example).
For More Information
- Dozens more answers to tough Oracle questions from Karen Morton are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical Oracle and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
