NLS_DATABASE_PARAMETERS vs. v$parameter

Hi All,

I believe that the following is true:

(1). INIT.ORA settings are read up and fed into the SYS.V$PARAMETER view.

(2). NLS_DATABASE_PARAMETERS view looks at SYS.PROPS$ table for its values.
CREATE OR REPLACE VIEW NLS_DATABASE_PARAMETERS ( PARAMETER, VALUE ) AS select name,
substr(value$, 1, 30)
from props$
where name like 'NLS%'

Q: Where does SYS.PROPS$ table get its values? Why aren't they the SAME as the v$parameter values? Do we hack in new values into the SYS.PROPS$ table to make changes? What effect does CREATE DATABASE and/or ALTER DATABASE have on v$parameter and/or sys.props$?

We have an Ora8i (8.1.5.0.0) on WinNT db, with NLS_DATE_FORMAT = "MM/DD/YY" in the NLS_DATABASE_PARAMETERS view, but NLS_DATE_FORMAT = "MM/DD/YYYY" in the SYS.V$PARAMETER view. It's INIT.ORA file has "NLS_DATE_FORMAT = MM/DD/YYYY" as the last line of the file, and we restarted the database many times since the change was made.

When I log into SQL*Plus, execute "select sysdate from dual", I get "22-DEC-1999", hence it is using MM/DD/YYYY.

I have read that the NLS_LANGUAGE and/or NLS_TERRITORY (American, German, etc.) overrides NLS_DATE_FORMAT settings, but I'm not quite sure how.

I know the information from this web page source (thanks to the author):

http://osi.oracle.com/~tkyte/Misc/NLSDateFormat.html

So I'm confused why SQL*Plus would use MM/DD/YYYY from v$parameter instead of MM/DD/YY from NLS_DATABASE_PARAMETERS.

Am I missing something?

Thanks in advance!

--
Steve Chapman
TOAD Development Team
Quest Software, Inc.

www.toadsoft.com
www.quest.com Received on Wed Dec 22 1999 - 17:59:33 CST