Rem
Rem $Header: catxpend.sql 28-sep-99.15:11:32 jarnett Exp $
Rem
Rem catxpend.sql
Rem
Rem  Copyright (c) Oracle Corporation 1999. All Rights Reserved.
Rem
Rem    NAME
Rem      catxpend.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      Create view necessary for XA recovery.
Rem
Rem    NOTES
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jarnett     09/24/99 - bug 951528 - fix dba_pending_transactions
Rem    jarnett     09/24/99 - Created
Rem
Rem	DBA_PENDING_TRANSACTIONS
Rem	This view gives information about unresolved transactions (either
Rem	due to failure or co-ordinator has not sent a commit/rollback).
Rem	The view selects information from the pending_trans$ dictionary table
Rem	and the in-memory tables in all instances provided by
Rem	gv$global_transactions. A join is required between pending_trans$
Rem	and pending_sessions$ because the branch_id is stored in 
Rem	pending_sessions$. Since Oracle's lock manager is session based we
Rem	will never have multiple sessions for a given transaction that are in
Rem	the prepared state (although this is possible when transaction is
Rem	in collecting state). Hence, the funny BITAND condition is probably
Rem	not required (tran.state != collecting ==> only one branch left ?)
Rem	Secondly, the view is not just a simple Union of the fixed view
Rem	and the dictionary tables information in order to eliminate the window
Rem	where SMON writes information into the dictionary table after we select
Rem	it from the in-memory table (not having the MINUS may result in
Rem	duplicates in this view).
Rem	DBAs are not required to select this view. Currently, only XA uses this
Rem	view
set echo on
create or replace view DBA_PENDING_TRANSACTIONS(formatid, globalid, branchid)
as
(((select formatid, globalid, branchid
   from   gv$global_transaction
   where  refcount = preparecount)
 minus
  (select global_tran_fmt, global_foreign_id, branch_id
   from   sys.pending_trans$ tran, sys.pending_sessions$ sess
   where  tran.local_tran_id = sess.local_tran_id
     and  tran.state != 'collecting'
     and  dbms_utility.is_bit_set(tran.session_vector, sess.session_id)=1)
 )
 union
  (select global_tran_fmt, global_foreign_id, branch_id
   from   sys.pending_trans$ tran, sys.pending_sessions$ sess
   where  tran.local_tran_id = sess.local_tran_id
     and  tran.state != 'collecting'
     and  dbms_utility.is_bit_set(tran.session_vector, sess.session_id)=1)
);
drop public synonym DBA_PENDING_TRANSACTIONS;
create public synonym DBA_PENDING_TRANSACTIONS for DBA_PENDING_TRANSACTIONS;
grant select on DBA_PENDING_TRANSACTIONS to select_catalog_role;
comment on table DBA_PENDING_TRANSACTIONS is
  'information about unresolved global transactions';
comment on column DBA_PENDING_TRANSACTIONS.formatid is
  'format identifier of the transaction identifier';
comment on column DBA_PENDING_TRANSACTIONS.globalid is
  'global part (gtrid) of the transaction identifier';
comment on column DBA_PENDING_TRANSACTIONS.branchid is
  'branch qualifier (bqual) of the transaction identifier';

set echo off
