Find SQL from SID:
SELECT s.sid, s.serial#, st.sql_text sql_text
FROM v$session s, v$sql st, v$process p
WHERE s.sql_hash_value = st.hash_value
AND s.sql_address = st.address
AND s.paddr = p.addr
and ( s.sid='&which_sid' )
To the full SQL Text:
select x.sql_text from v$session s,v$sqltext x
where s.sql_address=x.address and s.sid = &which_sid order by sid, piece asc;
Find the Session details :
SELECT s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status,
p.program, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.spid IN (&OS_PROCESS_ID);
To set the Time to in a required format:
alter session set nls_date_format='DD:MON:YYYY:HH24:MI:SS';
Then execute the required sql.
To Monitor the Progress of RMAN Backup:
SELECT sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "% Complete"
FROM v$session_longops
WHERE opname LIKE 'RMAN:%'
AND opname NOT LIKE 'RMAN: aggregate%';
