Home » RDBMS Server » Server Administration » nls_numeric_characters doesn't change after reboot (Oracle 11g , windows8)
nls_numeric_characters doesn't change after reboot [message #673723] Thu, 06 December 2018 13:30 Go to next message
Andrey_R
Messages: 277
Registered: January 2012
Location: Israel
Senior Member

Hi all,
I have this database on my laptop, in which I am testing the nls_numeric_characters param:

SQL> show user
USER is "SYS"
SQL>
SQL> show parameter nls_numeric_character

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_numeric_characters               string      .,
SQL>
SQL> select 1+'0,1' from dual;
select 1+'0,1' from dual
         *
ERROR at line 1:
ORA-01722: invalid number

So I try to change it with alter system :

SQL> alter system set nls_numeric_characters=',.' scope=both;
alter system set nls_numeric_characters=',.' scope=both
                 *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option


SQL>

got an error..
So ok, I am just changing in spfile:

SQL> alter system set nls_numeric_characters=',.' scope=spfile;

System altered.

SQL>


Then I reboot and try to see if it changed:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1837244416 bytes
Fixed Size                  2256184 bytes
Variable Size            1157628616 bytes
Database Buffers          671088640 bytes
Redo Buffers                6270976 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> select 1+'0,1' from dual;
select 1+'0,1' from dual
         *
ERROR at line 1:
ORA-01722: invalid number


SQL>
SQL>
SQL> show parameter nls_numeric_characters

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_numeric_characters               string      .,
SQL>
SQL>
SQL>







It haven't.

Anybody can explain why ??

Thanks in advance.
Andrey

Re: nls_numeric_characters doesn't change after reboot [message #673726 is a reply to message #673723] Thu, 06 December 2018 14:05 Go to previous messageGo to next message
Michel Cadot
Messages: 66049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
SQL> show user
USER is "SYS"
Read SYS is special.

SQL> show user
USER is "MICHEL"

SQL> alter session set NLS_NUMERIC_CHARACTERS='.,';

Session altered.

SQL> select 1+'0,1' from dual;
select 1+'0,1' from dual
         *
ERROR at line 1:
ORA-01722: invalid number


SQL> alter session set NLS_NUMERIC_CHARACTERS=',.';

Session altered.

SQL> select 1+'0,1' from dual;
   1+'0,1'
----------
       1,1

1 row selected.
Re: nls_numeric_characters doesn't change after reboot [message #673826 is a reply to message #673726] Tue, 11 December 2018 08:55 Go to previous messageGo to next message
Andrey_R
Messages: 277
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Thu, 06 December 2018 22:05

Quote:
SQL> show user
USER is "SYS"
Read SYS is special.

SQL> show user
USER is "MICHEL"

SQL> alter session set NLS_NUMERIC_CHARACTERS='.,';

Session altered.

SQL> select 1+'0,1' from dual;
select 1+'0,1' from dual
         *
ERROR at line 1:
ORA-01722: invalid number


SQL> alter session set NLS_NUMERIC_CHARACTERS=',.';

Session altered.

SQL> select 1+'0,1' from dual;
   1+'0,1'
----------
       1,1

1 row selected.


I tried the same with a regular user.. didn't work:

SQL> show user
USER is "SYS"
SQL>
SQL>
SQL> create user a identified by a;

User created.

SQL> grant dba to a;

Grant succeeded.

SQL>
SQL>
SQL> conn a/a
Connected.
SQL>
SQL>
SQL> show parameter nls_num

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_numeric_characters               string      .,
SQL>
SQL>
SQL> alter system set nls_numeric_characters=',.' scope=spfile;

System altered.

SQL> show parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\ORACLE\APP\ORACLE\PRODUCT\1
                                                 1.2.0\DBHOME_1\DATABASE\SPFILE
                                                 MAGIC.ORA
SQL>
SQL>
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1837244416 bytes
Fixed Size                  2256184 bytes
Variable Size            1157628616 bytes
Database Buffers          671088640 bytes
Redo Buffers                6270976 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> show parameter nls_num

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_numeric_characters               string      .,
SQL>




Re: nls_numeric_characters doesn't change after reboot [message #673827 is a reply to message #673826] Tue, 11 December 2018 10:05 Go to previous messageGo to next message
Michel Cadot
Messages: 66049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
SQL> conn / as sysdba
Connected.
...
SQL> show parameter nls_num
As far as I can see you are still SYS.

Re: nls_numeric_characters doesn't change after reboot [message #673828 is a reply to message #673827] Tue, 11 December 2018 10:24 Go to previous messageGo to next message
Andrey_R
Messages: 277
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Tue, 11 December 2018 18:05

Quote:
SQL> conn / as sysdba
Connected.
...
SQL> show parameter nls_num
As far as I can see you are still SYS.


I had to switch to SYS in order to reboot the database.

I did the action of parameter change with user A:


SQL> conn a/a
Connected.
SQL>
SQL>
SQL> show parameter nls_num

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_numeric_characters               string      .,
SQL>
SQL>
SQL> alter system set nls_numeric_characters=',.' scope=spfile;

System altered.
Re: nls_numeric_characters doesn't change after reboot [message #673829 is a reply to message #673828] Tue, 11 December 2018 10:29 Go to previous messageGo to next message
Michel Cadot
Messages: 66049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I had to switch to SYS in order to reboot the database.
I understand that but you still did not validate that this "does not work", switch then to user A to do "show parameter".

