Re: Session ID

This is a multi-part message in MIME format.

--------------10174576BCB

Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit

Attached are some sql scripts that may be of use.

-- 


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^~~~~....
Rose Technologies

Robert Miller               Tel:  214-532-6558
Oracle Master DBA           Fax:  940-458-5318

1807 N. Elm - Suite 547   email:  rwmiller_at_rosetech.net
Denton Texas  76201
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~^^^^^~~~~....
Any opinions expressed are my own and do not necessarily
represent any client.



--------------10174576BCB

Content-Type: text/plain; charset=us-ascii; name="session.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="session.sql"

clear screen
set pagesize 50
set linesize 132

column audsid format 99999 heading 'Session'
column process format A7   heading 'Unix|Process'
column username format a6  heading 'Oracle|User'
column sid format 999      heading 'SID'
column serial# format 99999 HEADING 'Session|S/N'
column status format A8     heading 'Status'
column machine format A12     heading 'Machine'
column terminal format A12     heading 'Terminal'
column module format A16    heading 'Module'

select userenv('sessionid') CurrentSession from dual;

select 
    audsid,
    process,
    username,
	sid,
	serial#,
 	status,
	machine,
    terminal,
    module
from v$session 
where type <> 'BACKGROUND'
ORDER BY machine;



--------------10174576BCB

Content-Type: text/plain; charset=us-ascii; name="sesstext.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="sesstext.sql"

clear screen
set pagesize 50
set linesize 132

accept sidinput prompt 'Enter the sid...'

column audsid format 99999 heading 'Session'
column process format A7   heading 'Unix|Process'
column username format a6  heading 'Oracle|User'
column sid format 999      heading 'SID'
column serial# format 99999 HEADING 'Session|S/N'
column status format A8     heading 'Status'
column machine format A12     heading 'Machine'
column terminal format A12     heading 'Terminal'
column module format A16    heading 'Module'


select userenv('sessionid') CurrentSession from dual;

select 
    sys.v_$sqltext.sql_text
from  sys.v_$session, sys.v_$sqltext
where sys.v_$session.sid=&sidinput 
and   sys.v_$sqltext.address=sys.v_$session.sql_address
order by sys.v_$sqltext.piece asc;



--------------10174576BCB

Content-Type: text/plain; charset=us-ascii; name="sqltext.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="sqltext.sql"

clear screen
set pagesize 100
set linesize 132

accept sid Prompt 'Enter sid...';


column audsid format 99999 heading 'Session'
column process format A6   heading 'Unix|Process'
column osuser format A8    heading 'Unix|UserId'
column username format a6  heading 'Oracle|User'
column sid format 999      heading 'SID'
column serial# format 99999 HEADING 'Session|S/N'
column status format A8     heading 'Status'
column module format A16    heading 'Module'
column program format A28   heading 'Program'


select 
    B.SQL_TEXT
from v$session A, v$sqltext B
where a.SQL_ADDRESS = B.address
and a.sid=&sid
order by b.piece;
/



--------------10174576BCB

Content-Type: text/plain; charset=us-ascii; name="process.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="process.sql"

rem  this view gives a handy place to identify any associated
rem  unix process with an oracle session id.  Note:  for some
rem  very poor reason, audsid is the title of session_id

rem  rwmiller




select username , 
       audsid   , 
       process  , 
       osuser   
from   sys.v_$session
/



--------------10174576BCB

Content-Type: text/plain; charset=us-ascii; name="unixpro.sql"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="unixpro.sql"

rem  this view gives a handy place to identify any associated
rem  unix process with an oracle session id.  Note:  for some
rem  very poor reason, audsid is the title of session_id

rem  rwmiller




select username , 
       audsid   , 
       process  , 
       osuser   
from   sys.v_$session
/



--------------10174576BCB--