1 package dbms_transaction AUTHID CURRENT_USER is
2
3 ------------
4 -- OVERVIEW
5 --
6 -- This package provides access to SQL transaction statements from
7 -- stored procedures.
8 -- It also provids functions for monitoring transaction activities
9 -- (transaction ids and ordering of steps of transactions )
10
11 ----------------------------
12 -- PROCEDURES AND FUNCTIONS
13 --
14 procedure read_only;
15 -- Equivalent to SQL "SET TRANSACTION READ ONLY"
16 procedure read_write;
17 -- Equivalent to SQL "SET TRANSACTION READ WRITE"
18 procedure advise_rollback;
19 -- Equivalent to SQL "ALTER SESSION ADVISE ROLLBACK"
20 procedure advise_nothing;
21 -- Equivalent to SQL "ALTER SESSION ADVISE NOTHING"
22 procedure advise_commit;
23 -- Equivalent to SQL "ALTER SESSION ADVISE COMMIT"
24 procedure use_rollback_segment(rb_name varchar2);
25 -- Equivalent to SQL "SET TRANSACTION USE ROLLBACK SEGMENT <rb_seg_name>"
26 -- Input arguments:
27 -- rb_name
28 -- Name of rollback segment to use.
29 procedure commit_comment(cmnt varchar2);
30 -- Equivalent to SQL "COMMIT COMMENT <text>"
31 -- Input arguments:
32 -- cmnt
33 -- Comment to assoicate with this comment.
34 procedure commit_force(xid varchar2, scn varchar2 default null);
35 -- Equivalent to SQL "COMMIT FORCE <text>, <number>"
36 -- Input arguments:
37 -- xid
38 -- Local or global transaction id.
39 -- scn
40 -- System change number.
41 procedure commit;
42 pragma interface (C, commit); -- 1 (see psdicd.c)
43 -- Equivalent to SQL "COMMIT". Here for completeness. This is
44 -- already implemented as part of PL/SQL.
45 procedure savepoint(savept varchar2);
46 pragma interface (C, savepoint); -- 2 (see psdicd.c)
47 -- Equivalent to SQL "SAVEPOINT <savepoint_name>". Here for
48 -- completeness. This is already implemented as part of PL/SQL.
49 -- Input arguments:
50 -- savept
51 -- Savepoint identifier.
52 procedure rollback;
53 pragma interface (C, rollback); -- 3 (see psdicd.c)
54 -- Equivalent to SQL "ROLLBACK". Here for completeness. This is
55 -- already implemented as part of PL/SQL.
56 procedure rollback_savepoint(savept varchar2);
57 pragma interface (C, rollback_savepoint); -- 4 (see psdicd.c)
58 -- Equivalent to SQL "ROLLBACK TO SAVEPOINT <savepoint_name>". Here for
59 -- completeness. This is already implemented as part of PL/SQL.
60 -- Input arguments:
61 -- savept
62 -- Savepoint identifier.
63 procedure rollback_force(xid varchar2);
64 -- Equivalent to SQL "ROLLBACK FORCE <text>"
65 -- Input arguments:
66 -- xid
67 -- Local or global transaction id.
68 procedure begin_discrete_transaction;
69 pragma interface (C, begin_discrete_transaction); -- 5 (see psdicd.c)
70 -- Set "discrete transaction mode" for this transaction.
71 -- Exceptions:
72 -- ORA-08175 will be generated if a transaction attempts an operation
73 -- which cannot be performed as a discrete transaction. If this
74 -- exception is encountered, rollback and retry the transaction.
75
76 -- ORA-08176 will be generated if a transaction encounters data changed
77 -- by an operation that does not generate rollback data : create index,
78 -- direct load or discrete transaction. If this exception is
79 -- encountered, retry the operation that received the exception.
80 --
81 DISCRETE_TRANSACTION_FAILED exception;
82 pragma exception_init(DISCRETE_TRANSACTION_FAILED, -8175);
83
84 CONSISTENT_READ_FAILURE exception;
85 pragma exception_init(CONSISTENT_READ_FAILURE, -8176);
86
87 procedure purge_mixed(xid varchar2);
88 -- When indoubt transactions are forced to commit or rollback (instead of
89 -- letting automatic recovery resolve their outcomes), there is a
90 -- possibility that a transaction can have a mixed outcome: some sites
91 -- commit, and others rollback. Such inconsistency cannot be resolved
92 -- automatically by ORACLE; however, ORACLE will flag entries in
93 -- DBA_2PC_PENDING by setting the MIXED column to a value of 'yes'.
94 -- ORACLE will never automatically delete information about a mixed
95 -- outcome transaction. When the application or DBA is sure all
96 -- inconsistencies that might have arisen as a result of the mixed
97 -- transaction have been resolved, this procedure can be used to
98 -- delete the information about a given mixed outcome transaction.
99 -- Input arguments:
100 -- xid
101 -- This must be set to the value of the LOCAL_TRAN_ID column in
102 -- the DBA_2PC_PENDING table.
103
104 procedure purge_lost_db_entry(xid varchar2);
105 -- When a failure occurs during commit processing, automatic recovery will
106 -- consistently resolve the results at all sites involved in the
107 -- transaction. However, if the remote database is destroyed or
108 -- recreated before recovery completes, then the entries used to
109 -- control recovery in DBA_2PC_PENDING and associated tables will never
110 -- be removed, and recovery will periodically retry. Procedure
111 -- purge_lost_db_entry allows removal of such transactions from the
112 -- local site.
113
114 -- WARNING: purge_lost_db_entry should ONLY be used when the other
115 -- database is lost or has been recreated. Any other use may leave the
116 -- other database in an unrecoverable or inconsistent state.
117
118 -- Before automatic recovery runs, the transaction may show
119 -- up in DBA_2PC_PENDING as state "collecting", "committed", or
120 -- "prepared". If the DBA has forced an in-doubt transaction to have
121 -- a particular result by using "commit force" or "rollback force",
122 -- then states "forced commit" or "forced rollback" may also appear.
123 -- Automatic recovery will normally delete entries in any of these
124 -- states. The only exception is when recovery finds a forced
125 -- transaction which is in a state inconsistent with other sites in the
126 -- transaction; in this case, the entry will be left in the table
127 -- and the MIXED column will have a value 'yes'.
128
129 -- However, under certain conditions, it may not be possible for
130 -- automatic recovery to run. For example, a remote database may have
131 -- been permanently lost. Even if it is recreated, it will get a new
132 -- database id, so that recovery cannot identify it (a possible symptom
133 -- is ORA-02062). In this case, the DBA may use the procedure
134 -- purge_lost_db_entry to clean up the entries in any state other
135 -- than "prepared". The DBA does not need to be in any particular
136 -- hurry to resolve these entries, since they will not be holding any
137 -- database resources.
138
139 -- The following table indicates what the various states indicate about
140 -- the transaction and what the DBA actions should be:
141
142 -- State State of State of Normal Alternative
143 -- Column Global Local DBA DBA
144 -- Transaction Transaction Action Action
145 -- ---------- ------------ ------------ ------ ---------------
146 -- collecting rolled back rolled back none purge_lost_db_entry (1)
147 -- committed committed committed none purge_lost_db_entry (1)
148 -- prepared unknown prepared none force commit or rollback
149 -- forced unknown committed none purge_lost_db_entry (1)
150 -- commit
151 -- forced unknown rolled back none purge_lost_db_entry (1)
152 -- rollback
153 -- forced mixed committed (2)
154 -- commit
155 -- (mixed)
156 -- forced mixed rolled back (2)
157 -- rollback
158 -- (mixed)
159
160 -- Note 1: Use only if significant reconfiguration has occurred so that
161 -- automatic recovery cannot resolve the transaction. Examples are
162 -- total loss of the remote database, reconfiguration in software
163 -- resulting in loss of two-phase commit capability, or loss of
164 -- information from an external transaction coordinator such as a TP
165 -- Monitor.
166 -- Note 2: Examine and take any manual action to remove inconsistencies,
167 -- then use the procedure purge_mixed.
168 -- Input arguments:
169 -- xid
170 -- This must be set to the value of the LOCAL_TRAN_ID column in
171 -- the DBA_2PC_PENDING table.
172
173 FUNCTION local_transaction_id(create_transaction BOOLEAN := FALSE)
174 RETURN VARCHAR2;
175 -- Return local (to instance) unique identfier for current transaction
176 -- Return null if there is no current transction.
177 -- Input parameters:
178 -- create_transaction
179 -- If true , start a transaciton if one is not currently
180 -- active.
181 --
182 FUNCTION step_id RETURN NUMBER;
183 -- Return local (to local transaction ) unique positive integer that orders
184 -- The DML operations of a transaction.
185 -- Input parmaeters:
186
187 end;
[Home] [Help]