Kto mnie trzyma??

 Problem blokad jest wpisany w naturę  przetwarzania bazodanowego.. Często powstają one w najmniej pożądanej chwili i są w stanie skutecznie  zdezorganizować kluczowe wielkie procesy np zamknięcie miesiąca, złożone raportowanie, przetwarzanie transakcyjne.. Dlatego warto takie sytuacje logować (wykorzystałem wcześniej opisany pakiet  PCKG_LOGGING).
Poniższa funkcja wykrywa blokady da danego obiektu  bazodanowego w określonym schemacie i  zapisuje informacje o blokadach z wykorzystaniem transakcji autonomicznych, nie ingeruje zatem w logikę transakcyjną kodu w którym jest użyta.
Co ciekawego można zrobić z poniższym kodem:

1. dodanie tabeli mapującej loginy użytkowników na adresy poczty elektronicznej  i wysyłanie maili do niesfornych użytkowników przy wykorzystaniu pakietu utl_smtp, warto także w ten sposób poinformować osobę bezpośrednio odpowiedzialną za bieżące przetwarzanie bazodanowe
2. jeśli  DBA opakuje ładnie kill session w procedurę PL/SQL - ubijać sesje tworzące nieporządane blokady

Wymagane uprawnienia - dostęp do widoków systemowych v$session, v$locked_object
CREATE OR REPLACE FUNCTION FDB_IsObjectUsed( pc_ObjectOwner VARCHAR2, pc_ObjectName VARCHAR2 ) RETURN NUMBER
AS
CURSOR C_MAIN IS
    SELECT 'Użytkownik bazodanowy ' || USERNAME || ' w sesji NR ' || SID
        || ' zalogowany do domeny jako '  || OSUSER ||  ' na komputerze ' || MACHINE
        || DECODE ( PROGRAM, NULL, NULL, ' korzystający z programu ' || PROGRAM) AS MSG,
        USERNAME,
        OSUSER 
 FROM v$session
 WHERE
   SID IN
 (SELECT DISTINCT SESSION_ID FROM v$locked_object WHERE OBJECT_ID IN
(SELECT OBJECT_ID FROM DBA_OBJECTS WHERE OBJECT_NAME = pc_ObjectName AND OWNER =pc_ObjectOwner))
AND SID <> sys_context('USERENV', 'SID')
ORDER BY OSUSER;
 vn_Result NUMBER := 0;
 vc_Body VARCHAR2(4000 CHAR);
 vc_LogMessage VARCHAR2( 4000 CHAR);
BEGIN
  vc_LogMessage := 'Następujący użytkownicy blokują obiekt ' ||  pc_ObjectOwner || '.' || pc_ObjectName ||  ': ';
  FOR EL IN C_MAIN
    LOOP   
        vn_Result := vn_Result +1;

           IF vc_Body IS NULL THEN
             vc_Body := vc_Body || EL.MSG;
          ELSE           
              vc_Body := vc_Body || CHR(13) || CHR(10) || EL.MSG;
          END IF;
                      
    END LOOP;
vc_LogMessage := vc_LogMessage || CHR(13) || CHR(10) || vc_Body;
   IF vn_Result > 0 THEN                  
    PCKG_LOGGING.PDB_ERror('Wykryto blokadę na obiekcie ' || pc_ObjectOwner || '.' || pc_ObjectName, vc_LogMessage);
   END IF;
        RETURN vn_Result;   
   EXCEPTION WHEN OTHERS THEN
        RETURN vn_Result;
END;
/