Session Control Statements

      Session control statements are dynamically manages the properties of a user session and don’t implicitly commit the current transaction.

Note : PL/SQL doesn’t support session control statements.

Following are the Session control statements.

ALTER SESSION : This statement is used  set or modify  the conditions or parameters that affect your connection to the database. The statement stays in effect until session closed.

Example :

Changing the date format dynamically

SQL> SELECT TO_CHAR(SYSDATE) TODAY FROM DUAL;

TODAY
---------
01-JAN-17

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY MM DD HH24:MI:SS';

Session altered.

SQL> SELECT TO_CHAR(SYSDATE) TODAY FROM DUAL;

TODAY
-------------------
2017 01 01 20:12:31

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH:MI:SS';

Session altered.

SQL> SELECT TO_CHAR(SYSDATE) TODAY FROM DUAL;

TODAY
-------------------
2017-01-01 08:13:20

SQL>

      We can not modified all parameters by using ALTER  SESSION. We can check what parameters can be modify by looking at V$PARAMETER view.

SQL> DESC V$PARAMETER;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM                                                NUMBER
 NAME                                               VARCHAR2(80)
 TYPE                                               NUMBER
 VALUE                                              VARCHAR2(4000)
 DISPLAY_VALUE                                      VARCHAR2(4000)
 ISDEFAULT                                          VARCHAR2(9)
 ISSES_MODIFIABLE                                   VARCHAR2(5)
 ISSYS_MODIFIABLE                                   VARCHAR2(9)
 ISINSTANCE_MODIFIABLE                              VARCHAR2(5)
 ISMODIFIED                                         VARCHAR2(10)
 ISADJUSTED                                         VARCHAR2(5)
 ISDEPRECATED                                       VARCHAR2(5)
 ISBASIC                                            VARCHAR2(5)
 DESCRIPTION                                        VARCHAR2(255)
 UPDATE_COMMENT                                     VARCHAR2(255)
 HASH                                               NUMBER

SQL>

We can see all parameters in V$PARAMETER view using SELECT NAME FROM V$PARAMETER;. Also we can see the parameters which can be modified with ALTER SESSION statements  by using below query.

SQL> SELECT NAME FROM V$PARAMETER WHERE ISSES_MODIFIABLE='TRUE';

Also, we can see if any parameter can be modified with ALTER SESSION or if it is modifiable at the instance level by using below query.

SQL> SELECT NAME,ISSES_MODIFIABLE,ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE
 2 FROM V$PARAMETER WHERE NAME='nls_date_format';

NAME
--------------------------------------------------------------------------------
ISSES ISSYS_MOD ISINS
----- --------- -----
nls_date_format
TRUE  FALSE     FALSE


SQL>

 

SET ROLE : This statement is used to enable or disable roles which have been granted. 

      When use logs on, Oracle Database enables all privileges granted explicitly to the user and all privileges in the user’s default roles. During the session, the user can use SET ROLE statement to change the roles which are currently enabled  in any number of times for the current session.

Note : You cannot enable more than 148 user-defined roles at one time.

Setting roles:

Enable particular role for current  session:

SET ROLE Dev_Role IDENTIFIED BY DevP12#$;

Enable all roles which are granted for current  session. 

SET ROLE dw_manager IDENTIFIED BY warehouse;

Enable all roles except particular one for current  session.

SET ROLE ALL EXCEPT Prog_Role;

Disable all roles for current  session.

SET ROLE NONE;

Advertisements

Post your Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s