OracleStudy -- NLS_DATE_FORMAT system environment: Operating System: RedHatEL6 Database: Oracle11gR2 when setting the time format in RMAN, the error message is: how to set the NLS_DATE_FORMAT in Oracle ..
OracleStudy -- NLS_DATE_FORMAT system environment: Operating System: RedHatEL6 Database: Oracle11gR2 when setting the time format in RMAN, the error message is: how to set the NLS_DATE_FORMAT in Oracle ..
System Environment:
Operating System: RedHat EL6
Database: Oracle 11gR2
When the time format is set in RMAN, an error is returned:
How to Set NLS_DATE_FORMAT in Oracle
Category:
How to Set NLS_DATE_FORMAT in Oracle
Oracle Date Format defaults to: DD-MON-YY, which can be modified to take effect permanently in the following three ways;
1. Modify the glogin. SQL file. The path of this file is:
Windows:
% ORACLE_HOME \ sqlplus \ admin \
In Unix:
$ ORACLE_HOME/sqlplus/admin/
Add the following to the file:
Alter session set nls_date_format = 'yyyy-mm-dd hh24: mi: ss ';
2. Modify the Registry (windows)
Start --> Run --> regedit --> HKEY_LOCAL_MACHINE --> SOFTWARE --> ORACLE --> KEY_ORADb10g_home1
Create a 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 The RMAN session
Run {
...........
SQL 'alter session set NLS_DATE_FORMAT = "YYYY-MM-DD HH24: MI: SS ";
SQL 'alter session set NLS_LANG = "AMERICAN ";
...........
}
Oracle official explanation:
Explanation
Part1:
NLS_DATE_FORMAT is client centric, not server centric. this way each client can see dates the way he/she needs. 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... OT & p_id = 74375.1
NLS_DATE_FORMAT is used if a date format mask is not specified in application code. The valid tive 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.
This article is from the blog of "tianke's blog". Please keep this source