Oracle Study之--NLS_DATE_FORMAT

旧城等待， 2022-08-08 09:16 199阅读 0赞

**Oracle Study之--NLS\_DATE\_FORMAT**

**系统环境：**

**     操作系统：RedHat EL6**

**     数据库：    Oracle 11gR2**

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

**![Center][]**

**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 hh24: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.

[Center]: /images/20220805/00867fe099ae4390a83b70bc3f8fc462.png