Oracle Session and Its Properties

# What is the difference between v$, gv$, x$ v_$ and gv_$ ?
v$ - these objects are synonyms not view
gv$ - these objects are also synonyms where g stand for "global" it contains some extra columns for RAC information.
v_$ - these are the actual views where v$ synonyms have created from it.
gv_$ - these are also same actual view which contains more information about RAC
x_$ - there are the actual X$ structure Tables which are mapped to v$ views


# V$ View which stores all the information of Dynamic view
v$fixed_view_definition
v$fixed_table

# Set a session to perform activity behalf of another user if you are login as sys/system
ALTER SESSION SET CURRENT_SCHEMA=WIRES;


# Find out your session information 
SELECT SYS_CONTEXT('USERENV', 'SID') FROM DUAL; -- SYS_CONTEXT funtion have multiple parameter as our requirment
SELECT USERENV('SID') "SID" FROM DUAL;
select p.pid,p.spid,p.pname,p.username,p.serial#, p.program,s.username,s.logon_time from v$process p, v$session s where s.paddr=p.addr;
select sid, serial# from v$session s where paddr = (select addr from v$process where spid = 23048);

# To Find Current running statement of Session
set pagesize 50000
set linesize 30000
set long 500000
set head off

select sesion.sid, sql_text from v$sqltext sqltext, v$session sesion
where sesion.sql_hash_value = sqltext.hash_value
and sesion.sql_address = sqltext.address;
and sesion.sql_id = sqltext.sql_id;
and sesion.username is not null order by sqltext.piece;

select a.sid, a.username,b.sql_id, b.sql_fulltext 
from v$session a, v$sql b 
where a.sql_id = b.sql_id 
and a.status = 'ACTIVE' and a.username = 'KUMARA45';

select s.username USername, substr(sa.sql_text,1,54) TEST_DATA
from v$process p, v$session s, v$sqlarea sa
where p.addr=s.paddr
and s.username = 'SIEBEL';
and s.sql_address=sa.address(+)
and sa.sql_text like '%DEL%'
and s.sql_hash_value=sa.hash_value(+);


# Find out the Locked Session Details of user
select substr(a.os_user_name,1,15) "OS_User"
,substr(a.oracle_username,1,8) "DB_User"
,substr(b.owner,1,8) "Schema"
,substr(b.object_name,1,30) "Object_Name"
,substr(b.object_type,1,10) "Object_Type"
,substr(c.segment_name,1,15) "RBS"
,substr(d.used_urec,1,12) "#_of-UndoRecords"
,e.sid,e.serial#
from v$locked_object a
,dba_objects b
,dba_rollback_segs c
,v$transaction d
,v$session e
where a.object_id =  b.object_id
and a.xidusn  = c.segment_id
and a.xidusn  = d.xidusn
and a.xidslot = d.xidslot
and d.addr    = e.taddr;

SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,
b.object_id,substr(b.object_name,1,40) object_name,sysdate
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id;

select c.owner,c.object_name,c.object_type,b.sid,b.serial#,b.status,b.osuser,b.machine
from v$locked_object a , v$session b, dba_objects c
where b.sid = a.session_id and a.object_id = c.object_id;

select event,p1,p2,p3 from v$session_wait where wait_time=0 and event='enqueue';

# Find out the long running session
set lines 200
col "Index Operation" for a60 trunc
col "ETA Mins" format 999.99
col "Runtime Mins" format 999.99

select sess.sid as "Session ID", sql.sql_text as "Index Operation",
longops.totalwork, longops.sofar,
longops.elapsed_seconds/60 as "Runtime Mins",
longops.time_remaining/60 as "ETA Mins"
from v$session sess, v$sql sql, v$session_longops longops
where
sess.sid=longops.sid
and sess.sql_address = sql.address
and sess.sql_address = longops.sql_address
and sess.status  = 'ACTIVE'
and longops.totalwork > longops.sofar
and upper(sql.sql_text) like '%INDEX%'
order by 3, 4;