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
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

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.