DBA_2PC_PENDING view tips

Oracle Tips by Burleson Consulting

DBA_2PC_PENDING

<< Return to the index

Oracle 11g makes the following comments about the DBA_2PC_PENDING table: "info about distributed transactions awaiting recovery"

Related notes on DBA_2PC_PENDING:

Column description of the DBA_2PC_PENDING view:

LOCAL_TRAN_ID

Description of DBA_2PC_PENDING.LOCAL_TRAN_ID: "string of form: n.n.n, n a number"

GLOBAL_TRAN_ID

Description of DBA_2PC_PENDING.GLOBAL_TRAN_ID: "globally unique transaction id"

STATE

Description of DBA_2PC_PENDING.STATE: "collecting, prepared, committed, forced commit, or forced rollback"

MIXED

Description of DBA_2PC_PENDING.MIXED: "yes => part of the transaction committed and part rolled back (commit or rollback with the FORCE option was used)"

ADVICE

Description of DBA_2PC_PENDING.ADVICE: "C for commit, R for rollback, else null"

TRAN_COMMENT

Description of DBA_2PC_PENDING.TRAN_COMMENT: "text for "commit work comment ""

FAIL_TIME

Description of DBA_2PC_PENDING.FAIL_TIME: "value of SYSDATE when the row was inserted (tx or system recovery)"

FORCE_TIME

Description of DBA_2PC_PENDING.FORCE_TIME: "time of manual force decision (null if not forced locally)"

RETRY_TIME

Description of DBA_2PC_PENDING.RETRY_TIME: "time automatic recovery (RECO) last tried to recover the transaction"

OS_USER

Description of DBA_2PC_PENDING.OS_USER: "operating system specific name for the end-user"

OS_TERMINAL

Description of DBA_2PC_PENDING.OS_TERMINAL: "operating system specific name for the end-user terminal"

HOST

Description of DBA_2PC_PENDING.HOST: "name of the host machine for the end-user"

DB_USER

Description of DBA_2PC_PENDING.DB_USER: "Oracle user name of the end-user at the topmost database"

COMMIT#

Description of DBA_2PC_PENDING.COMMIT#: "global commit number for committed transactions"

DBA_2PC_PENDING View Source

Oracle 11g's data dictionary defines the DBA_2PC_PENDING view using the following source query:

select local_tran_id,
nvl(global_oracle_id, global_tran_fmt||'.'||global_foreign_id),
state, decode(status,'D','yes','no'), heuristic_dflt, tran_comment,
fail_time, heuristic_time, reco_time,
top_os_user, top_os_terminal, top_os_host, top_db_user, global_commit#
from sys.pending_trans$

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.
You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.

Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

Oracle performance Tuning 10g reference poster

Oracle training & performance tuning books