Oracle Study之--NLS_DATE_FORMAT

cyrus

　　Oracle Study之--NLS_DATE_FORMAT
　　
　　系统环境：
　　操作系统：RedHat EL6
　　数据库：    Oracle 11gR2
　　在RMAN中设置时间格式时，报错：
　　
　　http://img.blog.csdn.net/20150513193624565?watermark/2/text/aHR0cDovL2Jsb2cuY3Nkbi5uZXQvbHF4MDQwNQ==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70/gravity/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>
　　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.