nls_date_format not reflecting
| Topic ID: 2594 | |
| Created By: | 2007-SEP-06 08:07:06 [Dba_Giri] |
| Updated By: | 2007-SEP-06 13:55:33 [Vitaliy] |
| Status: | Open |
| Severity: | Normal |
| Read Only: | No |
|
8194
2007-SEP-06 08:07:06
|
||||
|
I have changed the nls_date_format as mentioned below: Alter system set nls_date_format='YYYYMMDD' scope=spfile My Server is started using the spfile and after the above command execution, I have restarted the server also, even though am not getting the date format as YYYYMMDD. I have checked it using the following Select Sysdate from dual; It is showing the output in default date dormat i.e DD-MON-YY I have seen the nls_date_format as using the following show parameter nls_date_format it is given the o/p as YYYYMMDD can you please let me know problem might be what..? Thanks a lot in advance.. Giri K.Y.
|
8197
2007-SEP-06 13:53:16
|
||||
|
## ## nls_database_parameters are the values that were set during database creation ## 12:37:24 SYSTEM@10R2:oracle> col value format a30 12:37:35 SYSTEM@10R2:oracle> set lines 132 12:37:37 SYSTEM@10R2:oracle> set trims on 12:37:40 SYSTEM@10R2:oracle> select * from nls_database_parameters; PARAMETER VALUE ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET AL32UTF8 NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM PARAMETER VALUE ------------------------------ ------------------------------ NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 10.1.0.4.2 20 rows selected. ## ## nls_instance_parameters are the values set in the init.ora or spfile ## NOTE: ## nls_instance_parameters values overwrite nls_database_parameters ## 12:38:03 SYSTEM@10R2:oracle> col parameter format a30 12:38:23 SYSTEM@10R2:oracle> select * from nls_instance_parameters; PARAMETER VALUE ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_SORT NLS_DATE_LANGUAGE NLS_DATE_FORMAT NLS_CURRENCY NLS_NUMERIC_CHARACTERS NLS_ISO_CURRENCY NLS_CALENDAR NLS_TIME_FORMAT NLS_TIMESTAMP_FORMAT PARAMETER VALUE ------------------------------ ------------------------------ NLS_TIME_TZ_FORMAT NLS_TIMESTAMP_TZ_FORMAT NLS_DUAL_CURRENCY NLS_COMP NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE 17 rows selected. ## ## nls_session_parameters are the values set at the session level ## ## NOTE: ## nls_session_parameters overwrite BOTH ## nls_instance_parameters and ## nls_database_parameters values ## 12:38:28 SYSTEM@10R2:oracle> select * from nls_session_parameters; PARAMETER VALUE ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM PARAMETER VALUE ------------------------------ ------------------------------ NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE 17 rows selected. 12:38:46 SYSTEM@10R2:oracle> REFERENCE: Note:1048554.6 How to set NLS_DATE_FORMAT Note:241047.1 The Priority of NLS Parameters Explained Run all of the above queries on your system and it should be clear why your setting at the SPFILE is not taking presence. - Vitaliy
[edited by: Vitaliy at 13:55 (CST) on Sep. 06, 2007]