Oracle Study之--NLS_DATE_FORMAT
来源:互联网 发布:mac打字不显示候选框 编辑:程序博客网 时间:2024/05/17 13:13
Oracle Study之--NLS_DATE_FORMAT
系统环境:
操作系统:RedHat EL6
数据库: Oracle 11gR2
在RMAN中设置时间格式时,报错:
Oracle中 NLS_DATE_FORMAT的设置方法
分类:
Oracle中 NLS_DATE_FORMAT的设置方法
Oracle日期格式默认为:DD-MON-YY,以下是3种方法可以修改使之永久生效;
分类:
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.
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.
0 0
- Oracle Study之--NLS_DATE_FORMAT
- Oracle NLS_DATE_FORMAT
- Oracle PL/SQL之令人不解的提示(nls_date_format)
- Oracle session nls_date_format
- ORACLE 日期格式(NLS_DATE_FORMAT)问题
- Oracle Study之--CHECKPOINT探讨
- Oracle Study之案例--Oracle Sqlplus错误
- Oracle Study之--Oracle TimeZone升级
- Oracle Study之--Oracle 触发器(Trigger)
- Oracle Study之--Oracle正则表达式
- Oracle Study之--Oracle数据仓库备份方案
- oracle 性能优化操作十三: 使用nls_date_format
- oracle 性能优化操作十三: 使用nls_date_format
- 【转】Oracle中 NLS_DATE_FORMAT的设置方法
- Oracle RAC Study之--删除VOTE DISK
- Oracle RAC Study之--Cache Fusion
- Oracle Study之案例--DBMS_METADATA Package应用
- Oracle Study之--IPCS管理共享内存
- mysql 查询指定行记录的语句
- VMWARE Workstation 10 安装Ghost 版本WIN7解决办法
- iOS —— CornerStone配置SVN,HTTP及SVN简单使用说明
- POJ-3032 Card Trick
- Android ViewPager内容部分随手势上下滑动隐藏与显示Indicator效果的实现
- Oracle Study之--NLS_DATE_FORMAT
- CPU动态调频二:interactive governor
- MySQL单列索引和联合索引
- HDU 1796 How many integers can you find(容斥)
- aidl ( 一 ) activity获取后台service数据
- C高级知识总结
- [_UIAlertShimPresentingViewController shouldAutorotate] is returning YES解决方法
- 第九周 项目3-分数类中的运算符重载(2)
- CCLabelTTF设置第三方字体遇到的问题