Oracle Database Discussions

SQL> rollback force '1.5.550355';
 
rollback force '117.28.39057'
 
ORA-02058: no prepared transaction found with ID 1.5.550355

SQL> exec admin.admin_dba_main.KILL_ORPH_2PHASE_COMMITS();  -->my package name
 
begin admin.admin_dba_main.KILL_ORPH_2PHASE_COMMITS(); end;
 
ORA-01031: insufficient privileges -->this is a weird one as I am logged in as a SYSDBA user
ORA-06512: at "SYS.DBMS_TRANSACTION", line 88
ORA-06512: at "ADMIN.ADMIN_DBA_MAIN", line 44
ORA-02058: no prepared transaction found with ID 1.5.550355
ORA-06512: at line 2

CREATE OR REPLACE PACKAGE ADMIN."ADMIN_DBA_MAIN" IS
  -- CURSORS **************************************************************************
  CURSOR cur_2pc IS
    SELECT p.local_tran_id FROM DBA_2PC_PENDING p;

  -- KILL_ORPH_2PHASE_COMMITS ****************************************************************
  PROCEDURE KILL_ORPH_2PHASE_COMMITS(inLocal_tran_id IN varchar2 :=NULL);

END ADMIN_DBA_MAIN;

CREATE OR REPLACE PACKAGE BODY ADMIN."ADMIN_DBA_MAIN" IS

  -- KILL_ORPH_2PHASE_COMMITS ****************************************************************
  -- Will clean orphaned processes foun in dba_2pc_pending view.
  -- Raman Gill
  PROCEDURE KILL_ORPH_2PHASE_COMMITS(inLocal_tran_id IN varchar2 :=NULL) is
    tsql            VARCHAR2(256);
    v_local_tran_id varchar2(22 byte);
  
  BEGIN
    IF inLocal_tran_id is not null then
    
      tsql := 'ROLLBACK FORCE ''' || inLocal_tran_id || '''';
      dbms_output.put_line(tsql);
    
      EXECUTE IMMEDIATE tsql;
      commit;
      RETURN;
    END IF;
    
    --open cursor
    open cur_2pc;
  
    LOOP
      --fetch cursor data
      fetch cur_2pc
        into v_local_tran_id;
    
      exit when cur_2pc%NOTFOUND;
      BEGIN
        tsql := 'ROLLBACK FORCE ''' || v_local_tran_id || '''';
        dbms_output.put_line(tsql);
      
        EXECUTE IMMEDIATE tsql;
      EXCEPTION
        WHEN OTHERS THEN
          dbms_transaction.purge_lost_db_entry('''' || v_local_tran_id || '''');
          commit;
          CLOSE cur_2pc;
      END;
    END LOOP;
    CLOSE cur_2pc;
  
  END KILL_ORPH_2PHASE_COMMITS;