OracleStudy -- NLS_DATE_FORMAT

Source: Internet
Author: User
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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.