Date Format

The date columns in SQL* Plus are returned in the format 'DD-MON-YY', in order to obtain the time along with the date - one uses to_char() function with the date datatype column and the date format as arguments to display the date along with timestamp in what ever format one desires.

Say a query against a table which has only date columns then it is highly frustrating to use the to_char function on every data datatype column to change the format to include the timestamp. This is where the NLS_DATE_FORMAT parameter comes into picture. Set the NLS_DATE_FORMAT to what ever format the date has to be displayed, all the date datatypes columns in a query return the output in the same format specified with NLS_DATE_FORMAT. Look at the examples below.

SQL> select last_date,next_date from dba_jobs;

LAST_DATE NEXT_DATE
--------- ---------
24-MAY-09 25-MAY-09
25-MAY-09 25-MAY-09
09-FEB-09 01-JAN-00
01-MAR-09 01-JAN-00
24-MAY-09 25-MAY-09
25-MAY-09 25-MAY-09
25-MAY-09 26-MAY-09
24-MAY-09 25-MAY-09
24-MAY-09 25-MAY-09
25-MAY-09 26-MAY-09
25-MAY-09 26-MAY-09
25-MAY-09 26-MAY-09
24-MAY-09 25-MAY-09
24-MAY-09 25-MAY-09
24-MAY-09 25-MAY-09
24-MAY-09 25-MAY-09

16 rows selected.

SQL> alter session set NLS_DATE_FORMAT = 'mm-dd-yyyy HH24:mi:ss';

Session altered.

SQL> select last_date,next_date from dba_jobs;

LAST_DATE           NEXT_DATE
------------------- -------------------
05-24-2009 09:56:49 05-25-2009 08:56:49
05-25-2009 05:28:03 05-25-2009 05:34:03
02-09-2009 16:48:24 01-01-4000 00:00:00
03-01-2009 23:19:11 01-01-4000 00:00:00
05-24-2009 16:18:10 05-25-2009 15:18:10
05-25-2009 00:02:09 05-25-2009 23:00:00
05-25-2009 01:02:12 05-26-2009 00:00:00
05-24-2009 18:28:18 05-25-2009 17:28:18
05-24-2009 21:03:09 05-25-2009 09:03:09
05-25-2009 00:00:03 05-26-2009 00:00:00
05-25-2009 00:00:13 05-26-2009 00:00:04
05-25-2009 00:00:14 05-26-2009 00:00:04
05-24-2009 18:28:13 05-25-2009 17:28:13
05-24-2009 16:15:01 05-25-2009 15:15:01
05-24-2009 16:15:00 05-25-2009 15:15:00
05-24-2009 16:14:59 05-25-2009 15:14:59

16 rows selected.

STARTUP arguments

This post enlightens the usage of options with the STARTUP command of an Oracle database. When using the PFILE argument of the STARTUP command, the init.ora parameter does not necessarily be in $ORACLE_HOME/dbs directory - but can be located anywhere else while the actual path along with the file name has to be mentioned as a value for the PFILE argument. The SPFILE.ORA can be located in only one place, $ORACLE_HOME/dbs. Irrespective of wherever the PFILE is located a "CREATE SPFILE FROM PFILE" will create the SPFILE only in $ORACLE_HOME/dbs directory.

SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\ORACLE\10.2.0\DATABASE\SPFI
                                                 LEORCL10.ORA
SQL> startup force
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1248428 bytes
Variable Size             100664148 bytes
Database Buffers          155189248 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.

SQL>
SQL> startup spfile="C:\Documents and Settings\oracle_and_unix\Desktop\SPFILEORCL10.ORA"
SP2-0714: invalid combination of STARTUP options

SQL> startup force spfile="C:\Documents and Settings\oracle_and_unix\Desktop\SPFILEORCL10.ORA"
SP2-0714: invalid combination of STARTUP options

SQL> startup pfile="C:\Documents and Settings\oracle_and_unix\Desktop\initorcl10.ora"
ORA-01081: cannot start already-running ORACLE - shut it down first

SQL> startup force pfile="C:\Documents and Settings\oracle_and_unix\Desktop\initorcl10.ora"
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1248428 bytes
Variable Size             104858452 bytes
Database Buffers          150994944 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.

SQL> sho parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile="C:\Documents and Settings\oracle_and_unix\Desktop\initorcl10.ora"
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1248428 bytes
Variable Size             104858452 bytes
Database Buffers          150994944 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.
SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string

SQL> create spfile from pfile;

File created.

SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1248428 bytes
Variable Size             100664148 bytes
Database Buffers          155189248 bytes
Redo Buffers                7139328 bytes
Database mounted.
Database opened.
SQL> sho parameter spfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      C:\ORACLE\10.2.0\DATABASE\SPFI
                                                 LEORCL10.ORA
SQL>