Oracle Database Discussions

Hi all,

10 XE, Ubuntu Linux.

I'm collecting data from a file which is just a bunch of numbers. I'm doing it a line at a time
(every 5 mins).

I want to use a DATE as my primary key.

I want to display the date to the nearest minute in the tables.

I did this - first attempt fails - change NLS_DATE_FORMAT and it works.

SQL> select trunc(systimestamp, 'MI') from dual;
TRUNC(SY
--------
16:55:00    <============ No good!

====================

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';

Session altered.

SQL> select trunc(systimestamp, 'MI') from dual;

TRUNC(SYSTIMESTAMP,
-------------------
2011 08 31 16:55:00            <============== Yipee!

So, I decide that I'll change my NLS_DATE_FORMAT for good. I try

SQL> alter system  SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS';
alter system  SET NLS_DATE_FORMAT = 'YYYY MM DD HH24:MI:SS'
                  *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option

I then look at the parameter

SQL> select name, value, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE from v$parameter where name = 'nls_date_format';

NAME		VALUE			  ISSES ISSYS_MOD ISINS
--------------- ------------------------- ----- --------- -----
nls_date_format 			  TRUE	FALSE	  FALSE

This appears to imply that I have to change the parameter for every session? I know that I can
do this in my glogin.sql or whatever - but why can't I change this in the database?

Is it to do with a setting in my machine itself? I don't appear to be able to modify my NLS_TERRITORY either. Checked
my Ubuntu settings - Ireland - correct. America is wrong! (hee hee...).

SQL> select name, value, ISSES_MODIFIABLE, ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE from v$parameter where name = 'nls_territory';

NAME		VALUE			  ISSES ISSYS_MOD ISINS
--------------- ------------------------- ----- --------- -----
nls_territory	AMERICA 		  TRUE	FALSE	  FALSE

Explanations &c. appreciated.


Paul...