DBA Hub

📋Steps in this guide1/5

DBMS_SESSION : Managing Sessions From a Connection Pool in Oracle Databases

A guide to using the DBMS_SESSION package to manage sessions that form part of a connection pool.

oracle miscconfigurationintermediate
by OracleDba
39 views
1

Client Identifier ( SET_IDENTIFIER and CLEAR_IDENTIFIER )

In many client-server applications it was common to allocate a different database user for each real user of the system. This made it simple to identify and audit the users at the database level. If applications used a single database user and managed security internally, this made identifying the real users of the system difficult. The issue was complicated further by multi-tier architectures that used connection pooling. To counter this, Oracle 9iR1 introduced the and procedures to allow the real user to be associated with a session, regardless of what database user was being used for the connection. The procedures amend the content of the column in the view. When a connection is taken from the connection pool the application should call the procedure, passing the real user information as a parameter. The information is visible in the view when queried from privileged user in another session. Before the session is released back into the connection pool, the application should call the procedure. The column of the view is blanked for the session. Later releases of Oracle included the information in the audit trail, SQL trace files and performance tuning tools, making it even more useful.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
CONN test/test

EXEC sys.DBMS_SESSION.set_identifier('tim_hall');

COLUMN username FORMAT A20
COLUMN client_identifier FORMAT A20

SELECT USER AS username, SYS_CONTEXT('userenv', 'client_identifier') AS client_identifier FROM dual;

USERNAME             CLIENT_IDENTIFIER
-------------------- --------------------
TEST                 tim_hall

SQL>

-- Start a new session, leaving the existing session running.
CONN / AS SYSDBA

COLUMN username FORMAT A20
COLUMN client_identifier FORMAT A20

SELECT username, client_identifier FROM v$session WHERE username = 'TEST';

USERNAME             CLIENT_IDENTIFIER
-------------------- --------------------
TEST                 tim_hall

SQL>

-- Back in the original session.
EXEC sys.DBMS_SESSION.clear_identifier;

SELECT USER AS username, SYS_CONTEXT('userenv', 'client_identifier') AS client_identifier FROM dual;

USERNAME             CLIENT_IDENTIFIER
-------------------- --------------------
TEST

SQL>

-- Back in the privileged session.
SELECT username, client_identifier FROM v$session WHERE username = 'TEST';

USERNAME             CLIENT_IDENTIFIER
-------------------- --------------------
TEST

SQL>
2

Contexts ( CLEAR_CONTEXT and CLEAR_ALL_CONTEXT )

Contexts are namespaces used to store name-value pairs. Although contexts were available in Oracle 8i, the procedure was not introduced until 9iR1, while the procedure was introduced in 10gR1. If a session is being used as part of a connection pool and the state of its contexts are not reinitialized, this can lead to unexpected behavior. Calling with the parameter set to NULL is similar to calling the procedure.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
CONN / AS SYSDBA

GRANT CREATE ANY CONTEXT TO test;

CONN test/test

-- Create the context.
CREATE OR REPLACE CONTEXT parameter_ctx USING context_api;

-- Create the package to manage the context.
CREATE OR REPLACE PACKAGE context_api AS
  PROCEDURE set_parameter(p_name   IN  VARCHAR2,
                          p_value  IN  VARCHAR2);
  PROCEDURE clear_context (p_name  IN  VARCHAR2);
  PROCEDURE clear_all_context;
END context_api;
/

CREATE OR REPLACE PACKAGE BODY context_api IS
  PROCEDURE set_parameter (p_name   IN  VARCHAR2,
                           p_value  IN  VARCHAR2) IS
  BEGIN
    sys.DBMS_SESSION.set_context('parameter_ctx', p_name, p_value);
  END set_parameter;

  PROCEDURE clear_context (p_name  IN  VARCHAR2) IS
  BEGIN
    sys.DBMS_SESSION.clear_context('parameter_ctx', attribute => p_name);
  END clear_context;

  PROCEDURE clear_all_context IS
  BEGIN
    sys.DBMS_SESSION.clear_all_context('parameter_ctx');
  END clear_all_context;
END context_api;
/

-- Set two values in the context and check them.
EXEC context_api.set_parameter('variable1', 'one');
EXEC context_api.set_parameter('variable2', 'two');

COLUMN variable1 FORMAT A20
COLUMN variable2 FORMAT A20

SELECT SYS_CONTEXT('parameter_ctx', 'variable1') AS variable1,
       SYS_CONTEXT('parameter_ctx', 'variable2') AS variable2
FROM   dual;

VARIABLE1            VARIABLE2
-------------------- --------------------
one                  two

SQL>

-- Clear one of the name-value pairs and retest.
EXEC context_api.clear_context('variable1');

SELECT SYS_CONTEXT('parameter_ctx', 'variable1') AS variable1,
       SYS_CONTEXT('parameter_ctx', 'variable2') AS variable2
FROM   dual;

VARIABLE1            VARIABLE2
-------------------- --------------------
                     two

SQL> 

-- Clear all name-value pairs and retest.
EXEC context_api.clear_all_context;

SELECT SYS_CONTEXT('parameter_ctx', 'variable1') AS variable1,
       SYS_CONTEXT('parameter_ctx', 'variable2') AS variable2
FROM   dual;

VARIABLE1            VARIABLE2
-------------------- --------------------


SQL>
3

Packages ( RESET_PACKAGE )

