dbms_transaction Tips

The undo segment can be identified by the numbers before the first ?.?.  In the case above, the undo segment number is 7.

-----------------------------------------------------

If the transaction has a value of yes in the column mixed, then use the purge_mixed procedure. This could happen when using commit force or rollback force and one site will commit the transaction while the other site will rollback the transaction. Purge should only be used when the database is lost or has been recreated.

In this next example, how to find and purge lost transactions will be revealed.

conn pkg@dbms

Connected to:

Oracle 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, Oracle Label Security, OLAP, Data Mining,

Oracle Database Vault and Real Application Testing options

SQL>

show

 user

User is "pkg"

SQL>

--Using purge_lost_db_entry

--Query dba_2pc_pending to get transactions ids to be purged
select
   local_tran_id,
   global_tran_id,
   state,
   mixed
from
   dba_2pc_pending;
  
--Now try to purge the transaction using the purge_db_entry procedure
begin
dbms_transaction.purge_lost_db_entry(xid => 'local_tran_id');
end;
/
  
--If you find an error like this one below,
ORA-30019: Illegal rollback Segment operation in Automatic Undo mode
  
--then you need to follow these steps:
commit;

alter session set "_smu_debug_mode" = 4;

begin
dbms_transaction.purge_lost_db_entry('local_tran_id');
end;
/


--Or, if you got this another error below
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "sys.dbms_transaction", line 94
ORA-06512: at line 1


--then you need to follow these steps:
rollback force '7.12.102';

Commit;

alter system set "_smu_debug_mode" = 4;

begin
dbms_transaction.purge_lost_db_entry('local_tran_id');
end;
/

--Another useful queries that generate the purge to the command is below:

select
   'commit force '''||local_tran_id||''';'
from
   dba_2pc_pending;

select
   'exec dbms_transaction.purge_lost_db_entry('''||local_tran_id||''');'
from
   dba_2pc_pending
where
   state='forced commit';

If a transaction is found in a prepared state without an entry in the transaction table, it can be cleaned up manually by using these steps:

conn pkg@dbms

--Manually purge lost entries

--Find the prepared transaction

select

   local_tran_id,

   state

from

   dba_2pc_pending;

LOCAL_TRAN_ID          STATE

---------------------- ----------------

  7.12.102             prepared

--Find the transaction located on a rollback segment
select
   ktuxeusn,
   ktuxeslt,
   ktuxesqn, /* transaction id */
   ktuxesta status,
   ktuxecfl flags
from
   x$ktuxe
where
   ktuxesta!='inactive'
and
   ktuxeusn= 1; -- rollback segment number

--If you don't find any transaction, then you should delete it manually
set transaction use rollback segment SYSTEM;

delete from
   sys.pending_trans$
where
   local_tran_id = '7.12.102';

delete from
   sys.pending_sessions$
where
   local_tran_id = '7.12.102';
  
delete from
   sys.pending_sub_sessions$
where
   local_tran_id = '7.12.102';
commit;

Inside the DBMS Packages The DBMS packages form the foundation of Oracle DBA functionality.  Now, Paulo Portugal writes a landmark book Advanced Oracle DBMS Packages:  The Definitive Reference.  This is a must-have book complete with a code depot of working examples for all of the major DBMS packages.  Order directly from Rampant and save 30%.

Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:   and include the URL for the page.                      Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services   Copyright © 1996 -  2020 All rights reserved by Burleson Oracle ® is the registered trademark of Oracle Corporation.