• Arun Kumar

Database Blocked Session Report

Use below script to report both blocking and blocked sessions inside the oracle database

set serveroutput on
BEGIN
dbms_output.enable(1000000);
for do_loop in (select session_id, a.object_id, xidsqn, oracle_username, b.owner owner,
b.object_name object_name, b.object_type object_type
FROM v$locked_object a, dba_objects b
WHERE xidsqn != 0
and b.object_id = a.object_id)
loop
dbms_output.put_line(CHR(10));
dbms_output.put_line('-------------- DATABASE BLOCKED SESSION REPORT --------------');
dbms_output.put_line(CHR(10));
dbms_output.put_line('Blocking Session : '||do_loop.session_id);
dbms_output.put_line('Object Owner.Name: '||do_loop.owner||'.'||do_loop.object_name);
dbms_output.put_line('Object Type      : '||do_loop.object_type);
for next_loop in (select sid from v$lock
where id2 = do_loop.xidsqn
and sid != do_loop.session_id)
LOOP
dbms_output.put_line('Sessions blocked : '||next_loop.sid);
end loop;
end loop;
dbms_output.put_line(CHR(10));
dbms_output.put_line('------------------------ END REPORT ------------------------');
END;
/

block-session-script

59 views

Recent Posts

See All

Temp Tablespace utilization

It is very common that as a DBA you will encounter ‘ORA-1652: unable to extend temp segment’. Handling temporary tablespace is different than permanent tablespaces inside Oracle. On a high level, temp

Estimate flashback destination size

Sometimes application team will ask DBA to enable flashback for x number of days. In such case, a DBA needs to estimate the flashback space required for x number of days in order to store the flashbac

Datafile utilization check

When you want to shrink a datafile, you must always check the single datafile utilization. In case if you shrink datafile more than the used size, it will fail. Below query gives the datafile utilizat

  • Facebook
  • Twitter
  • YouTube
  • Instagram

© 2020 by DBA Genesis

Feedback