The Library is currently in the process of being upgraded from Oracle Database Version to 22.214.171.124
and version 18.0 is going to be available soon.
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 and beyond.
Have you viewed the Library's main Table of Contents? If not ... [Click Here]
... scroll down the page and you will find links to the largest collection of Oracle Database demos on the net.
SET TRANSACTION must be first statement of transaction
The following refers to use of the DBMS_TRANSACTION built-in package:
Before automatic recovery runs, the transaction may show up in DBA_2PC_PENDING as state "collecting", "committed", or "prepared".
If the DBA has forced an in-doubt transaction to have a particular result by using "commit force" or "rollback force", then states "forced commit" or "forced rollback" may also appear. Automatic recovery will normally delete entries in any of these states.
The only exception is when recovery finds a forced transaction which is in a state inconsistent with other sites in the transaction; in this case, the entry will be left in the table and the MIXED column will have a value 'yes'.
However, under certain conditions, it may not be possible for automatic recovery to run. For example, a remote database may have been permanently lost.
Even if it is recreated, it will get a new database id, so that recovery cannot identify it (a possible symptom is ORA-02062). In this case, the DBA may use the procedure purge_lost_db_entry to clean up the entries in any state other than "prepared".
The DBA does not need to be in any particular hurry to resolve these entries, since they will not be holding any database resources.
-- The following table indicates what the various states indicate about
-- the transaction and what the DBA actions should be:
Global Transaction State
Local Transaction State
Normal DBA Action
Alternative DBA Action
force commit or rollback
forced commit mixed
forced rollback mixed
(1) Use only if significant reconfiguration has occurred so that automatic recovery cannot resolve the transaction.
Examples are total loss of the remote database, reconfiguration in software resulting in loss of two-phase commit capability, or loss of information from an external transaction coordinator such as a TP Monitor.
(2) Examine and take any manual action to remove inconsistencies, then use the procedure purge_mixed.
CREATE OR REPLACE PROCEDURE spdemo (sp_in VARCHAR2) IS
INSERT INTO t2 (testcol) VALUES (1);
INSERT INTO t2 (testcol) VALUES (2);
INSERT INTO t2 (testcol) VALUES (3);
INSERT INTO t2 (testcol) VALUES (4);
EXECUTE IMMEDIATE 'ROLLBACK TO SAVEPOINT ' || sp_in;
SELECT * FROM t2;
TRUNCATE TABLE t2;
SELECT * FROM t2;
Estimate Transaction Rate
SELECT sequence#, first_change#, next_change#,
(next_change#-first_change#) AS chg_cnt,
first_time, next_time, ((next_time-first_time)*1440) AS chg_min,
((next_change#-first_change#)/((next_time-first_time)*1440)) AS chgs_per_min
ORDER BY sequence#;