SQL for RAC db

RAC sql RAC Instances, DB instances, instance status, List of all instances, with startup time, up till, host load. col host_name for a16 select i.instance_number, i.host_name, i.instance_name, to_char(startup_time, 'DD Mon YYYY HH24:Mi') startup_time, floor(sysdate - startup_time) uptill, i.status, round(o.value, 3) value from gv$instance i, gv$osstat o where i.instance_number = o.inst_id and o.stat_name = 'LOAD' order by instance_number; -- e.g. INSTANCE_NUMBER HOST_NAME INSTANCE_NAME STARTUP_TIME UPTILL STATUS VALUE --------------- ---------------- ---------------- -------------------------- ---------- ------------ ---------- 1 proddbx00101 dex011 12 Feb 2018 02:52 247 OPEN 15.979 2 proddbx00102 dex012 12 Feb 2018 05:25 247 OPEN 18 3 proddbx00103 dex013 12 Feb 2018 07:23 247 OPEN 21.59 4 proddbx00104 dex014 12 Feb 2018 09:37 247 OPEN 19.079

Current Transaction for RAC set line 250 col "username_sid_inst" for a35 select s.username||'('||s.sid||', '||s.inst_id||')' username_sid_inst,s.sid,t.start_time, t.used_ublk as "Block Count", round(t.used_ublk*8192/1024/1024,2) Size_MB from gv$transaction t,gv$session s where s.saddr=t.ses_Addr and t.inst_id=s.inst_id order by start_time;

Session Wait for RAC set line 250 col username for a24 col event for a45 col 'FileId# Obj#' for a16 select s.inst_id, s.username, s.row_wait_file# ||', '|| s.ROW_WAIT_OBJ# as "FileId# Obj#", s.sql_hash_value, sw.event, sw.wait_time, sw.seconds_in_wait, sw.state from gv$session s, gv$session_wait sw where s.sid = sw.sid and s.inst_id = sw.inst_id and sw.wait_time != 0 order by s.inst_id, s.username;

Sessions connected for RAC -- Group by User (Summary) select s.username, count(*) from gv$session s group by s.username order by s.username; -- Group by Instance, User select s.inst_id, s.username, count(*) from gv$session s group by s.inst_id, s.username order by s.inst_id, s.username;

Locks (Holder/Waiter) for RAC column sess format A20 select substr(DECODE(request,0,'Holder: ','Waiter: ')||sid||' ('||inst_id||')',1,18) sess, id1, id2, lmode, request, type, to_char(to_date(ctime,'sssss'),' hh24:mi:ss') time_h_m_s from gv$lock where (id1, id2, type) IN (select id1, id2, type from gv$lock where request>0) order by id1, request, ctime desc;