Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 
 

dbms_transaction Tips

Oracle Database Tips by Donald BurlesonSeptember 6, 2015


Commonly used SQL commands like commit, rollback, set transaction read only, and alter session advise commit can also be used inside procedures through the dbms_transaction  package.  Its purpose is to manage distributed transactions using procedures and functions of the package. The dbms_transaction  package allows us to commit, rollback, purge or create savepoints to transactions. Created automatically since Oracle 7.3.4 by the dbmstrns.sql script, the dbms_transaction package is created under the sys schema and a public synonym is also created, granting the execute privilege to the public.

 

There are more than fifteen procedures and functions.  Rather than describing each one, we will give examples utilizing some of the more important ones. An easy way to find the transaction ID and the current undo segment being used is by running the local_transaction_id function as shown here:

 

set serveroutput on
declare
v_local_tran_id varchar2(40);
begin
 --Start a read/write transaction
 dbms_transaction.read_write;
 select
    dbms_transaction.local_transaction_id
 into
    v_local_tran_id
 from
    dual;
 dbms_output.put_line('My local transaction id is: '||v_local_tran_id);
end;
/

 

My local transaction id is: 7.9.1470

SQL> 

select

 name

from

 v$rollname

where

 usn=7;

 

NAME

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

_SYSSMU7_2224510368$

 

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.

 

<  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.


                    









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.

 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster