1 PACKAGE dbms_service as
2
3 ------------
4 -- OVERVIEW
5 --
6 -- This package allows an application to manage services and sessions
7 -- connected with a specific service name.
8 --
9 -- Oracle Real Application Cluster (RAC) has a functionality to manage
10 -- service names across instances. This package allows the creation,
11 -- deletion, starting and stopping of services in both RAC and single
12 -- instance.
13 -- Additionally it provides the ability to disconnect all sessions which
14 -- connect to the instance with a service name when RAC removes that
15 -- service name from the instance.
16
17 ----------------
18 -- INSTALLATION
19 --
20 -- This package should be installed under SYS schema.
21 --
22 -- SQL> @dbmssrv
23 --
24 -----------
25 -- EXAMPLE
26 --
27 -- Disconnect all sessions in the local instance which connected
28 -- using service name foo.us.oracle.com.
29 --
30 -- dbms_service.disconnect_session('foo.us.oracle.com');
31 --
32 -- dbms_service.disconnect_session() does not return until all
33 -- corresponding sessions disconnected. Therefore, dbms_job package or
34 -- put the SQL session in background if the caller does not want to
35 -- wait for all corresponding sessions disconnected.
36 --
37 -- An option can be passed to disconnect_session(). If option is
38 -- dbms_service.disconnect_session_immediate, sessions will be
39 -- disconnected immediately.
40 --
41 -- dbms_service.disconnect_session('foo.us.oracle.com',
42 -- dbms_service.immediate);
43 --
44
45 --------------------------
46 -- IMPLEMENTATION DETAILS
47 --
48 -- dbms_service.disconnect_session() calls SQL statement
49 --
50 -- ALTER SYSTEM DISCONNECT SESSION sid, serial option
51 --
52 -- The default value of option is POST_TRANSCATION.
53 --
54 --
55 ------------
56 -- SECURITY
57 --
58 -- The execute privilage of the package is granted to DBA role only.
59
60 ----------------------------
61 -- PROCEDURES AND FUNCTIONS
62 --
63
64 -- Options for disconnect session
65 post_transaction constant number := 0;
66 immediate constant number := 1;
67 noreplay constant number := 2;
68
69 procedure disconnect_session(service_name in varchar2,
70 disconnect_option in number
71 default post_transaction);
72 -- Disconnect sessions which connect to the local instance with
73 -- the specified service name.
74 -- Input parameter(s):
75 -- service_name
76 -- service name of the sessions to be disconnected.
77 -- disconnect_option
78 -- option to be passed to 'alter system disconnect session'.
79 -- its value can be dbms_service.post_transaction,
80 -- dbms_service.immediate or dbms_session.noreplay.
81 --
82 -- post_transaction : wait for current transaction to complete
83 -- immediate : disconnect session immediately
84 -- noreplay : disconnect session immediately and current
85 -- transaction will not be replayed if
86 -- application continuity is enabled.
87 --
88 -- The default value is dbms_service.post_transaction
89
90 type svc_parameter_array is table of varchar2(100) index by varchar2(100);
91
92 procedure create_service(service_name in varchar2,
93 network_name in varchar2,
94 parameter_array in svc_parameter_array);
95 -- Creates a new service$ entry for this service name
96 -- Input parameter(s):
97 -- service_name
98 -- The service's short name. Limited to 64 characters.
99 -- network_name
100 -- the full network name for the service. This will usually be the same
101 -- as the service_name.
102 -- parameter_array
103 -- associative array with name/value pairs of the service attributes.
104 -- The following list describes the supported names.
105 --
106 -- goal
107 -- the workload management goal directive of the service. Valid values
108 -- are : DBMS_SERVICE.GOAL_SERVICE_TIME,
109 -- DBMS_SERVICE.GOAL_THROUGHPUT,
110 -- DBMS_SERVICE.GOAL_NONE.
111 -- dtp
112 -- declares the service to be for DTP or distributed transactions.
113 -- aq_ha_notifications
114 -- determines whether HA events are sent via AQ for this service.
115 -- failover_method
116 -- the TAF failover method for the service
117 -- failover_type
118 -- the TAF failover type for the service
119 -- failover_retries
120 -- the TAF failover retries for the service
121 -- failover_delay
122 -- the TAF failover delay for the service
123 -- edition
124 -- the initial session edition
125 -- commit_outcome
126 -- persist outcome of transactions
127 -- retention_timeout
128 -- timeout when the transaction outcome is retained
129 -- replay_initiation_timeout
130 -- timeout when replayed is disabled
131 -- session_state_consistency
132 -- Consistency of session state: static or dynamic
133 -- sql_translation_name
134 -- Name of SQL translation unit
135
136 procedure create_service(service_name in varchar2,
137 network_name in varchar2,
138 goal in number default NULL,
139 dtp in boolean default NULL,
140 aq_ha_notifications in boolean default NULL,
141 failover_method in varchar2 default NULL,
142 failover_type in varchar2 default NULL,
143 failover_retries in number default NULL,
144 failover_delay in number default NULL,
145 clb_goal in number default NULL,
146 edition in varchar2 default NULL);
147 -- Creates a new service$ entry for this service name
148 -- Input parameter(s):
149 -- service_name
150 -- The service's short name. Limited to 64 characters.
151 -- net_name
152 -- the full network name for the service. This will usually be the same
153 -- as the service_name.
154 -- goal
155 -- the workload management goal directive for the service. Valid values
156 -- are : DBMS_SERVICE.GOAL_SERVICE_TIME,
157 -- DBMS_SERVICE.GOAL_THROUGHPUT,
158 -- DBMS_SERVICE.GOAL_NONE.
159 -- dtp
160 -- declares the service to be for DTP or distributed transactions.
161 -- aq_ha_notifications
162 -- determines whether HA events are sent via AQ for this service.
163 -- failover_method
164 -- the TAF failover method for the service
165 -- failover_type
166 -- the TAF failover type for the service
167 -- failover_retries
168 -- the TAF failover retries for the service
169 -- failover_delay
170 -- the TAF failover delay for the service
171 -- edition
172 -- the initial session edition
173
174 procedure modify_service(service_name in varchar2,
175 parameter_array in svc_parameter_array);
176 -- Modifies an existing service
177 -- Input parameter(s):
178 -- service_name
179 -- The service's short name. Limited to 64 characters.
180 -- parameter_array
181 -- associative array with name/value pairs of the service attributes.
182 -- The following list describes the supported names.
183 --
184 -- goal
185 -- the workload management goal directive of the service. Valid values
186 -- defined under create_service above.
187 -- dtp
188 -- declares the service to be for DTP or distributed transactions.
189 -- aq_ha_notifications
190 -- determines whether HA events are sent via AQ for this service.
191 -- failover_method
192 -- the TAF failover method for the service
193 -- failover_type
194 -- the TAF failover type for the service
195 -- failover_retries
196 -- the TAF failover retries for the service
197 -- failover_delay
198 -- the TAF failover delay for the service
199 -- edition
200 -- the initial session edition
201 -- commit_outcome
202 -- persist outcome of transactions
203 -- retention_timeout
204 -- timeout when the transaction outcome is retained
205 -- replay_initiation_timeout
206 -- timeout when replayed is disabled
207 -- session_state_consistency
208 -- Consistency of session state: static or dynamic
209 -- sql_translation_name
210 -- Name of SQL translation unit
211
212 procedure modify_service(service_name in varchar2,
213 goal in number default NULL,
214 dtp in boolean default NULL,
215 aq_ha_notifications in boolean default NULL,
216 failover_method in varchar2 default NULL,
217 failover_type in varchar2 default NULL,
218 failover_retries in number default NULL,
219 failover_delay in number default NULL,
220 clb_goal in number default NULL,
221 edition in varchar2 default NULL,
222 modify_edition in boolean default FALSE);
223 -- Modifies an existing service
224 -- Input parameter(s):
225 -- service_name
226 -- The service's short name. Limited to 64 characters.
227 -- goal
228 -- the workload management goal directive for the service. Valid values
229 -- defined under create_service above.
230 -- dtp
231 -- declares the service to be for DTP or distributed transactions.
232 -- aq_ha_notifications
233 -- determines whether HA events are sent via AQ for this service.
234 -- failover_method
235 -- the TAF failover method for the service
236 -- failover_type
237 -- the TAF failover type for the service
238 -- failover_retries
239 -- the TAF failover retries for the service
240 -- failover_delay
241 -- the TAF failover delay for the service
242 -- edition
243 -- the initial session edition
244 -- modify_edition
245 -- true if edition is to be modified
246
247 procedure delete_service(service_name in varchar2);
248 -- Marks a service$ entry as deleted.
249 -- Input parameter(s):
250 -- service_name
251 -- The services short name. Limited to 64 characters.
252
253 procedure start_service(service_name in varchar2,
254 instance_name in varchar2 default NULL);
255 -- In single instance exclusive alters the service_name IOP to contain
256 -- this service_name. In RAC will optionally on the instance specified.
257 -- Input parameter(s):
258 -- service_name
259 -- The services short name. Limited to 64 characters.
260 -- instance_name
261 -- The instance on which to start the service. NULL results in starting
262 -- of the service on the local instance.
263 -- In single instance this can only be the current
264 -- instance or NULL.
265 -- Specify DBMS_SERVICE.ALL_INSTANCES to start the service on all
266 -- configured instances.
267
268 procedure stop_service(service_name in varchar2,
269 instance_name in varchar2 default NULL);
270 -- In single instance exclusive alters the service_name IOP to remove
271 -- this service_name. In RAC will call out to CRS to stop the service
272 -- optionally on the instance specified. Calls clscrs_stop_resource.
273 -- Input parameter(s):
274 -- service_name
275 -- The services short name. Limited to 64 characters.
276 -- instance_name
277 -- The instance on which to stop the service. NULL results in stopping
278 -- of the service locally.
279 -- In single instance this can only be the current
280 -- instance or NULL. The default in RAC and exclusive case is NULL.
281 -- Specify DBMS_SERVICE.ALL_INSTANCES to start the service on all
282 -- configured instances.
283
284 -------------
285 -- CONSTANTS
286 --
287 -- Constants for use in calling arguments.
288
289 goal_none constant number := 0;
290 goal_service_time constant number := 1;
291 goal_throughput constant number := 2;
292
293 all_instances constant varchar2(2) := '*';
294
295 -- Connection Balancing Goal arguments
296
297 clb_goal_short constant number := 1;
298 clb_goal_long constant number := 2;
299
300 -- TAF failover attribute arguments
301
302 failover_method_none constant varchar2(5) := 'NONE';
303 failover_method_basic constant varchar2(6) := 'BASIC';
304
305 failover_type_none constant varchar2(5) := 'NONE';
306 failover_type_session constant varchar2(8) := 'SESSION';
307 failover_type_select constant varchar2(7) := 'SELECT';
308
309 -------------------------
310 -- ERRORS AND EXCEPTIONS
311 --
312 -- When adding errors remember to add a corresponding exception below.
313
314 err_null_service_name constant number := -44301;
315 err_null_network_name constant number := -44302;
316 err_service_exists constant number := -44303;
317 err_service_does_not_exist constant number := -44304;
318 err_service_in_use constant number := -44305;
319 err_service_name_too_long constant number := -44306;
320 err_network_prefix_too_long constant number := -44307;
321 err_not_initialized constant number := -44308;
322 err_general_failure constant number := -44309;
323 err_max_services_exceeded constant number := -44310;
324 err_service_not_running constant number := -44311;
325 err_database_closed constant number := -44312;
326 err_invalid_instance constant number := -44313;
327 err_network_exists constant number := -44314;
328 err_null_attributes constant number := -44315;
329 err_invalid_argument constant number := -44316;
330 err_database_readonly constant number := -44317;
331 err_max_sn_length constant number := -44318;
332 err_aq_service constant number := -44319;
333 err_glb_service constant number := -44320;
334 err_invalid_pdb_name constant number := -44771;
335 err_crs_api constant number := -44772;
336 err_pdb_closed constant number := -44773;
337 err_pdb_invalid constant number := -44774;
338 err_pdb_name constant number := -44775;
339 err_pdb_exp constant number := -44776;
340 err_pdb_fail constant number := -44777;
341 err_tg_rettm constant number := -44778;
342 err_tg_repto constant number := -44779;
343 err_tg_co constant number := -44780;
344 err_tg_aq constant number := -44781;
345 err_crs_fail constant number := -44782;
346 err_mxrlbsvc constant number := -44783;
347 err_delint constant number := -44784;
348 err_tg_dbsvc constant number := -44785;
349 err_pdb_imp constant number := -44786;
350 err_intr constant number := -1013;
351
352 null_service_name EXCEPTION;
353 null_network_name EXCEPTION;
354 service_exists EXCEPTION;
355 service_does_not_exist EXCEPTION;
356 service_in_use EXCEPTION;
357 service_name_too_long EXCEPTION;
358 network_prefix_too_long EXCEPTION;
359 not_initialized EXCEPTION;
360 general_failure EXCEPTION;
361 max_services_exceeded EXCEPTION;
362 service_not_running EXCEPTION;
363 database_closed EXCEPTION;
364 invalid_instance EXCEPTION;
365 network_exists EXCEPTION;
366 null_attributes EXCEPTION;
367 invalid_argument EXCEPTION;
368 database_readonly EXCEPTION;
369 max_sn_length EXCEPTION;
370 aq_service EXCEPTION;
371 glb_service EXCEPTION;
372 invalid_pdb_name EXCEPTION;
373 crs_api_failed EXCEPTION;
374 pdb_closed EXCEPTION;
375 pdb_invalid EXCEPTION;
376 pdb_name EXCEPTION;
377 pdb_exp EXCEPTION;
378 pdb_fail EXCEPTION;
379 tg_rettm EXCEPTION;
380 tg_repto EXCEPTION;
381 tg_co EXCEPTION;
382 tg_aq EXCEPTION;
383 crs_fail EXCEPTION;
384 mxrlbsvc EXCEPTION;
385 delint EXCEPTION;
386 tg_dbsvc EXCEPTION;
387 pdb_imp EXCEPTION;
388 intr EXCEPTION;
389
390 PRAGMA EXCEPTION_INIT(null_service_name, -44301);
391 PRAGMA EXCEPTION_INIT(null_network_name, -44302);
392 PRAGMA EXCEPTION_INIT(service_exists, -44303);
393 PRAGMA EXCEPTION_INIT(service_does_not_exist, -44304);
394 PRAGMA EXCEPTION_INIT(service_in_use, -44305);
395 PRAGMA EXCEPTION_INIT(service_name_too_long, -44306);
396 PRAGMA EXCEPTION_INIT(network_prefix_too_long, -44307);
397 PRAGMA EXCEPTION_INIT(not_initialized, -44308);
398 PRAGMA EXCEPTION_INIT(general_failure, -44309);
399 PRAGMA EXCEPTION_INIT(max_services_exceeded, -44310);
400 PRAGMA EXCEPTION_INIT(service_not_running, -44311);
401 PRAGMA EXCEPTION_INIT(database_closed, -44312);
402 PRAGMA EXCEPTION_INIT(invalid_instance, -44313);
403 PRAGMA EXCEPTION_INIT(network_exists, -44314);
404 PRAGMA EXCEPTION_INIT(null_attributes, -44315);
405 PRAGMA EXCEPTION_INIT(invalid_argument, -44316);
406 PRAGMA EXCEPTION_INIT(database_readonly, -44317);
407 PRAGMA EXCEPTION_INIT(max_sn_length, -44318);
408 PRAGMA EXCEPTION_INIT(aq_service, -44319);
409 PRAGMA EXCEPTION_INIT(glb_service, -44320);
410 PRAGMA EXCEPTION_INIT(invalid_pdb_name, -44771);
411 PRAGMA EXCEPTION_INIT(crs_api_failed, -44772);
412 PRAGMA EXCEPTION_INIT(pdb_closed, -44773);
413 PRAGMA EXCEPTION_INIT(pdb_invalid, -44774);
414 PRAGMA EXCEPTION_INIT(pdb_name, -44775);
415 PRAGMA EXCEPTION_INIT(pdb_exp, -44776);
416 PRAGMA EXCEPTION_INIT(pdb_fail, -44777);
417 PRAGMA exception_init(tg_rettm, -44778);
418 PRAGMA EXCEPTION_INIT(tg_repto, -44779);
419 PRAGMA EXCEPTION_INIT(tg_co, -44780);
420 PRAGMA EXCEPTION_INIT(tg_aq, -44781);
424 PRAGMA EXCEPTION_INIT(tg_dbsvc, -44785);
421 PRAGMA EXCEPTION_INIT(crs_fail, -44782);
422 PRAGMA EXCEPTION_INIT(mxrlbsvc, -44783);
423 PRAGMA EXCEPTION_INIT(delint, -44784);
425 PRAGMA EXCEPTION_INIT(pdb_imp, -44786);
426 PRAGMA EXCEPTION_INIT(intr, -1013);
427
428 end dbms_service;
[Home] [Help]