How to change the Oracle default timestamp format

Using alter session only set NLS_TIMESTAMP_FORMAT or NLS_TIMESTAMP_TZ_FORMAT for the current session.

It does开发者_如何转开发 not change the default database properties.

The Oracle 10g's locale installed on my machine is set to Chinese. And the timestamp format is Chinese specific.

I need to use the US format (YYYY-MM-DD HH:MI:SS).

Any solution?

From the Oracle documentation:

You can specify the value of NLS_TIMESTAMP_FORMAT by setting it in the initialization parameter file. You can specify its value for a client as a client environment variable.

You can also alter the value of NLS_TIMESTAMP_FORMAT by changing its value in the initialization parameter and then restarting the instance. To alter the value during a session use the ALTER SESSION SET statement.

NLS_TIMESTAMP_FORMAT http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams132.htm

and for NLS_TIMESTAMP_TZ_FORMAT: http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/initparams133.htm

You'll need to alter the instance's paramters and bounce the database.

Doh. I had two tabs open and put this in the wrong thread...

To set the default permanently in the database, from the database:

alter system ... scope=spfile;

then as SYS (so you may need to talk to a DBA), and obviously at an appropriate time! -

shutdown;
startup;

(I know the spfile has been mentioned, but it can all be done from the sqlplus cmdline, at least in version 11g.)