1

Oracle 12c (12.1.0.2.0) database

Issue: I wanted to change NLS_LANGUAGE and NLS_TERRITORY in NLS_INSTANCE_PARAMETERS. I know how to change for session, but I wanted to change it for the instance. The reason why is because stored procedure scripts weren't running correctly due to timestamp. I was getting this error:

start_date=>TO_TIMESTAMP_TZ('12-MAY-2015 02.00.00.000000000 AM +05:30','DD-MON-RRRR HH.MI.SSXFF AM TZR','NLS_DATE_LANGUAGE=english'), repeat_interval=>
ORA-01855: E necessario AM/A.M. ou PM/P.M. 

Reasononing: The NLS_DATE_FORMAT and the NLS_TIMESTAMP_FORMAT were the same across. I even tried unset LANG and unset NLS_LANG. Despite all of this I was still getting date conversion errors, so I decided to update all of the NLS-related parameters to the same values across all views. My database (nls_database_parameters) and session (nls_session_parameters) were showing the same NLS_LANGUAGE, but my instance (nls_instance_parameters) was not.

Solution: To fix this I had to update the init<db>.ora and the spfiles<db>.ora I didn't really see this explicitly specified anywhere except for maybe on Burleson Consulting's website, so I decided to put my findings here to help others with the same issue.

I updated NLS_LANGUAGE and NLS_TERRITORY in NLS_INSTANCE_PARAMETERS, restarted the database, and it fixed my issue.

I had to force restart because for some reason I couldn't restart normally.

3
  • I think the main purpose of NLS_LANGUAGE, NLS_TERRITORY, etc. (except CHARACTERSET) on database, resp. instance level is just to provide default values for according session level values which in fact apply. Fix the problem in your session, resp. in your code. Jan 31, 2017 at 14:04
  • Show us your actual problem - changing NLS_LANGUAGE and NLS_TERRITORY in NLS_INSTANCE_PARAMETERS is most likely the wrong solution! Jan 31, 2017 at 14:09
  • Thanks @WernfriedDomscheit ! It did seem weird that NLS_LANGUAGE and NLS_TERRITORY were affecting my date conversions, but after changing NLS_INSTANCE_PARAMETERS my issue was resolved. I added more details about my issue in the description. Overall I added this question to show people how to update this because I didn't see it easily explained anywhere. Thank you! Jan 31, 2017 at 16:43

1 Answer 1

0

Example below on how to update this:

[oracle@mydbserver dbs]$ unset LANG
[oracle@mydbserver dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 27 13:18:26 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT sys_context('userenv','language') from dual;

SYS_CONTEXT('USERENV','LANGUAGE')
--------------------------------------------------------------------------------
BRAZILIAN PORTUGUESE_BRAZIL.AL32UTF8

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@mydbserver dbs]$ unset NLS_LANG
[oracle@mydbserver dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 27 13:18:51 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> SELECT sys_context('userenv','language') from dual;

SYS_CONTEXT('USERENV','LANGUAGE')
--------------------------------------------------------------------------------
BRAZILIAN PORTUGUESE_BRAZIL.AL32UTF8

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

This is where I edited the init and spfile - you might want to take a backup !!

[oracle@mydbserver dbs]$ ls
hc_scpp.dat  init.ora  initscpp.ora  lkSCPP  orapwmydb  spfilemydb.ora  teste.ora

edit initscpp.ora and spfilemydb.ora with the values you want for the NLS parameters Then I proceeded to restart

[oracle@mydbserver dbs]$ sqlplus "/ as sysdba"
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 27 13:25:30 2017
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> shutdown
^CORA-01013: o usuario solicitou o cancelamento da operac?o atual

shutdown was slow so I [ctrl+c] and tried to force restart then restart normally !!

SQL> startup force
ORACLE instance started.

Total System Global Area 2,0724E+10 bytes
Fixed Size                  3158000 bytes
Variable Size            3070230544 bytes
Database Buffers         1,7645E+10 bytes
Redo Buffers                5230592 bytes
Database mounted.
Database opened.
SQL> shutdown
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 2.0724E+10 bytes
Fixed Size                  3158000 bytes
Variable Size            3070230544 bytes
Database Buffers         1.7645E+10 bytes
Redo Buffers                5230592 bytes
Database mounted.
Database opened.
SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@mydbserver dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 27 13:31:17 2017

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>  SELECT sys_context('userenv','language') from dual;

SYS_CONTEXT('USERENV','LANGUAGE')
--------------------------------------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

SQL> quit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.