Temporarily and permanently modify the default output format of oracle sysdate

1. The current session is valid.

Alter session set NLS_DATE_FORMAT = 'yyyy-MM-DD: HH24: MI: ss ';

2. Permanent Effect

After the sys user logs in, execute the following command and restart the database to make it take effect.


Alter system set NLS_DATE_FORMAT = 'yyyy-MM-DD: HH24: MI: ss' scope = spfile;

View the sysdate format:


Select * from v $ nls_parameters where parameter = 'nls _ DATE_FORMAT ';

Note:Scope = spfileOption

In Oracle, there is a thing called spfile, that is, the dynamic parameter file, which sets various Oracle parameters. The so-called dynamic, that is, you can avoid shutting down the database,
Modify database parameters, which are recorded in spfile. There are four scope options when changing parameters. Scope is the range
++ Scope = spfile only changes the records in the spfile, and does not change the memory, that is, it does not take effect immediately, but waits for the next database to take effect. Some parameters can only be changed in this way
You can only use this method to modify the sysdate format.
++ Scope = memory only changes the memory and does not change the spfile. That is, it will become invalid upon next startup.
++ Scope = both memory and spfile are changed
++ Does not specify the scope parameter, which is equivalent to scope = both.