Handy SQLs for DBA's

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%';