-----------------------------------------------------
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.
< Code 6.4 - dbms_transaction.sql
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:
< Code 6.5 - dbms_transaction_man_purge.sql
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;
All this information may be found in MOSC Note: 401302.1. If more information about how to resolve transaction problems is desired, take a time to read MOSC Note 126069.1.
|
 |
|
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.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|