Alter Session in Oracle with Examples

Back to: Oracle DBA Tutorials

Alter Session in Oracle with Examples

In this article, I am going to discuss Alter Session in Oracle with Examples. Please read our previous article where we discussed Modifying Initialization Parameters in Oracle with Examples.

Alter Session in Oracle with Examples

Until now we have learnt about the initialization parameters and learnt about how we can find the parameters in the oracle reference manual. In this article, we will try to modify any of the initialization parameters or basic parameters. We will modify using the alter session.

Note: Open the Linux machine and start the database. Once the database is up and running open the pluggable database as well. If all the previous steps are done then open the SQL developer and log in as sysdba.

Step1: We will run a query using the data dictionary V$PARAMETER. Using this V$PARAMETER we can view all the parameters in the current session.

Query: select name, value from V$PARAMETER order by name;

You can see the output as the name of the parameter and the value of the parameter. You can count the rows of this parameter by right-clicking on the output.

We have 445 rows because we are on the oracle 19c database. Even the expert database admin does not know all this information. Some of the values are simple and we can learn them easily.

Step2: We will run a query using the data dictionary v$PARAMETER to check the date format in the session using the name ‘nls_date_format’ and check if the parameter can be modified at the session level or system level or else in the pluggable database level.

Query: select name,value ,isses_modifiable,issys_modifiable,ispdb_modifiable from V$PARAMETER where name =’nls_date_format’;

We can see the output the value of the nls_date_format is null. We can see the ISSES_MODIFIABLE is TRUE and FALSE for ISSYS_MODIFIABLE and TRUE for ISPDB_MODIFIABLE. This indicates that the parameter nls_date_format can be modified during the session. But this parameter cannot be modified for the system level and also can be modified at the PDB level.

Note: NULL does not mean that the value of the parameter is NULL. This indicates a derived parameter from another parameter NLS_TERRITORY.

Step3: Let us run a query to check the date in our session using a simple query.

Query: select sysdate from dual;

We can see the date as 08-OCT-22 because the format is configured in the database as DD-MONTH-RR. We will try to change the format of the date and check how the parameter behaves in the database. We will be changing the date to format “dd-mm-yyyy” using the below command.

Command: alter session set nls_date_format = ‘dd-mm-yyyy’;

Now let us try to check whether the changes are affected my session. So, we need to run the same query to check the date format of the nls_date_format.

Query: select name,value ,isses_modifiable,issys_modifiable,ispdb_modifiable from V$PARAMETER where name =’nls_date_format’;

So, by looking at the output we can see the date format is changed to dd-mm-yyyy. But affects only this session. But other sessions will be able to see the old value. We have seen the date format in our session. Let us try to verify the format by checking the date in the database.

Query: select sysdate from dual;

So, the format has changed from 08-OCT-22 to 08-10-2022. Let us try again to change the date format and see if it is working fine or not. We will be changing the format of the month from mm to month using the command:

Command: alter session set nls_date_format = ‘dd-month-yyyy’;

So, we have changed the date format again. Let us query to check the date format and check the date in our current session.

Query: select name,value ,isses_modifiable,issys_modifiable,ispdb_modifiable from V$PARAMETER where name =’nls_date_format’;

Query: select sysdate from dual;

So, the changes were reflected effectively. These changes will be applied to only your session. Other sessions or other changes will not come across these changes. Even if we log out from our session and login back we these changes will no longer be available.

Note: The SQL Developer can also make these date changes at the database level. For these types of setting you to have to click on Tools and select Preferences in the SQL developer.

The above image is the Preference setting in the tools. Please select the database and select NLS.

After selecting NLS you can able to see the date settings in the database and other settings related to the database.

If you make any changes, it’s just the alter statement running in the background of this SQL developer. These changes will be applied whenever you open the SQL developer. This is Environment Variable page in the SQL developer.

Step4: Now let us try to run a query which displays only parameters that can be modified at the session level. We need to select name, value, isses_modifiable (session_modifiable), issys_modifiable (system_modifiable) and ispdb_modifiable (pluggable database modifiable) from the parameter v$PARAMETER where TRUE value for isses_modifiable.

Query: select name,value, isses_modifiable,issys_modifiable,ispdb_modifiable from V$PARAMETER where isses_modifiable=’TRUE’;

Let us try to check the number of rows.

You can see that 182 rows are present. So, we can modify 182 parameters at the session level. So, we have learnt about how we can change any parameter related to the session and we have changed the date format parameter in the database.

In the next article, I am going to discuss V$PARAMETER vs V$PARAMETER2 in Oracle with Examples. Here, in this article, I try to explain Alter Session in Oracle with Examples and I hope you enjoy this Alter Session in Oracle article.