Oracle UTL_ORAMTS
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Header
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.2 to 12.2.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Also important ... there are rumors on the web about SPARC and Solaris being discontinued. Welcome to the age of fake news. There is absolutely not a byte of truth to these irresponsible rumors.
Purpose This package is not built by default during installation but rather by running the oramtsadmin.sql also located in the oramts/admin directory: Do not build this package with ownership by SYS.

The script creates OraMTS administrator user account on Oracle database server. It schedules database jobs for automatic transaction recovery every one minute.
When the database job is run it uses this package to perform a number of functions including:
  • Checks for unresolved global transactions in the database that are related to MSDTC
  • Information in the transaction identifiers of the in-doubt transactions identifies the computer on which the transaction was started
  • The Oracle MTS Recovery Service on that computer resolves the transaction
  • Schedules post-recovery cleanup every hour
All the above, of course, is Oracle theory. When running the oramtsadmin script it fails miserably as it appears to have never been tested and contains numerous flaws so I recreate it to build the package for testing:
Package Build SQL> create user mtssys identified by mtssys default tablespace users temporary tablespace temp;

User created.

SQL> grant connect, resource, unlimited tablespace, create session to mtssys;

Grant succeeded.

SQL> grant create synonym, create view, create table to mtssys;

Grant succeeded.

SQL> grant select_catalog_role to mtssys;

Grant succeeded.

SQL> grant force any transaction to mtssys;

Grant succeeded.

SQL> grant execute on dbms_job to mtssys;

Grant succeeded.

SQL> grant execute on utl_http to mtssys;

Grant succeeded.

SQL> grant execute on utl_file to mtssys;

Grant succeeded.

SQL> grant create any directory to mtssys;

Grant succeeded.

SQL> grant select on sys.dba_pending_transactions to mtssys;

Grant succeeded.

SQL> grant select on sys.dba_2pc_pending to mtssys;

Grant succeeded.

SQL> grant select, update, delete on sys.pending_trans$ to mtssys;

Grant succeeded.

SQL> grant select, update, delete on sys.pending_sessions$ to mtssys;

Grant succeeded.

SQL> grant select, update, delete on sys.pending_sub_sessions$ to mtssys;

Grant succeeded.

SQL> conn mtssys/mtssys@pdbdev
Connected.

SQL> create table oramts_rmproxy_info 2 (
  2 rmguid VARCHAR2(32) NOT NULL UNIQUE,
  3 protid NUMBER(2),
  4 endpoint VARCHAR2(64),
  5 last_scan_time DATE,
  6 last_oper_time DATE);

Table created.

SQL> create table oramts_pending_transactions as select * from dba_pending_transactions where 1 = 0;

Table created.

SQL> @?/oramts/admin/utl_oramts.sql

SP2-0808: Package created with compilation warnings

SQL> sho err
Errors for PACKAGE UTL_ORAMTS:

LINE/COL ERROR
-------- -----------------------------------------------------------
1/1 PLW-05018: unit UTL_ORAMTS omitted optional AUTHID clause;
default value DEFINER used

SQL> @?/oramts/admin/prvtoramts.plb

SP2-0810: Package Body created with compilation warnings

SQL> sho err
Errors for PACKAGE BODY UTL_ORAMTS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PLW-05021: exception UNKNOWN_PROTOCOL does not have a pragma EXCEPTION_INIT

0/0 PLW-05021: exception UNKNOWN_FGT_OUTCOME does not have a pragma EXCEPTION_INIT

0/0 PLW-05021: exception UNKNOWN_TXN_OUTCOME does not have a pragma EXCEPTION_INIT

0/0 PLW-05021: exception MALFORMED_ADDRESS does not have a pragma EXCEPTION_INIT

88/26 PLW-07203: parameter 'XID' may benefit from use of the NOCOPY compiler hint

90/48 PLW-07203: parameter 'ENDPOINT_ADDR' may benefit from use of the NOCOPY compiler hint

93/6 PLW-07203: parameter 'IPADDR' may benefit from use of the NOCOPY compiler hint

94/6 PLW-07203: parameter 'PORTNUM' may benefit from use of the NOCOPY compiler hint

131/11 PLW-06009: procedure "GET_PROTOCOL" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

167/11 PLW-06009: procedure "GET_ENDPOINT" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

175/26 PLW-07203: parameter 'XID' may benefit from use of the NOCOPY compiler hint

220/7 PLW-06009: procedure "RECOVER_AUTOMATIC" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

291/8 PLW-06009: procedure "SHOW_INDOUBT" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

307/7 PLW-06009: procedure "SHOW_INDOUBT" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

331/35 PLW-07204: conversion away from column type may result in sub-optimal query plan

331/37 PLW-07204: conversion away from column type may result in sub-optimal query plan

333/32 PLW-07204: conversion away from column type may result in sub-optimal query plan

333/49 PLW-07204: conversion away from column type may result in sub-optimal query plan

