DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: NLS_DATE_FORMAT Oracle9i

Hybrid View

  1. #1
    Join Date
    Oct 2005
    Posts
    2

    NLS_DATE_FORMAT Oracle9i

    I have been researching how to change the oracle instance date format at the session level. The only thing that seems to work is using a tigger that sets does a alter session per login into the database. It appeasr that changing the registry for Oracle 8i was all that was required, but that does not work for Oracle 9i. To keep from going crazy I would like to know if I am correct?

  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Quote Originally Posted by jwitherspoon
    It appeasr that changing the registry for Oracle 8i was all that was required, but that does not work for Oracle 9i.
    It is stil the same in 9i - all you have to do is to set the appropriate registry key. But I hope you understand you need to set this enviroment on the client, not on the server, right?
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  3. #3
    Join Date
    Oct 2005
    Posts
    2

    Nls_date_format

    No I did not know that, but I will try changing it on the client. Just to confirm do I need to change it om the server and client? Thanks

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    OK, I should have been more specific in my first reply. In short, you should understand where and how NLS_DATE_FORMAT format can be set appart from setting it in ALTER SESSION and which setting takes precedence.

    First, you can set it in ALTER SESSION, which sets it for the current session. This is the higher priority which owerrides the following two.

    The next option is to set it as an environment variable (which in MS Win usually means to set it in the registry). So, if you don't explicitly set it with ALTER SESSION, all your sessions started from this environment will take NLS_DATE_FORMAT from the registry (or better to say - from the environment variable NLS_DATE_FORMAT). If you set this iin your server's registry, it doesn't mean your database will inherit the NLS_DATE_FORMAT parameter from this registry setting, it simply means that any client session that is started from the server itself will read it from ther. For example, if you start SQL*Plus directly on the server, the setting of the NLS_DATE_FORMAT registry key will be valid for that session unless you override it with the ALTER SESSION. But this setting on the server's registry has no effect on sessions that were started from any other computer, they get their NLS_DATE_FORMAT from their own local environment.

    Third option is to set NLS_DATE_FORMAT in the database's initialization file (pfile or spfile). If you set it there, then it will be valid for all database sessions for which no local nevironment variable NLS_DATE_FORMAT was set and in which no "ALTER SESSION SET NLS_DATE_FORMAT" was executed.

    So priorities are in the following order (from highest to lowest, higher override the lower):
    1. ALTER SESSION
    2. environment variable
    3. instance initialization file
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  5. #5
    Join Date
    Jan 2006
    Posts
    4
    Can you tell me when and why "Alter session" gets triggered?

    I have a PHP/Oracle site where i dont use alter session anywhere in my PHP code. But still i see this getting triggered in database and freezes the database.

  6. #6
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    as stated in the other thread - do you have triggers?

  7. #7
    Join Date
    Jan 2006
    Posts
    4
    No i dont have any triggers...

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