Home

Listing (and optionally unlocking) all locked objects on a Siebel server

Mon, 2012.01.09 - 17:56 — müzso
Siebel Tools makes it not easy to create a list of all locked objects. You've to do it for each potential object type and unlocking multiple objects is not easy either. Eg. you can only unlock objects that you've locked. If some developer left an object locked, you either know her/his password or you don't. In the latter case you've take over the user and log in with it to unlock the objects.

I've created a PL/SQL procedure (anonymous block, if you like) for Oracle-based Siebel servers to list all locked objects with all the lock properties (username + date) and optionally unlock all locked objects of a user too. Of course, Oracle does not support any direct SQL on a Siebel database, so use this at your own risk.

-- Find all locked objects.
-- Optionally filter by username.
DECLARE
  TYPE tVcArr IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;
 
  --------------------
  -- config section --
  --------------------
  -- set this if you want the results via htp.p() (instead of dbms_output.put_line())
  bHtpOutput BOOLEAN := FALSE;
  -- set this if you want to filter on username
  sUsername VARCHAR2(30) := NULL;
  -- set this to automatically unlock objects
  -- (works only if sUsername is specified too,
  --  this restriction is meant as a fail-safe)
  bUnlock BOOLEAN := FALSE;
  -- set this if you just want to see what is being executed
  -- (if both bUnlock and this are set, unlocking is not executed, only the
  --  SQL is printed)
  bPrintSQL BOOLEAN := FALSE;
 
  sUserID VARCHAR2(30);
  aTabNames tVcArr;
  aColNames tVcArr;
  nIdxTab NUMBER;
  nIdxCol NUMBER;
  sSQL VARCHAR2(32767);
  sCond VARCHAR2(32767);
  sTab VARCHAR2(30) := NULL;
  sTmp VARCHAR2(32767);
  sCol VARCHAR2(30);
  sRowid VARCHAR2(30);
  aTabColNames tVcArr;
  bObjLockedBy BOOLEAN := FALSE;
  bLockedBy BOOLEAN := FALSE;
  bObjLockedFlg BOOLEAN := FALSE;
  bLockedFlg BOOLEAN := FALSE;
  sObjLockedBy VARCHAR2(30);
  sLockedBy VARCHAR2(30);
  bName BOOLEAN := FALSE;
  bTablePrinted BOOLEAN;
  nCur NUMBER;
  nIdx NUMBER;
  nTmp NUMBER;
  PROCEDURE mprint(p_sInput IN VARCHAR2) IS                     
  BEGIN
    IF bHtpOutput THEN
      htp.p(p_sInput);
    ELSE
      dbms_output.put_line(SUBSTR(p_sInput, 1, 255));
    END IF;
  END mprint;