333/52 PLW-07204: conversion away from column type may result in sub-optimal query plan

362/8 PLW-06009: procedure "FORGET_RMS" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

373/7 PLW-06009: procedure "FORGET_RMS" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

394/7 PLW-06009: procedure "SET_TRACE_ON" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

408/7 PLW-06009: procedure "SET_TRACE_OFF" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

483/22 PLW-06009: procedure "RECOVER_DBA_2PC_PENDING" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

491/14 PLW-06009: procedure "RECOVER_DBA_2PC_PENDING" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

536/10 PLW-06009: procedure "RECOVER_DBA_PENDING_TRANS" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

549/7 PLW-06009: procedure "RECOVER_DBA_PENDING_TRANS" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

590/35 PLW-07202: bind type would result in conversion away from column type

590/37 PLW-07202: bind type would result in conversion away from column type

595/21 PLW-07202: bind type would result in conversion away from column type

595/23 PLW-07202: bind type would result in conversion away from column type

635/8 PLW-06009: procedure "RECOVER_TRANSACTION" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR

641/2 PLW-06002: Unreachable code
665/48 PLW-07203: parameter 'ENDPOINT_ADDR' may benefit from use of the NOCOPY compiler hint

697/6 PLW-07203: parameter 'IPADDR' may benefit from use of the NOCOPY compiler hint

698/6 PLW-07203: parameter 'PORTNUM' may benefit from use of the NOCOPY compiler hint

809/4 PLW-05005: subprogram FORGET_RM_HTTP returns without value at line 861

829/11 PLW-07206: analysis suggests that the assignment to 'FORMATID' may be unnecessary

881/7 PLW-06009: procedure "DO_TRACE" OTHERS handler does not end in RAISE or RAISE_APPLICATION_ERROR
AUTHID DEFINER
Dependencies
DBA_2PC_PENDING DBMS_TRANSACTION UTL_FILE
DBA_PENDING_TRANSACTIONS DBMS_UTILITY UTL_HTTP
DBMS_OUTPUT ORAMTS_PENDING_TRANSACTIONS UTL_RAW
DBMS_SQL ORAMTS_RMPROXY_INFO  
Documented No
First Available The package header script appears to have been created in 2007 but the admin script that creates the user and builds the package is new to 12c.
Security Model This package should be created using the oramtsadmin.sql script and the package will be owned by the ORAMTS administrator the script creates.
Source {ORACLE_HOME}/oramts/admin/utl_oramts.sql
Subprograms
 
FORGET_RMS
forget all resource managers that have no indoubt transactions utl_oramts.forget_rms;
exec utl_oramts.forget_rms;
 
GET_ENDPOINT
Given a branch qualifier extracts the endpoint utl_oramts.get_endpoint(brqual IN VARCHAR2) RETURN VARCHAR2;
SQL> SELECT utl_oramts.get_endpoint('ZZYZX')
2 FROM dual;

UTL_ORAMTS.GET_ENDPOINT('ZZYZX')
----------------------------------------------
unknown
 
GET_PROTOCOL
Given a branch qualifier extracts the protocol id utl_oramts.get_protocol(brqual IN VARCHAR2) RETURN VARCHAR2;
SQL> SELECT utl_oramts.get_protocol('ZZYZX')
  2 FROM dual;

UTL_ORAMTS.GET_PROTOCOL('ZZYZX')
----------------------------------------------
unknown
 
RECOVER_AUTOMATIC
Entry point for recovery job utl_oramts.recover_automatic;
exec utl_oramts.recover_automatic;
==============================
recovered transactions

PL/SQL procedure successfully completed.
 
SET_OBFUSCATION_OFF
Set xid obfuscation off utl_oramts.set_obfuscation_off;
exec utl_oramts.set_obfuscation_off;
 
SET_OBFUSCATION_ON
Set xid obfuscation on utl_oramts.set_obfuscation_on;
exec utl_oramts.set_obfuscation_on;
 
SET_TRACE_CONSOLE_OFF
Set trace to console off utl_oramts.set_trace_console_off;
exec utl_oramts.set_trace_console_off;
 
SET_TRACE_CONSOLE_ON
Set trace to console on utl_oramts.set_trace_console_on;
exec utl_oramts.set_trace_console_on;
 
SET_TRACE_OFF
Set debug trace off utl_oramts.set_trace_off;
exec utl_oramts.set_trace_off;
 
SET_TRACE_ON
Set debug trace on utl_oramts.set_trace_on
exec utl_oramts.set_trace_on;
 
SHOW_INDOUBT
List all in-doubt Micosoft Transaction Server related transactions utl_oramts.show_indoubt;
exec utl_oramts.show_indoubt;
=========================================
currently indoubt transactions

PL/SQL procedure successfully completed.

Related Topics
Packages
What's New In 12cR1
What's New In 12cR2

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2017 Daniel A. Morgan All Rights Reserved