Oracle Study之--NLS_DATE_FORMAT

创新互联建站是一家集网站建设,北林企业网站建设,北林品牌网站建设,网站定制,北林网站建设报价,网络营销,网络优化,北林网站推广为一体的创新建站企业，帮助传统企业提升企业形象加强企业竞争力。可充分满足这一群体相比中小企业更为丰富、高端、多元的互联网需求。同时我们时刻保持专业、时尚、前沿，时刻以成就客户成长自我，坚持不断学习、思考、沉淀、净化自己，让我们为更多的企业打造出实用型网站。

系统环境：

   操作系统：RedHat EL6

   数据库：   Oracle 11gR2

在RMAN中设置时间格式时，报错：

Oracle中 NLS_DATE_FORMAT的设置方法

分类：
Oracle中 NLS_DATE_FORMAT的设置方法
Oracle日期格式默认为：DD-MON-YY，以下是3种方法可以修改使之永久生效；

1、修改glogin.sql文件，这个文件的路径为：
Windows下：
%ORACLE_HOME\sqlplus\admin\
Unix下：
$ORACLE_HOME/sqlplus/admin/
在文件中加入：
alter session set nls_date_format = 'yyyy-mm-dd hh34:mi:ss';

2、修改注册表(windows平台)
开始-->运行-->regedit-->HKEY_LOCAL_MACHINE-->SOFTWARE-->ORACLE-->KEY_ORADb10g_home1
新建字符串值：
NLS_DATE_FORMAT,并设置其值为：YYYY-MM-DD:HH24:MI:SS

3、修改用户环境变量(Unix平台)
export NLS_DATE_FORMAT =AMERICAN
export NLS_DATE_FORMAT ='YYYY-MM-DD HH24:MI:SS'
RMAN会话中设置NLS_DATE_FORMAT 和NLS_LANG
run {
...........
sql 'alter session set NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS";
sql 'alter session set NLS_LANG ="AMERICAN";
...........
}

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.

另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。

文章名称：OracleStudy之--NLS_DATE_FORMAT-创新互联
网页路径：http://lswzjz.com/article/dcpdpj.html