change NLS_DATE_FORMAT in props$

[newbie5]

I would like to change NLS_DATE_FORMAT in props$ (which is the format oracle uses for export/import ).

init.ora and alter session set NLS_DATE_FORMAT are not doing it.

SQL> show parameter nls_date_format

NAME TYPE VALUE
----------------------------------- ----------- -----------
nls_date_format string MM/DD/YYYY


SQL> select sysdate from dual;

SYSDATE
----------
02/17/2003


1 select name, value$ from props$
2* where name='NLS_DATE_FORMAT'
SQL> /

NAME
------------------------------
VALUE$
------------------------------------------------
NLS_DATE_FORMAT
DD-MON-YY


How do I change NLS_DATE_FORMAT in props$?

[sgodbole]

What version of Oracle you are using? Show us your init.ora entry for this parameter. Changing this parameter in init.ora and bouncing (shutdown/startup) the database should do it.

[newbie5]

As I clearly indicated in the post.. show parameter shows the NLS_DATE_FORMAT in the init.ora.. and it is different from what is showing in props$.

Oracle 9.2 on w2k server... Registry entry matches init.ora...

[agasimani]

It is becuase of NLS_LANG. When you restart your database, your NLS_LANG is overridden. So you should specify NLS_DATE_FORMAT alongwith NLS_LANG in your client.

c:\SET NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
c:\SET NLS_DATE_FORMAT="MM/DD/YYYY"