Sessions have the ability to alter package state by amending the values of package variables. If a session is being used as part of a connection pool and the state of its packages are not reinitialized, this can lead to unexpected behavior. To solve this, the procedure, available since Oracle 7, can be called to reinitialize all packages.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
-- Create a package with a package variable and GET/SET methods.
CREATE OR REPLACE PACKAGE p1 AS
  FUNCTION get_value RETURN NUMBER;
  PROCEDURE set_value (p_value IN NUMBER);
END p1;
/

CREATE OR REPLACE PACKAGE BODY p1 AS
  g_number NUMBER := 1;

  FUNCTION get_value RETURN NUMBER AS
  BEGIN
    RETURN g_number;
  END get_value;

  PROCEDURE set_value (p_value IN NUMBER) AS
  BEGIN
    g_number := p_value;
  END set_value;
END p1;
/

-- Check the current value.
SELECT p1.get_value FROM dual;

 GET_VALUE
----------
         1

SQL>

-- Alter the value and retest.
EXEC p1.set_value(2);

SELECT p1.get_value FROM dual;

 GET_VALUE
----------
         2

SQL>

-- Reset the package state and retest.
EXEC sys.DBMS_SESSION.reset_package;

SELECT p1.get_value FROM dual;

 GET_VALUE
----------
         1

SQL>
4

Memory ( FREE_UNUSED_USER_MEMORY )

If a session performs operations that cause it to allocate a large amount of memory in the PGA or UGA, this memory will not be reclaimed until the session disconnects. For sessions in the connection pool this can present a problem unless they are managed properly. As the name suggests, the procedure, available since Oracle 7, frees up unused memory in the session.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
CONN / AS SYSDBA

GRANT SELECT ON v_$mystat TO test;
GRANT SELECT ON v_$statname TO test;

CONN test/test

-- Create a package with a collection as a package variable.
CREATE OR REPLACE PACKAGE p1 AS
  FUNCTION get_pga_size RETURN NUMBER;
  PROCEDURE populate_tab;
  PROCEDURE empty_tab;
END p1;
/

CREATE OR REPLACE PACKAGE BODY p1 AS
  TYPE t_tab IS TABLE OF all_objects%ROWTYPE;
  g_tab t_tab;

  FUNCTION get_pga_size RETURN NUMBER AS
    l_number NUMBER;
  BEGIN
    SELECT ms.value
    INTO   l_number
    FROM   v$mystat ms
           JOIN v$statname sn ON sn.statistic# = ms.statistic#
    WHERE  sn.name = 'session pga memory';
    RETURN l_number;
  END get_pga_size;

  PROCEDURE populate_tab AS
  BEGIN
    SELECT *
    BULK COLLECT INTO g_tab
    FROM  all_objects;
  END populate_tab;

  PROCEDURE empty_tab AS
  BEGIN
    g_tab.delete;
  END empty_tab;
END p1;
/

-- Check the current PGA size.
SELECT p1.get_pga_size FROM dual;

GET_PGA_SIZE
------------
     3273264

SQL>

-- Populate the collection and retest.
EXEC p1.populate_tab;

SELECT p1.get_pga_size FROM dual;

GET_PGA_SIZE
------------
    47641136

SQL>

-- Empty the collection and retest.
EXEC p1.empty_tab;

SELECT p1.get_pga_size FROM dual;

GET_PGA_SIZE
------------
    47641136

SQL>

-- Free unused memory and retest.
EXEC sys.DBMS_SESSION.free_unused_user_memory;

SELECT p1.get_pga_size FROM dual;

GET_PGA_SIZE
------------
     5632560

SQL>
5

End-To-End Tracing From Java

From 12c onward is deprecated in favour of , which backwards compatible with . Thanks to Stefan Koehler for pointing this out. Java programs connecting to the database using JDBC can set the , and columns of the view without calls to the or packages. This makes it a more scalable solution, since extra calls to the database are not required. When a new connection (conn) is made, or a connection is pulled from the connection pool, the details are set using the following type of code. Before releasing a connection back to the connection pool, the details should be blanked. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
try {
  String e2eMetrics[] = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
  e2eMetrics[OracleConnection.END_TO_END_ACTION_INDEX]   = "Starting";
  e2eMetrics[OracleConnection.END_TO_END_MODULE_INDEX]   = "MyProgram";
  e2eMetrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = "tim_hall";
  // Pre-12c
  //((OracleConnection) conn).setEndToEndMetrics(e2eMetrics, (short) 0);
  // 12c Onward
  ((OracleConnection) conn).setClientInfo(e2eMetrics, (short) 0);
} catch (SQLException sqle) {
  // Do something...
}

try {
  String e2eMetrics[] = new String[OracleConnection.END_TO_END_STATE_INDEX_MAX];
  e2eMetrics[OracleConnection.END_TO_END_ACTION_INDEX]   = null;
  e2eMetrics[OracleConnection.END_TO_END_MODULE_INDEX]   = null;
  e2eMetrics[OracleConnection.END_TO_END_CLIENTID_INDEX] = null;
  // Pre-12c
  //((OracleConnection) conn).setEndToEndMetrics(e2eMetrics, Short.MIN_VALUE);
  // 12c Onward
  ((OracleConnection) conn).setClientInfo(e2eMetrics, Short.MIN_VALUE);
} catch (SQLException sqle) {
  // Do something...
}

Comments (0)

Please to add comments

No comments yet. Be the first to comment!