[Oracle] 오라클 날짜 형식 변경하기

/* sys계정으로 접속 */
$ su - oracle 
Password:
$ sqlplus 
Enter user-name: /as sysdba
SQL*Plus: Release 11.2.0.2.0 Production

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Use SQL*Plus to execute SQL, PL/SQL and SQL*Plus statements.

SQL> SELECT * FROM SYS.PROPS$ WHERE NAME='NLS_DATE_FORMAT';
NAME
--------------------------------------------------------------------------------
VALUE$
--------------------------------------------------------------------------------
COMMENT$
--------------------------------------------------------------------------------
NLS_DATE_FORMAT
DD-MON-RR
Date format

 SQL> UPDATE SYS.PROPS$ SET VALUE$='YYYY-MM-DD HH:MI:SS' WHERE NAME='NLS_DATE_FORMAT';
1 row updated.

 SQL> SELECT * FROM SYS.PROPS$ WHERE NAME='NLS_DATE_FORMAT'; 
NAME
--------------------------------------------------------------------------------
VALUE$
--------------------------------------------------------------------------------
COMMENT$
--------------------------------------------------------------------------------
NLS_DATE_FORMAT
YYYY-MM-DD HH:MI:SS
Date format

 SQL> commit;
Commit complete.

 SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

 SQL> STARTUP 
ORACLE instance started.

Total System Global Area  405020672 bytes
Fixed Size		    2226960 bytes
Variable Size		  301991152 bytes
Database Buffers	   96468992 bytes
Redo Buffers		    4333568 bytes
Database mounted.
Database opened.

alter system set nls_date_format = 'YYYY-MM-DD' scope=spfile;

 

alter system set nls_timestamp_format = 'YYYY-MM-DD HH:MI:SS' scope=spfile;