Re: nls_numeric_characters doesn't change after reboot [message #673830 is a reply to message #673829] Tue, 11 December 2018 10:56 Go to previous messageGo to next message
Andrey_R
Messages: 277
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Tue, 11 December 2018 18:29

Quote:
I had to switch to SYS in order to reboot the database.
I understand that but you still did not validate that this "does not work", switch then to user A to do "show parameter".



Please see below:


SQL> conn a/a
Connected.
SQL>
SQL>
SQL> show user
USER is "A"
SQL>
SQL>
SQL> show parameter nls_numeric

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_numeric_characters               string      .,
SQL>
Re: nls_numeric_characters doesn't change after reboot [message #673831 is a reply to message #673830] Tue, 11 December 2018 10:58 Go to previous messageGo to next message
Michel Cadot
Messages: 66049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post complete version number.

Re: nls_numeric_characters doesn't change after reboot [message #673832 is a reply to message #673831] Tue, 11 December 2018 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 66049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Check environment parameters if nls_numeric_characters is not set.

Re: nls_numeric_characters doesn't change after reboot [message #673833 is a reply to message #673831] Tue, 11 December 2018 11:01 Go to previous messageGo to next message
Andrey_R
Messages: 277
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Tue, 11 December 2018 18:58

Post complete version number.



SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE    11.2.0.3.0      Production
TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production


Re: nls_numeric_characters doesn't change after reboot [message #673834 is a reply to message #673832] Tue, 11 December 2018 11:02 Go to previous messageGo to next message
Andrey_R
Messages: 277
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Tue, 11 December 2018 18:59

Check environment parameters if nls_numeric_characters is not set.


You mean, like this ?


SQL> select value
  2  from nls_session_parameters
  3  where parameter = 'NLS_NUMERIC_CHARACTERS';

VALUE
----------------------------------------
.,

Re: nls_numeric_characters doesn't change after reboot [message #673835 is a reply to message #673834] Tue, 11 December 2018 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 66049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No I meant at OS level(SET, computer environment, registry).

Re: nls_numeric_characters doesn't change after reboot [message #673836 is a reply to message #673835] Tue, 11 December 2018 11:39 Go to previous messageGo to next message
Andrey_R
Messages: 277
Registered: January 2012
Location: Israel
Senior Member

Michel Cadot wrote on Tue, 11 December 2018 19:08

No I meant at OS level(SET, computer environment, registry).


- I searched for "nls_numeric_characters" in the registry - found nothing
- No set explicitly specified nor set up before the sqlplus execution - opened CMD and typed sqlplus...
- Checked environment variables - found nothing related to this param..

Any other suggestions?
Re: nls_numeric_characters doesn't change after reboot [message #673837 is a reply to message #673836] Tue, 11 December 2018 12:24 Go to previous messageGo to next message
Michel Cadot
Messages: 66049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

v$parameter (and show parameter) gives the session parameters.
v$system_parameter gives the instance (or system) parameters.
Check this later one.
Also check nls_database_parameters (nls_instance_parameters is the same as v$system_parameter).

(I assume you altered the parameter, restarted and checked in the same instance if you are in RAC.)

Re: nls_numeric_characters doesn't change after reboot [message #673838 is a reply to message #673836] Tue, 11 December 2018 12:59 Go to previous messageGo to next message
joy_division
Messages: 4917
Registered: February 2005
Location: East Coast USA
Senior Member
I think I remember seeing once that this only uneatable for the session and not instance. It's like the characterset. You cannot change it for the database. You have to create a new database. "I think"
SCOTT@ORCLSQL> l
  1  selecT * from v$system_parameter
  2  where NAME like 'nls%'
  3* and num=283

             NUM NAME                                       TYPE VALUE
---------------- ------------------------------ ---------------- ------------
DISPLAY_VALUE
---------------------------------------------------------------------------------------------------
DEFAULT_VALUE
---------------------------------------------------------------------------------------------------
ISDEFAULT ISSES ISSYS_MOD ISPDB ISINS ISMODIFI ISADJ ISDEP ISBAS DESCRIPT
--------- ----- --------- ----- ----- -------- ----- ----- ----- --------
UPDATE_COMMENT
---------------------------------------------------------------------------------------------------
            HASH           CON_ID
---------------- ----------------
             283 nls_numeric_characters                        2


TRUE      TRUE  FALSE     TRUE  FALSE FALSE    FALSE FALSE FALSE NLS nume

      1350504691                0

ISSYS_MODIFIABLE = FALSE
ISINSTANCE_MODIFIABLE = FALSE
ISMODIFIED = FALSE
ISADJUSTED = FALSE
ISDEPRECATED = FALSE
ISBASIC = FALSE

[Updated on: Tue, 11 December 2018 13:04]

Report message to a moderator

Re: nls_numeric_characters doesn't change after reboot [message #673839 is a reply to message #673838] Tue, 11 December 2018 13:12 Go to previous messageGo to next message
Michel Cadot
Messages: 66049
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Indeed, we should first read the documentation. Laughing

Re: nls_numeric_characters doesn't change after reboot [message #673849 is a reply to message #673839] Wed, 12 December 2018 07:39 Go to previous message
joy_division
Messages: 4917
Registered: February 2005
Location: East Coast USA
Senior Member
I am always happy when I remember something. It reassures me that I am not "losing it." I fear that day is coming sooner rather than later.
However, I don't remember WHERE or WHENit was that I remember it from.
Previous Topic: Table Partitioning
Goto Forum:
  


Current Time: Thu Dec 13 05:30:46 CST 2018