The NLS_CHARACTER and NLS_NCHAR_CHARACTER parameters which are related to the database character set cannot be modified after the instance is created. The current OceanBase Database version supports the modification of only NLS parameters that specify the time and date formats.
NLS parameters are categorized into database-level NLS parameters and session-level NLS parameters. Only a user with the administrator privileges (the sys user by default) under the tenant is allowed to modify the database-level NLS parameters.
Modify database-level NLS parameters
Usually, you can modify database-level NLS parameters inside an Oracle tenant in either of the following two ways:
Run the ALTER SYSTEM SET command.
Run the SET GLOBAL command.
Example: Modify database-level NLS parameters
obclient> select * from nls_database_parameters where parameter in ('NLS_DATE_FORMAT','NLS_TIMESTAMP_FORMAT','NLS_TIMESTAMP_TZ_FORMAT');
+-------------------------+------------------------------+
| PARAMETER | VALUE |
+-------------------------+------------------------------+
| NLS_DATE_FORMAT | DD-MON-RR |
| NLS_TIMESTAMP_FORMAT | DD-MON-RR HH.MI.SSXFF AM |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
+-------------------------+------------------------------+
3 rows in set (0.00 sec)
obclient> alter system set nls_date_format='YYYY-MM-DD';
Query OK, 0 rows affected (0.01 sec)
obclient> set global nls_timestamp_format='YYYY-MM-DD HH24:MI:SSXFF';
Query OK, 0 rows affected (0.01 sec)
obclient> set global nls_timestamp_tz_format='YYYY-MM-DD HH24:MI:SSXFF';
Query OK, 0 rows affected (0.01 sec)
obclient> select * from nls_database_parameters where parameter in ('NLS_DATE_FORMAT','NLS_TIMESTAMP_FORMAT','NLS_TIMESTAMP_TZ_FORMAT');
+-------------------------+--------------------------+
| PARAMETER | VALUE |
+-------------------------+--------------------------+
| NLS_DATE_FORMAT | YYYY-MM-DD |
| NLS_TIMESTAMP_FORMAT | YYYY-MM-DD HH24:MI:SSXFF |
| NLS_TIMESTAMP_TZ_FORMAT | YYYY-MM-DD HH24:MI:SSXFF |
+-------------------------+--------------------------+
3 rows in set (0.00 sec)
obclient> select * from nls_session_parameters where parameter in ('NLS_DATE_FORMAT','NLS_TIMESTAMP_FORMAT','NLS_TIMESTAMP_TZ_FORMAT');
+-------------------------+------------------------------+
| PARAMETER | VALUE |
+-------------------------+------------------------------+
| NLS_DATE_FORMAT | DD-MON-RR |
| NLS_TIMESTAMP_FORMAT | DD-MON-RR HH.MI.SSXFF AM |
| NLS_TIMESTAMP_TZ_FORMAT | DD-MON-RR HH.MI.SSXFF AM TZR |
+-------------------------+------------------------------+
3 rows in set (0.00 sec)
It can be learned from the foregoing example that the modification of database-level NLS parameters is not applied to the current session. The modification takes effect only after the session is disconnected and then reconnected.
obclient> select sysdate, systimestamp from dual;
+-----------+-------------------------------------+
| SYSDATE | SYSTIMESTAMP |
+-----------+-------------------------------------+
| 02-APR-20 | 02-APR-20 07.50.58.427570 PM +08:00 |
+-----------+-------------------------------------+
1 row in set (0.00 sec)
obclient> show session variables where variable_name in ('nls_date_format','nls_timestamp_format','nls_timestamp_tz_format');
+-------------------------+------------------------------+
| VARIABLE_NAME | VALUE |
+-------------------------+------------------------------+
| nls_date_format | DD-MON-RR |
| nls_timestamp_format | DD-MON-RR HH.MI.SSXFF AM |
| nls_timestamp_tz_format | DD-MON-RR HH.MI.SSXFF AM TZR |
+-------------------------+------------------------------+
3 rows in set (0.01 sec)
obclient>
Modify session-level NLS parameters
You can specify, for a client session, NLS parameters that are different from those for the database. The current OceanBase Database version supports the modification of only NLS parameters that specify the time and date formats.
You can modify session-level NLS parameters in either of the following two ways:
Run the ALTER SESSION SET command.
Run the SET [SESSION] VARIABLE command.
Example: Modify session-level NLS parameters
obclient> alter session set nls_date_format='YYYY/MM/DD';
Query OK, 0 rows affected (0.00 sec)
obclient> set session nls_timestamp_format='YYYY/MM/DD HH.MI.SSXFF AM';
Query OK, 0 rows affected (0.00 sec)
obclient> set session nls_timestamp_tz_format='YYYY/MM/DD HH.MI.SSXFF AM TZR';
Query OK, 0 rows affected (0.00 sec)
obclient> select sysdate, systimestamp from dual;
+------------+--------------------------------------+
| SYSDATE | SYSTIMESTAMP |
+------------+--------------------------------------+
| 2020/04/02 | 2020/04/02 07.51.52.254705 PM +08:00 |
+------------+--------------------------------------+
1 row in set (0.00 sec)
obclient> show variables where variable_name in ('nls_date_format','nls_timestamp_format','nls_timestamp_tz_format');
+-------------------------+-------------------------------+
| VARIABLE_NAME | VALUE |
+-------------------------+-------------------------------+
| nls_date_format | YYYY/MM/DD |
| nls_timestamp_format | YYYY/MM/DD HH.MI.SSXFF AM |
| nls_timestamp_tz_format | YYYY/MM/DD HH.MI.SSXFF AM TZR |
+-------------------------+-------------------------------+
3 rows in set (0.01 sec)
obclient>
The modification of the session-level NLS parameters takes effect only in the current session. If the session is disconnected and then reconnected, the session takes the settings of the database-level NLS parameters as the initial values of the session-level NLS parameters