BEGIN
  IF bHtpOutput THEN
    mprint('<PRE>');
  ELSE
    dbms_output.enable(1000000);
  END IF;
 
  IF sUsername IS NOT NULL THEN
    sUsername := UPPER(sUsername);
    BEGIN
      SELECT u.ROW_ID
      INTO sUserID
      FROM S_USER u
      WHERE u.LOGIN = sUsername;
    EXCEPTION WHEN NO_DATA_FOUND THEN
      mprint('No Siebel user with name = ''' || sUsername || ''' was found. Skipping filtering on username.');
    END;
  END IF;
 
  SELECT uo.object_name, utc.column_name
  BULK COLLECT INTO aTabNames, aColNames
  FROM user_tab_columns utc, user_objects uo
  WHERE utc.table_name = uo.object_name
  AND uo.object_type = 'TABLE'
  AND (
    utc.column_name LIKE 'OBJ\_LOCKED\_%' ESCAPE '\'
    OR utc.column_name LIKE 'LOCKED\_%' ESCAPE '\'
    OR utc.column_name = 'NAME'
    OR utc.column_name = 'ROW_ID'
  )
  AND (uo.object_name != 'S_PROJECT' OR utc.COLUMN_NAME != 'OBJ_LOCKED_FLG')
  ORDER BY 1, DECODE(utc.column_name, 'NAME', 1, 'ROW_ID', 2, 3), utc.column_name;
  aTabNames(aTabNames.COUNT + 1) := 'X';
  aColNames(aColNames.COUNT + 1) := 'X';
 
  nIdxTab := aTabNames.FIRST;
  WHILE nIdxTab IS NOT NULL LOOP
    IF sTab IS NOT NULL AND sTab != aTabNames(nIdxTab) AND aTabColNames.COUNT > 0 THEN
      sCond := NULL;
      bName := FALSE;
      bLockedBy := FALSE;
      bObjLockedBy := FALSE;
      nIdxCol := aTabColNames.FIRST;
      WHILE nIdxCol IS NOT NULL LOOP
        IF aTabColNames(nIdxCol) = 'OBJ_LOCKED_FLG' OR aTabColNames(nIdxCol) = 'LOCKED_FLG' THEN
          IF aTabColNames(nIdxCol) = 'OBJ_LOCKED_FLG' THEN
            bObjLockedFlg := TRUE;
          ELSE
            bLockedFlg := TRUE;
          END IF;
          IF sCond IS NULL THEN
            sCond := 't.' || aTabColNames(nIdxCol) || ' = ''Y''';
          ELSE
            sCond := sCond || ' OR t.' || aTabColNames(nIdxCol) || ' = ''Y''';
          END IF;
          nTmp := nIdxCol;
          nIdxCol := aTabColNames.NEXT(nIdxCol);
          aTabColNames.DELETE(nTmp);
        ELSE
          IF aTabColNames(nIdxCol) = 'OBJ_LOCKED_BY' OR aTabColNames(nIdxCol) = 'LOCKED_BY' THEN
            IF aTabColNames(nIdxCol) = 'OBJ_LOCKED_BY' THEN
              bObjLockedBy := TRUE;
            ELSE
              bLockedBy := TRUE;
            END IF;
            nTmp := nIdxCol;
            nIdxCol := aTabColNames.NEXT(nIdxCol);
            aTabColNames.DELETE(nTmp);
          ELSE
            IF aTabColNames(nIdxCol) = 'NAME' THEN
              bName := TRUE;
            END IF;
            aTabColNames(nIdxCol) := 't.' || aTabColNames(nIdxCol);
            nIdxCol := aTabColNames.NEXT(nIdxCol);
          END IF;
        END IF;
      END LOOP;
      IF sCond IS NOT NULL AND aTabColNames.COUNT > 0 THEN
        IF sUserID IS NOT NULL THEN
          IF (bObjLockedBy OR bLockedBy) THEN
            sCond := sCond || ' AND (1 = 0';
            IF bObjLockedBy THEN
              sCond := sCond || ' OR t.OBJ_LOCKED_BY = ''' || sUserID || '''';
            END IF;
            IF bLockedBy THEN
              sCond := sCond || ' OR t.LOCKED_BY = ''' || sUserID || '''';
            END IF;
            sCond := sCond || ')';
          ELSE
            sCond := NULL;
          END IF;
        END IF;
        IF bObjLockedBy THEN
          aTabColNames(aTabColNames.LAST + 1) := 'u1.LOGIN';
        END IF;
        IF bLockedBy THEN
          aTabColNames(aTabColNames.LAST + 1) := 'u2.LOGIN';
        END IF;
        IF sCond IS NOT NULL THEN
          sTmp := NULL;
          nIdxCol := aTabColNames.FIRST;
          WHILE nIdxCol IS NOT NULL LOOP
            IF sTmp IS NULL THEN
              sTmp := aTabColNames(nIdxCol);
            ELSE
              sTmp := sTmp || ', ' || aTabColNames(nIdxCol);
            END IF;
            nIdxCol := aTabColNames.NEXT(nIdxCol);
          END LOOP;
          sSQL := 'SELECT ' || sTmp || ' FROM ' || sTab || ' t';
          IF bObjLockedBy THEN
            sSQL := sSQL || ' LEFT OUTER JOIN S_USER u1 ON u1.ROW_ID = t.OBJ_LOCKED_BY';
          END IF;
          IF bLockedBy THEN
            sSQL := sSQL || ' LEFT OUTER JOIN S_USER u2 ON u2.ROW_ID = t.LOCKED_BY';
          END IF;
          sSQL := sSQL || ' WHERE (' || sCond || ')';
          IF bName THEN
            sSQL := sSQL || ' ORDER BY t.NAME';
          ELSE
            sSQL := sSQL || ' ORDER BY t.ROW_ID';
          END IF;
          nCur := dbms_sql.open_cursor();
          IF bPrintSQL THEN
            mprint('SQL: ' || sSQL);
          END IF;
          dbms_sql.parse(c => nCur, statement => sSQL, language_flag => dbms_sql.native);
          nIdxCol := aTabColNames.FIRST;
          nIdx := 1;
          WHILE nIdxCol IS NOT NULL LOOP
            dbms_sql.define_column(
              c => nCur,
              position => nIdx,
              column => sSQL,
              column_size => 4000
            );
            nIdx := nIdx + 1;
            nIdxCol := aTabColNames.NEXT(nIdxCol);
          END LOOP;
          nTmp := dbms_sql.execute(c => nCur);
          bTablePrinted := FALSE;
          WHILE dbms_sql.fetch_rows(c => nCur) > 0 LOOP
            IF NOT bTablePrinted THEN
              mprint('Table: ' || sTab);
              mprint('');
              bTablePrinted := TRUE;
            END IF;
            sRowid := NULL;
            sObjLockedBy := NULL;
            sLockedBy := NULL;
            sTmp := NULL;
            nIdxCol := aTabColNames.FIRST;
            nIdx := 1;
            WHILE nIdxCol IS NOT NULL LOOP
              dbms_sql.column_value(
                c => nCur,
                position => nIdx,
                value => sSQL
              );
              IF aTabColNames(nIdxCol) = 'u1.LOGIN' THEN
                sCol := 'OBJ_LOCKED_BY_USERNAME';
                sObjLockedBy := sSQL;
              ELSIF aTabColNames(nIdxCol) = 'u2.LOGIN' THEN
                sCol := 'LOCKED_BY_USERNAME';
                sLockedBy := sSQL;
              ELSE
                sCol := SUBSTR(aTabColNames(nIdxCol), 3);
                IF sCol = 'ROW_ID' THEN
                  sRowid := sSQL;
                END IF;
              END IF;
              IF sUserID IS NULL OR (aTabColNames(nIdxCol) != 'u1.LOGIN' AND aTabColNames(nIdxCol) != 'u2.LOGIN') THEN
                IF sTmp IS NULL THEN
                  sTmp := '  ' || sCol || ' = ' || sSQL;
                ELSE
                  sTmp := sTmp || ', ' || sCol || ' = ' || sSQL;
                END IF;
              END IF;
              nIdx := nIdx + 1;
              nIdxCol := aTabColNames.NEXT(nIdxCol);
            END LOOP;
            mprint(sTmp);
            IF sUserID IS NOT NULL AND bUnlock AND sRowid IS NOT NULL THEN
              sTmp := NULL;
              nIdxCol := aTabColNames.FIRST;
              WHILE nIdxCol IS NOT NULL LOOP
                IF (
                  aTabColNames(nIdxCol) != 'u1.LOGIN'
                  AND aTabColNames(nIdxCol) != 'u2.LOGIN'
                  AND (
                    (aTabColNames(nIdxCol) LIKE 't.OBJ\_LOCKED\_%' ESCAPE '\' AND sObjLockedBy = sUsername)
                    OR (aTabColNames(nIdxCol) LIKE 't.LOCKED\_%' ESCAPE '\' AND sLockedBy = sUsername)
                  )
                ) THEN
                  sCol := SUBSTR(aTabColNames(nIdxCol), 3);
                  IF sTmp IS NULL THEN
                    sTmp := sCol || ' = NULL';
                  ELSE
                    sTmp := sTmp || ', ' || sCol || ' = NULL';
                  END IF;
                END IF;
                nIdxCol := aTabColNames.NEXT(nIdxCol);
              END LOOP;
              IF sTmp IS NOT NULL THEN
                IF sObjLockedBy = sUsername THEN
                  IF bObjLockedFlg THEN
                    sTmp := sTmp || ', OBJ_LOCKED_FLG = ''N''';
                  END IF;
                  IF bObjLockedBy THEN
                    sTmp := sTmp || ', OBJ_LOCKED_BY = NULL';
                  END IF;
                END IF;
                IF sLockedBy = sUsername THEN
                  IF bLockedFlg THEN
                    sTmp := sTmp || ', LOCKED_FLG = ''N''';
                  END IF;
                  IF bLockedBy THEN
                    sTmp := sTmp || ', LOCKED_BY = NULL';
                  END IF;
                END IF;
                sSQL := 'UPDATE ' || sTab || ' SET ' || sTmp || ' WHERE ROW_ID = :1';
                IF bPrintSQL THEN
                  mprint('    Unlock SQL: ' || sSQL || ' (row_id = ' || sRowid || ')');
                ELSE
                  EXECUTE IMMEDIATE sSQL USING sRowid;
                END IF;
              END IF;
            END IF;
          END LOOP;
          dbms_sql.close_cursor(c => nCur);
          IF bTablePrinted THEN
            mprint('');
          END IF;
        END IF;
      END IF;
      aTabColNames.DELETE();
    END IF;
   
    aTabColNames(aTabColNames.COUNT + 1) := aColNames(nIdxTab);
   
    sTab := aTabNames(nIdxTab);
    nIdxTab := aTabNames.NEXT(nIdxTab);
  END LOOP;
 
  IF bHtpOutput THEN
    mprint('</PRE>');
  END IF;
END;