CODEWITHSUNDEEP
oracle-database
Muhammad
Muhammad

Reputation: 307

how to change the NLS_LANGUAGE and other parameters in oracle

i have NLS_LANGUAGE American, I want to change to ITALIAN, The database is oracle 11g.

I am trying the following 

ALTER DATABASE CHARACTER SET ITALIAN

but this gives error.

Upvotes: 2

Views: 53354

Answers (4)

Andy
Andy

Reputation: 128

There are several ways to modify NLS_LANGUAGE:

  1. Session level (temporary, affects only current session):
ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';
  1. System level (permanent, requires SYSDBA privileges):
ALTER SYSTEM SET NLS_LANGUAGE = 'AMERICAN' SCOPE = SPFILE;

Then restart the database for changes to take effect.

  1. Through environment variable:
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
  1. In init.ora/spfile.ora:
NLS_LANGUAGE = AMERICAN

For more details and considerations, have a look at this post.

Upvotes: 0

Ajay Takur
Ajay Takur

Reputation: 6236

Run at host level, setting env variables export LANG=en_US.UTF-8

export NLS_LANG=AMERICAN_AMERICA.AL32UTF8

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59436

I don't think you like to modify the NLS_LANGUAGE of the entire database, just on your local session.

Run ALTER SESSION SET NLS_LANGUAGE="ITALIAN";

Do you like to modify the NLS_LANGUAGE or the CHARACTER Set? These are two completely different things.

Upvotes: 2

nightfox79
nightfox79

Reputation: 2119

The default value of NLS_LANGUAGE may be operating system-specific. You can alter the NLS_LANGUAGE parameter by changing the value in the initialization file and then restarting the instance. This setting is in the init.ora or spfile.ora.

If you just want to change your session info for example to change the date format, but without changing server messages you can use alter session as follows:

SQL> ALTER SESSION SET NLS_LANGUAGE=Italian;
SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp;
ENAME     HIREDATE    SAL
-----     --------    ---
Clark     09-Dic-88   4195.83
Miller    23-Mar-87   4366.67
Strauß    01-Apr-95   3795.87

SQL> ALTER SESSION SET NLS_LANGUAGE=German;
SQL> SELECT ename, hiredate, ROUND(sal/12,2) sal FROM emp;
ENAME     HIREDATE    SAL
-----     --------    ---
Clark     09-DEZ-88   4195.83
Miller    23-MÄR-87   4366.67
Strauß    01-APR-95   3795.87

Upvotes: 2

Related Questions