Oracle Study-- NLS_DATE_FORMAT

Oracle Study-- NLS_DATE_FORMAT

System environment:

Operating system: RedHat EL6

Database: Oracle 11gR2

When setting the time format in RMAN, an error is reported:

The setting method of NLS_DATE_FORMAT in Oracle

Classification:

The setting method of NLS_DATE_FORMAT in Oracle

The Oracle date format defaults to DD-MON-YY. Here are three ways to modify it to make it permanent.

1. Modify the glogin.sql file. The path to this file is:

Under Windows:

% ORACLE_HOME\ sqlplus\ admin\

Under Unix:

$ORACLE_HOME/sqlplus/admin/

Add to the file:

Alter session set nls_date_format = 'yyyy-mm-dd hh34:mi:ss'

2. Modify the registry (windows platform)

Start-- > run-- > regedit-- > HKEY_LOCAL_MACHINE-- > SOFTWARE-- > ORACLE-- > KEY_ORADb10g_home1

Create a new string value:

NLS_DATE_FORMAT and set its value to: YYYY-MM-DD:HH24:MI:SS

3. Modify user environment variables (Unix platform)

Export NLS_DATE_FORMAT = AMERICAN

Export NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'

Set NLS_DATE_FORMAT and NLS_LANG in a RMAN session

Run {

.

Sql 'alter session set NLS_DATE_FORMAT= "YYYY-MM-DD HH24:MI:SS"

Sql 'alter session set NLS_LANG = "AMERICAN"

.

}

The official explanation of Oracle:

Explanation

Part1:

NLS_DATE_FORMAT is client centric, not server centric. This way each client can see dates the way he/she needs to. For example clent in Europe is used to dates in DD/MM/YY format while clients in the US are used to MM/DD/YY. Therefore, each client needs to set NLS_DATE_FORMAT on the client box. In Windows, start regedit, go to HKEY_Local_Machine, Software, Oracle and add entry NLS_DATE_FORMAT = 'MM/DD/YYYY'. On Unix set environment variable NLS_DATE_FORMAT.

Part2:

Part1 is not entirely true.

NLS_DATE_FORMAT is not that straight forward. And explanation I gave you is not entirely true. There is a lot of articles in Oracle's MetaLink on that topic. If you are MetaLink customer check http://support.oracle.com.sg/met... OT&p_id=74375.1

NLS_DATE_FORMAT is used if a date format mask is not specified in application code. The effective NLS_DATE_FORMAT is determined by the following (in order of precedence):

1. Session NLS_DATE_FORMAT (via ALTER SESSION command)

2. Client side NLS_DATE_FORMAT (from client environment variables/registry settings)

3. Instance NLS_DATE_FORMAT (from init.ora file)

4. Database NLS_DATE_FORMAT

Session NLS_DATE_FORMAT is set to client side NLS_DATE_FORMAT (explicit or implicit) ONLY if NLS_LANG is set. Another words, if NLS_LANG is set, then session NLS_DATE_FORMAT will be taken from the client. If clent NLS_LANG is set and client NLS_DATE_FORMAT is not, session NLS_DATE_FORMAT will default to DD-MON-YY (and that was exactly what you experienced). If NLS_LANG is not specified on the client side, NLS_DATE_FORMAT will be taken from instance NLS_DATE_FORMAT which is NLS_DATE_FORMAT from INIT.ORA. If NLS_DATE_FORMAT is not set in INIT.ORA, session NLS_DATE_FORMAT will default to DD-MON-YY.