| default timestamp-Format [message #63506] |
Mon, 11 October 2004 20:30  |
Shilpa
Messages: 59
Registered: November 2001
|
Member |
|
|
Hi,
I wanted to change the default timestamp format to 'MM/DD/YYYY HH24:MI:SSXFF' . I have tried with ALTER SYSTEM.. but cudnt succeed...
Now i am installing the Oracle9i DB again... so how to set it using init.ora? In oracle9i, SP init file is introduced. I dont know
how to set it using Sp file
Cud u pls guide me?
Thx in advance
With regds,
shilpa
|
|
|
|
| Re: default timestamp-Format [message #63509 is a reply to message #63506] |
Tue, 12 October 2004 00:19  |
 |
Maaher
Messages: 7065
Registered: December 2001
|
Senior Member |
|
|
You are trying to alter the nls_timestamp_format parameter. From the Oracle9i Database Administrator Guide:
----------------------------------------------------------------------
Use the SET clause of the ALTER SYSTEM statement to set or change initialization parameter values. Additionally, the SCOPE clause specifies the scope of a change as described in the following table:
SCOPE Clause Description
- SCOPE = SPFILE
The change is applied in the server parameter file only. The effect is as follows:
For dynamic parameters, the change is effective at the next startup and is persistent.
For static parameters, the behavior is the same as for dynamic parameters. This is the only SCOPE specification allowed for static parameters.
- SCOPE = MEMORY
The change is applied in memory only. The effect is as follows:
For dynamic parameters, the effect is immediate, but it is not persistent because the server parameter file is not updated.
For static parameters, this specification is not allowed.
- SCOPE = BOTH
The change is applied in both the server parameter file and memory. The effect is as follows:
For dynamic parameters, the effect is immediate and persistent.
For static parameters, this specification is not allowed.
----------------------------------------------------------------------
So, I think an "ALTER SYSTEM SET NLS_TIMESTAMP_FORMAT = 'yourformat' SCOPE BOTH;" will do the trick.
I must admit however, as I am no DBA, that I haven't tried it myself.
MHE
|
|
|
|
| Re: default timestamp-Format [message #63539 is a reply to message #63509] |
Thu, 14 October 2004 01:47 |
Shilpa
Messages: 59
Registered: November 2001
|
Member |
|
|
hi,
whatever suggestion u have given that wont do?
Is there any other way? I tried by setting NLS_TIMESTAMP_FORMAT in init.ora also. It shows the value for that parameter correcctly using "SHOW PARAMETER" but doesnt reflect .....still the default format is "DD-MON-YYYY HH12:MI:SSXFF AM'
Shilpa
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
