2 Replies Latest reply on Apr 9, 2013 8:38 PM by Sergiusz Wolicki-Oracle

    NLS_DATABASE_PARAMETER is not reflecting the updated NLS_LENGTH_SEMANTICS.

    311152
      Hi,

      I have alter the DB with the alter system query to change to NLS_LENGTH_SEMANTICS, even though I restarted the DB the value is not refelceted when I
      query NLS_DATABASE_PARAMETER table.

      I executed the below query:

      ALTER SYSTEM SET nls_length_semantics=CHAR SCOPE=BOTH

      But when i checked v$PARAMETER the updated NLS_LENGTH_SEMANTICS reflects as updated one ( CHAR ).
      Why the NLS_DATABASE_PARAMETER is not reflecting the changes. ?
      Do I need to update the PROPS$ Table manually, so that NLS_DATABASE_PARAMETER will reflect it.

      Regards
      Suresh
        • 1. Re: NLS_DATABASE_PARAMETER is not reflecting the updated NLS_LENGTH_SEMANTICS.
          P.Forstmann
          NLS_DATABASE_PARAMETERS shows the NLS parameters that have been used at database creation time: these parameter values cannot be changed.

          NLS_INSTANCE_PARAMETERS show NLS parameters settings from SPFILE/PFILE and/or changed with ALTER SYSTEM:

          SQL> select * from nls_database_parameters where parameter like '%SEM%';
          
          PARAMETER                      VALUE
          ------------------------------ ----------------------------------------
          NLS_LENGTH_SEMANTICS           BYTE
          
          SQL> select * from nls_instance_parameters where parameter like '%SEM%';
          
          PARAMETER                      VALUE
          ------------------------------ ----------------------------------------
          NLS_LENGTH_SEMANTICS           BYTE
          
          SQL> alter system set nls_length_semantics=char;
          
          System altered.
          
          SQL> select * from nls_database_parameters where parameter like '%SEM%';
          
          PARAMETER                      VALUE
          ------------------------------ ----------------------------------------
          NLS_LENGTH_SEMANTICS           BYTE
          
          SQL> select * from nls_instance_parameters where parameter like '%SEM%';
          
          PARAMETER                      VALUE
          ------------------------------ ----------------------------------------
          NLS_LENGTH_SEMANTICS           CHAR
          
          SQL>
          • 2. Re: NLS_DATABASE_PARAMETER is not reflecting the updated NLS_LENGTH_SEMANTICS.
            Sergiusz Wolicki-Oracle
            Moreover, avoid setting NLS_LENGTH_SEMANTICS in spfile. Preferably, add explicit semantics keywords to column/variable definitions or use ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR in object creation scripts. If you set NLS_LENGTH_SEMANTICS in spfile, you must remember to reset it to BYTE each time you run an installation script for an application that does not expect character length semantics. This includes Oracle-supplied database scripts.


            -- Sergiusz