How to clear/kill all locks held on one database package in Oracle

In scenarios where users are not able to access a package due to locks held on it,below pl/sql will help clear those sessions


declare
cursor c1 is
SELECT a.osuser,a.username,a.action, a.sid, a.serial# sl,b.spid, a.status,a.last_call_et,to_char(logon_time,'DD-MON HH24: MI:SS') logon_time, a.machine,
a.program FROM v$session a,v$process b WHERE a.paddr=b.addr and sid in (select session_id from dba_dml_locks where name ='Package Name')
and osuser not in ('list of excluded_users')
and sid = 123;

    L_QRY       varchar2(3000);
    L_ID          varchar2(3000);
begin
    for i in c1
    loop
        L_ID := i.sid||','||i.sl;
        dbms_output.put_line('Loop starts for :'||L_ID);
        L_QRY := 'alter system disconnect session '''||L_ID||' immediate''''';
        dbms_output.put_line('L_QRY :'||L_QRY);
    
        execute immediate L_QRY;
    end loop;
exception
    when others then
    dbms_output.put_line('ERRM:'||SQLERRM);
end;

Query to find user sessions using particular db objects

SELECT a.osuser,a.username,a.action, a.sid, a.serial#,b.spid, a.status,a.last_call_et,to_char(logon_time,'DD-MON HH24: MI:SS') logon_time, a.machine,
a.program FROM v$session a,v$process b
WHERE a.paddr=b.addr and sid in (select session_id from dba_ddl_locks where name =upper('DB_OBJECT_NAME'))

Query to check the amount of archive generation in database

Archive generated on day to Day basis :

select trunc(first_time, 'DD') , count(*) from v$loghist
group by trunc(first_time, 'DD') order by 1 desc; 


Archive generated on day to Hourly basis :

select trunc(first_time, 'HH') , count(*) from v$loghist group by trunc(first_time, 'HH') order by 1 desc

Queries to check locks in the Oracle Database

User who is blocking :

SELECT s1.username || '@' || s1.machine
    || ' ( SID=' || s1.sid || ' )  is blocking '
    || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
    FROM v$lock l1, v$session s1, v$lock l2, v$session s2
    WHERE s1.sid=l1.sid AND s2.sid=l2.sid
    AND l1.BLOCK=1 AND l2.request > 0
    AND l1.id1 = l2.id1
    AND l1.id2 = l2.id2


Transaction Lock types and query being executed by those sessions:

SELECT s.inst_id,
NVL (s.username, 'Internal') "Database User",m.SID,s.serial#,p.spid "DB OS Process",m.TYPE,
DECODE (m.lmode,0, 'None',1, 'Null',2, 'Row Share',3, 'Row Excl.',4, 'Share',5, 'S/Row Excl.',6, 'Exclusive',lmode, LTRIM (TO_CHAR (lmode, '990')) ) "Lock Type",
DECODE (m.request,0, 'None',1, 'Null',2, 'Row Share',3, 'Row Excl.',4, 'Share',5, 'S/Row Excl.',6, 'Exclusive',request, LTRIM (TO_CHAR (m.request, '990')) ) "Lock Request",
DECODE (command,0, 'None', DECODE (m.id2,0, dusr.username || '.' || SUBSTR (dobj.NAME, 1, 30),'Rollback Segment')) "Object",s.machine "Application Server",s.process "Apps OS process",
m.ctime,NVL (NVL (usr.description, s.action),'Database Session') "Online User,Concurrent",NVL (fnd.responsibility_name, s.module) "Responsibility,Module",fnd.user_form_name "Form Name",
SQL.sql_text "Statement"
FROM gv$session s, gv$lock m, gv$process p, apps.fnd_form_sessions_v fnd, apps.fnd_user usr, gv$sqlarea SQL,dba_users dusr, SYS.obj$ dobj
WHERE m.id1 IN (SELECT il.id1 FROM gv$lock il WHERE il.request <> 0)
AND m.SID = s.SID AND s.paddr = p.addr AND s.inst_id = p.inst_id AND SQL.inst_id(+) = s.inst_id AND SQL.address(+) = s.sql_address AND SQL.hash_value(+) = s.sql_hash_value
AND s.username != 'SYS' AND m.lmode != 4 AND fnd.audsid(+) = s.audsid AND m.inst_id = s.inst_id AND fnd.user_name = usr.user_name(+) AND fnd.user_id = usr.user_id(+)
AND dobj.obj#(+) = DECODE (m.id2, 0, m.id1, 1) AND dusr.user_id(+) = dobj.owner# ORDER BY m.id1, m.request ASC, m.SID;