change NLS_DATE_FORMAT in props$
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: change NLS_DATE_FORMAT in props$

  1. #1
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178

    change NLS_DATE_FORMAT in props$

    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$?
    Last edited by newbie5; 02-17-2003 at 09:50 PM.

  2. #2
    Join Date
    Jan 2003
    Posts
    78
    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.
    HTH.
    Shripad Godbole
    OCP DBA (8,8i,9i)

    "Let's document it and call it a feature."

  3. #3
    newbie5 is offline Call me super inquisitive
    Join Date
    Jun 2002
    Posts
    178
    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...

  4. #4
    Join Date
    Feb 2001
    Location
    UAE
    Posts
    304
    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"
    Agasimani
    OCP(10g/9i/8i/8)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width