ORACLE SESSIONS MONITORING SQL QUERIES

 SQL>
SELECT SID, SERIAL#,OPNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;

TO find out sql_id for the above sid:
SQL> select sql_id from gv$session where sid='&SID';
To find sql text for the above sql_id:
SQL> select sql_fulltext from gv$sql where sql_id='&sql_id';
To find wait event of the query for which it is waiting for:
SQL>select sql_id, state, last_call_et, event, program, osuser from gv$session where sql_id='&sql_id';     ---------------------------> best query for getting  os user

SELECT s.inst_id,
   s.sid,
   s.serial#,
   p.spid,
   s.username,
   s.program FROM   gv$session s
   JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id;
if object/table locked ora error :
alter session set ddl_lock_timeout=30;
alter system kill session 'sid, serial#';

=============================================
select a.SAMPLE_TIME, a.SQL_OPNAME, a.SQL_EXEC_START, a.program, a.client_id, b.SQL_TEXT
from DBA_HIST_ACTIVE_SESS_HISTORY a, dba_hist_sqltext b --v$sqltext b
where a.SQL_ID = b.SQL_ID and sql_id='&sql_id'
order by a.SQL_EXEC_START asc;
=============================
SELECT SESSION_ID, program, module , event,sql_id ,TIME_WAITED,SESSION_STATE 
FROM  gv$active_session_history where sql_id='&sqlid';
=======================================================
select distinct inst_id,sample_time,time_waited, session_id,session_serial#,sql_id from gv$active_session_history where sql_id='&sqlid'  order by 1 desc;
select distinct inst_id,sid,status,sql_id,sql_plan_hash_value,sql_child_address,sql_exec_id
 from gv$sql_plan_monitor where sql_id='&sql_id' and rownum<=5;
1b3mkpnwf6c9c
5968nvz4bcn7n
========================
SELECT distinct sql_text FROM gv$sql WHERE sql_id='&sql_id';
===================================================
oracle_sql_text_spid.sql
----------------------------------
select s.sql_text,s.sql_fulltext from v$sql s 
where sql_id in (SELECT A.SQL_ID FROM V$SESSION A,V$PROCESS B 
WHERE A.PADDR=B.ADDR AND B.SPID='6495');
=================================================================
select t.sql_id,
    t.sql_text,
    s.executions_total,
    s.elapsed_time_total
from DBA_HIST_SQLSTAT s, DBA_HIST_SQLTEXT t
where t.sql_id=s.sql_id and 
s.snap_id between 9349 and 9351;
or  t.sql_id='&sql_id';
====================================================================
 col username for a15
 col machine for a20
 col event for a20
 col program for a20
 col osuser for a20
 set line 3000
 set pagesize 2000
 SELECT
 Inst_id,
    s.username,
    s.blocking_session blk_sess,
    s.blocking_instance blk_inst,
    s.sid, 
    s.serial#, 
    s.sql_id,
    s.status,
    s.seconds_in_wait waittime,
    s.event,
    s.machine 
 FROM
    gv$session s
 WHERE
    blocking_session IS NOT NULL order by SECONDS_IN_WAIT;
    
****************** getting inactive sessions ***********************************
    select sid,serial#,username,status from gv$session where username='NIMBUS' and status='INACTIVE';
    
***************** details of session ***************************
select distinct inst_id,sid,status,sql_id,sql_plan_hash_value,sql_child_address,sql_exec_id
 from gv$sql_plan_monitor where sql_id='&sql_id' and rownum<=5;
 
select inst_id, status,first_change_time,last_change_time,sql_exec_start,plan_operation,
plan_object_owner,plan_object_name,plan_object_type,plan_time, plan_temp_space, starts,output_rows from gv$sql_plan_monitor 
where sql_id='&sql_id' and rownum<=5;
*************** osusername,machine,termial and program lockwait details ************
select sid,serial#,username,lockwait,status,osuser,machine,terminal,program,
server,final_blocking_session from gv$session where username='NIMBUS';
desc all_objects;
****************ddl details for objects ********************************************
select owner,object_name,object_type,last_ddl_time,status,(select host_name from v$instance) "host" from all_objects where object_name='SUBSCRIPTION';
SELECT ORACLE_USERNAME, OS_USER_NAME,LOCKED_MODE,OBJECT_NAME,OBJECT_TYPE
    FROM V$LOCKED_OBJECT A, DBA_OBJECTS B
    WHERE A.OBJECT_ID=B.OBJECT_ID;
    
select * from dba_ddl_locks;


select column_name,data_type,(select host_name from v$instance) as host from all_tab_columns where table_name='UDAS_DTVREQUEST' ;
********************** kill multiple sessions ****************************
begin 
for x in (select sid,serial#,username,status from gv$session where username='NIMBUS' and status='INACTIVE')
loop
dbms_output.put_line('alter system kill session '||''''||x.sid||', '||x.serial#||''''||';');
end loop;
end;
/