【Oracle 筆記 】語系支援 Language Support

使用者連上DB之後，查看目前的NLS參數設定 

不同使用者會因設定不同，而看到不同的內容。

SQL> select * from nls_session_parameters

    PARAMETER                 VALUE

1 NLS_LANGUAGE               AMERICAN
2 NLS_TERRITORY              AMERICA
3 NLS_CURRENCY               $
4 NLS_ISO_CURRENCY           AMERICA
5 NLS_NUMERIC_CHARACTERS     .,
6 NLS_CALENDAR               GREGORIAN
7 NLS_DATE_FORMAT            DD-MON-RR
8 NLS_DATE_LANGUAGE          AMERICAN
9 NLS_SORT                   BINARY
10 NLS_TIME_FORMAT           HH.MI.SSXFF AM
11 NLS_TIMESTAMP_FORMAT      DD-MON-RR HH.MI.SSXFF AM
12 NLS_TIME_TZ_FORMAT        HH.MI.SSXFF AM TZR
13 NLS_TIMESTAMP_TZ_FORMAT   DD-MON-RR HH.MI.SSXFF AM TZR
14 NLS_DUAL_CURRENCY         $
15 NLS_COMP BINARY
16 NLS_LENGTH_SEMANTICS      BYTE
17 NLS_NCHAR_CONV_EXCP       FALSE


查看目前 Oracle Instance NLS 參數設定:

SQL> select * from nls_instance_parameters

    PARAMETER                VALUE

1 NLS_LANGUAGE               AMERICAN
2 NLS_TERRITORY              america
3 NLS_SORT                   binary
4 NLS_DATE_LANGUAGE 
5 NLS_DATE_FORMAT            DD-MON-RR
6 NLS_CURRENCY 
7 NLS_NUMERIC_CHARACTERS     .,
8 NLS_ISO_CURRENCY 
9 NLS_CALENDAR 
10 NLS_TIME_FORMAT 
11 NLS_TIMESTAMP_FORMAT 
12 NLS_TIME_TZ_FORMAT 
13 NLS_TIMESTAMP_TZ_FORMAT 
14 NLS_DUAL_CURRENCY 
15 NLS_COMP binary
16 NLS_LENGTH_SEMANTICS      BYTE
17 NLS_NCHAR_CONV_EXCP       FALSE


查看目前 Oracle Database NLS 參數設定:

SQL> select * from nls_database_parameters

    PARAMETER                   VALUE

1 NLS_NCHAR_CHARACTERSET        AL16UTF16
2 NLS_LANGUAGE                  AMERICAN
3 NLS_TERRITORY                 AMERICA
4 NLS_CURRENCY                  $
5 NLS_ISO_CURRENCY              AMERICA
6 NLS_NUMERIC_CHARACTERS        .,
7 NLS_CHARACTERSET              UTF8
8 NLS_CALENDAR                  GREGORIAN
9 NLS_DATE_FORMAT               DD-MON-RR
10 NLS_DATE_LANGUAGE            AMERICAN
11 NLS_SORT                     BINARY
12 NLS_TIME_FORMAT              HH.MI.SSXFF AM
13 NLS_TIMESTAMP_FORMAT         DD-MON-RR HH.MI.SSXFF AM
14 NLS_TIME_TZ_FORMAT           HH.MI.SSXFF AM TZR
15 NLS_TIMESTAMP_TZ_FORMAT      DD-MON-RR HH.MI.SSXFF AM TZR
16 NLS_DUAL_CURRENCY            $
17 NLS_COMP                     BINARY
18 NLS_LENGTH_SEMANTICS         BYTE
19 NLS_NCHAR_CONV_EXCP          FALSE
20 NLS_RDBMS_VERSION            11.2.0.2.0

DBA 亦可使用 show parameter nls 指令查看 Database NLS 的設定。

若要修改 NLS 參數設定，則要先查詢 Oracle 提供那些參數的設定值。這些設定值可以從 Data Dictionary中的 v$nls_valid_valuses  這 View 得知。

SQL> select * from v$nls_valid_values where value in ('AL16UTF16','AMERICAN','AMERICA','UTF8');

    PARAMETER                VALUE            ISDEPRECATED

1 LANGUAGE                   AMERICAN         FALSE
2 TERRITORY                  AMERICA          FALSE
3 CHARACTERSET               UTF8             FALSE
4 CHARACTERSET               AL16UTF16        FALSE

修改 NLS 參數設定

SQL> select * from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';

    PARAMETER          VALUE

1 NLS_DATE_FORMAT      DD-MON-RR


修改 nls_data_format 的參數設定:

SQL> alter session set nls_date_format = 'MM/DD/YY';


確認修改是否成功:

SQL> select * from nls_session_parameters;

    PARAMETER                   VALUE
1 NLS_LANGUAGE                  AMERICAN
2 NLS_TERRITORY                 AMERICA
3 NLS_CURRENCY                  $
4 NLS_ISO_CURRENCY              AMERICA
5 NLS_NUMERIC_CHARACTERS        .,
6 NLS_CALENDAR                  GREGORIAN
7 NLS_DATE_FORMAT              MM/DD/YY
8 NLS_DATE_LANGUAGE            AMERICAN
9 NLS_SORT           BINARY
10 NLS_TIME_FORMAT             HH.MI.SSXFF AM
11 NLS_TIMESTAMP_FORMAT        DD-MON-RR HH.MI.SSXFF AM
12 NLS_TIME_TZ_FORMAT          HH.MI.SSXFF AM TZR
13 NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZR
14 NLS_DUAL_CURRENCY           $
15 NLS_COMP                    BINARY
16 NLS_LENGTH_SEMANTICS        BYTE
17 NLS_NCHAR_CONV_EXCP         FALSE