Re: Altering session

From: Michel Cadot <micadot_at_netcourrier.com>
Date: Mon, 16 Jul 2001 15:30:50 +0200
Message-ID: <9iuqab$kin$1@s1.read.news.oleane.net>

"Alain Kreienbuhl" <alain.kreienbuhl_at_fisystem.com> a écrit dans le message news: 3B52E34E.4AB95AB9_at_fisystem.com...
> Thanks (Merci :-) Michel,
>
>
>
>
> I'm getting the following results :
>
>
> SQL> select rpad(parameter,20), rpad(value,20) from v$nls_parameters;
>
> RPAD(PARAMETER,20) RPAD(VALUE,20)
> -------------------- --------------------
> NLS_LANGUAGE FRENCH
> NLS_TERRITORY FRANCE
> NLS_CURRENCY F
> NLS_ISO_CURRENCY FRANCE
> NLS_NUMERIC_CHARACTE ,.
> NLS_CALENDAR GREGORIAN
> NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:S
> NLS_DATE_LANGUAGE FRENCH
> NLS_CHARACTERSET WE8ISO8859P1
> NLS_SORT FRENCH
> NLS_NCHAR_CHARACTERS WE8ISO8859P1
>
> 11 ligne(s) sÚlectionnÚe(s).
>
> SQL> select rpad(name,20), rpad(value,20) from v$parameter where name like '%nls%';
>
> RPAD(NAME,20) RPAD(VALUE,20)
> -------------------- --------------------
> nls_language AMERICAN
> nls_territory AMERICA
> nls_sort _nul
> nls_date_language _nul
> nls_date_format _nul
> nls_currency _nul
> nls_numeric_characte _nul
> nls_iso_currency _nul
> nls_calendar _nul
>
> 9 ligne(s) sÚlectionnÚe(s).
>
> I guess this means that the database is US by default ( as shown in the v$parameter
> table) however the current session is using the french local as
> show in "v$nls_parameters". Is the this correct ?
>
> I allways thought that v$nls_parameters was just a restriction of v$parameter...
> Looks like it's not. ..
>
> Thanks
> Alain.
>
>
> Michel Cadot a écrit :
>
> > "Alain Kreienbuhl" <alain.kreienbuhl_at_fisystem.com> a écrit dans le message news:
> > 3B52BD16.D6412B8C_at_fisystem.com...
> > > Hello,
> > >
> > > After issuing the following command :
> > >
> > > alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
> > >
> > > How can I check the session content ? I'd like to do something like a "
> > > select * from session".
> > > In which schema do Session belong to ?
> > >
> > > Thanks for your help.
> > >
> > > Alain.
> > >
> >
> > You can see your current nls parameters in the v$nls_parameters,
> > for instance:
> > select value from v$nls_paramaters where parameter='NLS_DATE_FORMAT';
> >
> > You cannot see theses values for the other sessions.
> >
> > --
> > Hope this helps
> > Michel
>

There are three levels to define the nls parameters:

- database (via create database, alter database)
- instance (via init.ora)
- session (via environment parameters, alter session)

session level overwrites instance level which overwrites database nls parameters:

v815> select * from nls_database_parameters; (= sys.props$)

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-YY
NLS_DATE_LANGUAGE              AMERICAN
NLS_CHARACTERSET               WE8ISO8859P1
NLS_SORT                       BINARY
NLS_NCHAR_CHARACTERSET         WE8ISO8859P1
NLS_DUAL_CURRENCY              $
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-YY HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-YY HH.MI.SSXFF AM TZH:T
NLS_RDBMS_VERSION              8.1.5.0.0

NLS_COMP 18 rows selected.

v815> select * from nls_instance_parameters; (= v$parameter where name like 'nls%')

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
NLS_TIME_TZ_FORMAT
NLS_TIMESTAMP_TZ_FORMAT

NLS_DUAL_CURRENCY
NLS_COMP 15 rows selected.

v815> select * from nls_session_parameters; (= v$nls_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/MM/YYYY HH24:MI:SS
NLS_DATE_LANGUAGE              FRENCH
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-YY HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZH:TZM
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-YY HH.MI.SSXFF AM TZH:T
NLS_DUAL_CURRENCY              $

NLS_COMP 15 rows selected.

--
Bonne journée
Michel

Received on Mon Jul 16 2001 - 08:30:50 CDT