1 package dbms_session AUTHID CURRENT_USER is
2 ------------
3 -- OVERVIEW
4 --
5 -- This package provides access to SQL "alter session" statements, and
6 -- other session information from, stored procedures.
7 ----------------------------
8 -- PROCEDURES AND FUNCTIONS
9 --
10 procedure set_role(role_cmd varchar2);
11 -- Equivalent to SQL "SET ROLE ...".
12 -- Input arguments:
13 -- role_cmd
14 -- This text is appended to "set role " and then executed as SQL.
15 procedure set_sql_trace(sql_trace boolean);
16 -- Equivalent to SQL "ALTER SESSION SET SQL_TRACE ..."
17 -- Note:
18 -- Using "execute immediate 'alter session'" has better performance
19 -- than using dbms_session.set_sql_trace.
20 -- Input arguments:
21 -- sql_trace
22 -- TRUE or FALSE. Turns tracing on or off.
23 procedure set_nls(param varchar2, value varchar2);
24 -- Equivalent to SQL "ALTER SESSION SET <nls_parameter> = <value>"
25 -- Note:
26 -- Using "execute immediate 'alter session'" has better performance
27 -- than using dbms_session.set_nls. Further, when setting multiple
28 -- parameters, EXEC IMMEDIATE allows user to set all of them in one
29 -- statement, which is much more efficient than calling
30 -- dbms_session.set_nls in multiple statements.
31 -- Input arguments:
32 -- param
33 -- The NLS parameter. The parameter name must begin with 'NLS'.
34 -- value
35 -- The value to set the parameter to. If the parameter is a
36 -- text literal then it will need embedded single-quotes. For
37 -- example "set_nls('nls_date_format','''DD-MON-YY''')"
38 procedure close_database_link(dblink varchar2);
39 -- Equivalent to SQL "ALTER SESSION CLOSE DATABASE LINK <name>"
40 -- Input arguments:
41 -- name
42 -- The name of the database link to close.
43 procedure reset_package;
44 -- Deinstantiate all packages in this session. In other words, free
45 -- all package state. This is the situation at the beginning of
46 -- a session.
47
48 --------------------------------------------------------------------
49 -- action_flags (bit flags) for MODIFY_PACKAGE_STATE procedure ---
50 --------------------------------------------------------------------
51 FREE_ALL_RESOURCES constant PLS_INTEGER := 1;
52 REINITIALIZE constant PLS_INTEGER := 2;
53
54 procedure modify_package_state(action_flags IN PLS_INTEGER);
55 -- The MODIFY_PACKAGE_STATE procedure can be used to perform
56 -- various actions (as specified by the 'action_flags' parameter)
57 -- on the session state of ALL PL/SQL program units active in the
58 -- session. This takes effect only after the PL/SQL call that
59 -- made the current invokation finishes running.
60 --
61 -- Parameter(s):
62 -- action_flags:
63 -- Determines what action is taken on the program units.
64 -- The following action_flags are supported:
65 --
66 -- * DBMS_SESSION.FREE_ALL_RESOURCES:
67 -- This frees all the memory associated with each of the
68 -- previously run PL/SQL programs from the session, and,
69 -- consequently, clears the current values of any package
70 -- globals and closes any cached cursors. On subsequent use,
71 -- the PL/SQL program units are re-instantiated and package
72 -- globals are reinitialized. This is essentially the
73 -- same as DBMS_SESSION.RESET_PACKAGE() interface.
74 --
75 -- * DBMS_SESSION.REINITIALIZE:
76 -- In terms of program semantics, the DBMS_SESSION.REINITIALIZE
77 -- flag is similar to the DBMS_SESSION.FREE_ALL_RESOURCES flag
78 -- in that both have the effect of re-initializing all packages.
79 --
80 -- However, DBMS_SESSION.REINITIALIZE should exhibit much better
81 -- performance than the DBMS_SESSION.FREE_ALL_RESOURCES option
82 -- because:
83 --
84 -- - packages are reinitialized without actually being freed
85 -- and recreated from scratch. Instead the package memory gets
86 -- reused.
87 --
88 -- - any open cursors are closed, semantically speaking. However,
89 -- the cursor resource is not actually freed. It is simply
90 -- returned to the PL/SQL cursor cache. And more importantly,
91 -- the cursor cache is not flushed. Hence, cursors
92 -- corresponding to frequently accessed static SQL in PL/SQL
93 -- will remain cached in the PL/SQL cursor cache and the
94 -- application will not incur the overhead of opening, parsing
95 -- and closing a new cursor for those statements on subsequent use.
96 --
97 -- - the session memory for PL/SQL modules without global state
98 -- (such as types, stored-procedures) will not be freed and
99 -- recreated.
100 --
101 --
102 -- Usage Example:
103 -- begin
104 -- dbms_session.modify_package_state(DBMS_SESSION.REINITIALIZE);
105 -- end;
106 --
107
108 function unique_session_id return varchar2;
109 pragma restrict_references(unique_session_id,WNDS,RNDS,WNPS);
110 -- Return an identifier that is unique for all sessions currently
111 -- connected to this database. Multiple calls to this function
112 -- during the same session will always return the same result.
113 -- Output arguments:
114 -- unique_session_id
115 -- can return up to 24 bytes.
116 function is_role_enabled(rolename varchar2) return boolean;
117 -- Determine if the named role is enabled for this session.
118 -- Input arguments:
119 -- rolename
120 -- Name of the role.
121 -- Output arguments:
122 -- is_role_enabled
123 -- TRUE or FALSE depending on whether the role is enabled.
124 function is_session_alive(uniqueid varchar2) return boolean;
125 -- Determine if the specified session is alive.
126 -- Input arguments:
127 -- uniqueid
128 -- Uniqueid of the session.
129 -- Output arguments:
130 -- is_session_alive
131 -- TRUE or FALSE depending on whether the session is alive.
132 procedure set_close_cached_open_cursors(close_cursors boolean);
133 -- Equivalent to SQL "ALTER SESSION SET CLOSE_CACHED_OPEN_CURSORS ..."
134 -- Input arguments:
135 -- close_cursors
136 -- TRUE or FALSE. Turns close_cached_open_cursors on or off.
137 procedure free_unused_user_memory;
138 -- Procedure for users to reclaim unused memory after performing operations
139 -- requiring large amounts of memory (where large is >100K). Note that
140 -- this procedure should only be used in cases where memory is at a
141 -- premium.
142 --
143 -- Examples operations using lots of memory are:
144 --
145 -- o large sorts where entire sort_area_size is used and
146 -- sort_area_size is hundreds of KB
147 -- o compiling large PL/SQL packages/procedures/functions
148 -- o storing hundreds of KB of data within PL/SQL indexed tables
149 --
150 -- One can monitor user memory by tracking the statistics
151 -- "session uga memory" and "session pga memory" in the
152 -- v$sesstat/v$statname fixed views. Monitoring these statistics will
153 -- also show how much memory this procedure has freed.
154 --
155 -- The behavior of this procedure depends upon the configuration of the
156 -- server operating on behalf of the client:
157 --
158 -- o dedicated server - returns unused PGA memory and session memory
159 -- to the OS (session memory is allocated from the PGA in this
160 -- configuration)
161 -- o MTS server - returns unused session memory to the
162 -- shared_pool (session memory is allocated from the shared_pool
163 -- in this configuration)
164 --
165 -- In order to free memory using this procedure, the memory must
166 -- not be in use.
167 --
168 -- Once an operation allocates memory, only the same type of operation can
169 -- reuse the allocated memory. For example, once memory is allocated
170 -- for sort, even if the sort is complete and the memory is no longer
171 -- in use, only another sort can reuse the sort-allocated memory. For
172 -- both sort and compilation, after the operation is complete, the memory
173 -- is no longer in use and the user can invoke this procedure to free the
174 -- unused memory.
175 --
176 -- An indexed table implicitly allocates memory to store values assigned
177 -- to the indexed table's elements. Thus, the more elements in an indexed
178 -- table, the more memory the RDBMS allocates to the indexed table. As
179 -- long as there are elements within the indexed table, the memory
180 -- associated with an indexed table is in use.
181 --
182 -- The scope of indexed tables determines how long their memory is in use.
183 -- Indexed tables declared globally are indexed tables declared in packages
184 -- or package bodies. They allocate memory from session memory. For an
185 -- indexed table declared globally, the memory will remain in use
186 -- for the lifetime of a user's login (lifetime of a user's session),
187 -- and is freed after the user disconnects from ORACLE.
188 --
189 -- Indexed tables declared locally are indexed tables declared within
190 -- functions, procedures, or anonymous blocks. These indexed tables
191 -- allocate memory from PGA memory. For an indexed table declared
192 -- locally, the memory will remain in use for as long as the user is still
193 -- executing the procedure, function, or anonymous block in which the
194 -- indexed table is declared. After the procedure, function, or anonymous
195 -- block is finished executing, the memory is then available for other
196 -- locally declared indexed tables to use (i.e., the memory is no longer
197 -- in use).
198 --
199 -- Assigning an uninitialized, "empty," indexed table to an existing index
200 -- table is a method to explicitly re-initialize the indexed table and the
201 -- memory associated with the indexed table. After this operation,
202 -- the memory associated with the indexed table will no longer be in use,
203 -- making it available to be freed by calling this procedure. This method
204 -- is particularly useful on indexed tables declared globally which can grow
205 -- during the lifetime of a user's session, as long as the user no
206 -- longer needs the contents of the indexed table.
207 --
208 -- The memory rules associated with an indexed table's scope still apply;
209 -- this method and this procedure, however, allow users to
210 -- intervene and to explictly free the memory associated with an
211 -- indexed table.
212 --
213 -- The PL/SQL fragment below illustrates the method and the use
214 -- of procedure free_unused_user_memory.
215 --
216 -- create package foobar
217 -- type number_idx_tbl is table of number indexed by binary_integer;
218 --
219 -- store1_table number_idx_tbl; -- PL/SQL indexed table
220 -- store2_table number_idx_tbl; -- PL/SQL indexed table
221 -- store3_table number_idx_tbl; -- PL/SQL indexed table
222 -- ...
223 -- end; -- end of foobar
224 --
225 -- declare
226 -- ...
227 -- empty_table number_idx_tbl; -- uninitialized ("empty") version
228 --
229 -- begin
230 -- for i in 1..1000000 loop
231 -- store1_table(i) := i; -- load data
232 -- end loop;
233 -- ...
234 -- store1_table := empty_table; -- "truncate" the indexed table
235 -- ...
236 -- -
237 -- dbms_session.free_unused_user_memory; -- give memory back to system
238 --
239 -- store1_table(1) := 100; -- index tables still declared;
240 -- store2_table(2) := 200; -- but truncated.
241 -- ...
242 -- end;
243 --
244 -- Performance Implication:
245 -- This routine should be used infrequently and judiciously.
246 --
247 -- Input arguments:
248 -- n/a
249 procedure set_context(namespace varchar2, attribute varchar2, value varchar2,
250 username varchar2 default null,
251 client_id varchar2 default null);
252 -- Input arguments:
253 -- namespace
254 -- Name of the namespace to use for the application context
255 -- attribute
256 -- Name of the attribute to be set
257 -- value
258 -- Value to be set
259 -- username
260 -- username attribute for application context . default value is null.
261 -- client_id
262 -- client identifier that identifies a user session for which we need
263 -- to set this context.
264 --
265 --
266 procedure set_identifier(client_id varchar2);
267 -- Input parameters:
268 -- client_id
269 -- client identifier being set for this session .
270 --
271 --
272 procedure clear_context(namespace varchar2, client_id varchar2 default null,
273 attribute varchar2 default null);
274 -- Input parameters:
275 -- namespace
276 -- namespace where the application context is to be cleared
277 -- client_id
278 -- all ns contexts associated with this client id are cleared.
279 -- attribute
280 -- attribute to clear .
281
282 procedure clear_all_context(namespace varchar2);
283 --
284 -- Input parameters:
285 -- namespace
286 -- namespace where the application context is to be cleared
287 --
288 procedure clear_identifier;
289 -- Input parameters:
290 -- none
291 --
292 TYPE AppCtxRecTyp IS RECORD ( namespace varchar2(30), attribute varchar2(30),
293 value varchar2(4000));
294 TYPE AppCtxTabTyp IS TABLE OF AppCtxRecTyp INDEX BY BINARY_INTEGER;
295 procedure list_context(list OUT AppCtxTabTyp, lsize OUT number);
296 -- Input arguments:
297 -- list
298 -- buffer to store a list of application context set in current
299 -- session
300 -- Output arguments:
301 -- list
302 -- contains a list of of (namespace,attribute,values) set in current
303 -- session
304 -- size
305 -- returns the number of entries in the buffer returned
306 procedure switch_current_consumer_group(new_consumer_group IN VARCHAR2,
307 old_consumer_group OUT VARCHAR2,
308 initial_group_on_error IN BOOLEAN);
309 -- Input arguments:
310 -- new_consumer_group
311 -- name of consumer group to switch to
312 -- old_consumer_group
313 -- name of the consumer group just switched out from
314 -- initial_group_on_error
315 -- If TRUE, sets the current consumer group of the invoker to his/her
316 -- initial consumer group in the event of an error.
317 --
318 procedure session_trace_enable(waits IN BOOLEAN DEFAULT TRUE,
319 binds IN BOOLEAN DEFAULT FALSE,
320 plan_stat IN VARCHAR2 DEFAULT NULL);
321 -- Enables SQL trace for the session. Supports waits and binds
322 -- specifications, which makes it more general than set_sql_trace. Using
323 -- this procedure is a preferred way in the future.
324 -- Input parameters:
325 -- waits
326 -- If TRUE, wait information will be present in the trace
327 -- binds
328 -- If TRUE, bind information will be present in the trace
329 -- plan_stat
330 -- Frequency at which we dump row source statistics.
331 -- Value should be 'never', 'first_execution'
332 -- (equivalent to NULL) or 'all_executions'.
333 procedure session_trace_disable;
334 -- Disables SQL trace for the session, which has been enabled by the
335 -- session_trace_enable procedure
336 -- Input parameters:
337 -- none
338 --
342 --
339 procedure set_edition_deferred(edition varchar2);
340 -- Requests a switch to the specified edition. The switch takes
341 -- effect at the end of the current client call.
343 -- Input parameters:
344 -- edition
345 -- The name of the edition to switch to. The contents of the
346 -- string are processed as a SQL identifier; double-quotes must
347 -- surround the remainder of the string if special characters or
348 -- lower case characters are present in the edition's actual
349 -- name, and if double-quotes are not used the contents will be
350 -- uppercased. The caller must have USE privilege on the named
351 -- edition.
352
353 type lname_array IS table of VARCHAR2(4000) index by BINARY_INTEGER;
354 type integer_array IS table of BINARY_INTEGER index by BINARY_INTEGER;
355 procedure get_package_memory_utilization(
356 owner_names OUT NOCOPY lname_array,
357 unit_names OUT NOCOPY lname_array,
358 unit_types OUT NOCOPY integer_array,
359 used_amounts OUT NOCOPY integer_array,
360 free_amounts OUT NOCOPY integer_array);
361
362 -- Supported info_kinds:
363 used_memory CONSTANT BINARY_INTEGER := 1;
364 free_memory CONSTANT BINARY_INTEGER := 2;
365
366 type big_integer_array IS table of INTEGER index by BINARY_INTEGER;
367 type big_integer_matrix IS table of big_integer_array
368 index by BINARY_INTEGER;
369 procedure get_package_memory_utilization(
370 desired_info IN integer_array,
371 owner_names OUT NOCOPY lname_array,
372 unit_names OUT NOCOPY lname_array,
373 unit_types OUT NOCOPY integer_array,
374 amounts OUT NOCOPY big_integer_matrix);
375
376 -- These procedures describe static package memory usage.
377 -- The output collections describe memory usage
378 -- in each instantiated package. Each package is
379 -- described by its owner name, package name, type,
380 -- and memory statistics.
381 -- The amount of unused memory is greater than zero
382 -- because of memory fragmentation and also because
383 -- once used free memory chunks initially go to a free
384 -- list owned by the package memory heap. They are
385 -- released back to the parent heap only when
386 -- free_unused_user_memory is invoked.
387 --
388 -- Two overloadings are provided.
389 -- The first measures memory usage up to 2**31-1 (the maximum for
390 -- BINARY_INTEGER). It only measures used and free memory.
391 -- The second measures up to 10**38 (the maximum for INTEGER (which is
392 -- NUMBER(38,0).) This overloading takes an IN 'desired_info' array
393 -- specifying which kinds of information are desired. Currently the options
394 -- are the same as for the first overloading; but in the future, additional
395 -- kinds of information may be supported. The OUT 'amounts' array is indexed
396 -- by info_kind values specified in 'desired_info' to yield arrays which are
397 -- indexed in turn by the same integer used to index the other OUT arrays to
398 -- yield the requested kinds of information.
399
400 procedure use_default_edition_deferred;
401 -- This procedure disassociates the session from an edition. The reset
402 -- takes effect at the end of the current client call. Following this
403 -- call and before the next top level call, no edition will be in use
404 -- by the session. The session will use the reigning database default
405 -- edition on its next operation.
406 -- Input parameters:
407 -- none
408 end;
[Home] [Help]